Ask Your Question

Sumifs problem

asked 2020-12-18 06:28:41 +0100

Bernie01 gravatar image

updated 2020-12-19 19:50:16 +0100

Descrição da imagem

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)

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2020-12-18 10:32:46 +0100

keme gravatar image

updated 2020-12-18 10:34:27 +0100

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)

edit flag offensive delete link more


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

Bernie01 gravatar imageBernie01 ( 2020-12-19 01:04:09 +0100 )edit


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.

keme gravatar imagekeme ( 2020-12-19 19:19:54 +0100 )edit

Please, if the answer solves the question click ✔.

m.a.riosv gravatar imagem.a.riosv ( 2020-12-20 00:02:01 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-12-18 06:28:41 +0100

Seen: 69 times

Last updated: Dec 19 '20