Sumifs problem

[Bernie01]

Hello, I am trying to obtain a sum of transactions based on
a) a Start Date,
b) an End Date,
c) a Client Name specified at L4 and ,
d) a Service Type specified in a Data Worksheet.
The Start and End Dates will both be found in the date column in the attached image. Can somebody please offer the correct syntax for this problem?

This is my attempt =SUMIFS(Cost, Date, ">="L6, Date,"<="N6, Client,L4, Service,$Data.A7) however I receive an an error message 'err:509

Cost is the range name of all the entries in the Cost column
Date is the range name of all the entries in the Date Column
Client is the range name of allthe entries in the Client column
$Data.A7 is the location of the type of service I want to sum for. It is contained in a separate Worksheet attached to the same Workbook as the current Worksheet

Cheers, Bernie

(Slightly edited for better readability by @Lupp)

[keme]

If you are testing for other conditions than straight equality, the entire condition (compare-operator and compared value) should be presented to the function as a string. Concatenate operator and value using CONCATENATE() function or & operator, e.g. like this:

=SUMIFS(Cost; Date; ">="&L6;Date;"<="&N6; Client;L4;Service;$Data.A7)

[Bernie01]

Thank you Keme. This has worked perfectly. I had seen ‘&’ used in some of the examples I researched but I must have typed them incorrectly as they never worked for me. Copying your suggestion into the cell I was using worked immediately. I am most grateful for your prompt and helpful reply

[keme]

Good!

The & operator expects text operands, so numeric input is implicitly converted.

Note that this can be a little confusing with dates at times, if you try to go beyond comparing to existing cell values. Then you need to know how date/time values work.

[mariosv]

Please, if the answer solves the question click .