SUMIF and SUM query

Hi,

I'm trying to workout why I am getting an error with the below formula. I get a 'there's a problem with this formula'.

Please Login or Register  to view this content.

Can someone help me with the error?

Many thanks in advance

The syntax for SUMIF is:

SUMIF(criteria_range, criteria, sum_range)

I presume you are looking in column AE of the Raw_data sheet to see if cells in that column are equal to XX, but then the rest of it is not a range, so it doesn't make sense to Excel.

I think you need to explain what yo are trying to do.

Hope this helps.

Pete

Hi Pete,

many thanks for your response.

I wanted to do the sum (sum_range) only if the column AE = XX. They are all on the same sheet (corrected to Sheet1).

In essence, sum of one countif - sum of the other countif, but only if AE = XX.

Please Login or Register  to view this content.

Hope that makes more sense.

Many thanks in advance.

You can use COUNTIFS to do that, which allows you to specify more than one condition, like this:

=COUNTIFS('Sheet1'!D:D,"<"&Charts!Q1,'Sheet1'!AE:AE,"XX") - COUNTIFS('Sheet1'!AF:AF,"<"&Charts!Q1,'Sheet1'!AE:AE,"XX")

Hope this helps.

Pete

Fantastic!!

Thank you so much, that worked perfectly.

You're welcome - thanks for the rep.

Pete