SMACK ..... straight into a brick wall.......

Can anyone advise why my SUM IF IF statement is not working below please?

{=SUM(IF(Z2:Z1190=C14,IF('Raw Data'!U2:U1190="Collections",'Raw Data'!F2:F1190)))}

Column Z = various dates in a ddmmyy format rows 2 to 1190
C14 = a single date in ddmmyy format
Column U on the Raw Data worksheet contains various departments including Collections
Column F on Raw Data worksheet contains Amounts that I'm looking to sum

Any assistance appreciated.

Hi,

try:

=SUMPRODUCT((Z2:Z1190=C14)*('Raw Data'!U2:U1190="Collections")*('Raw Data'!F2:F1190))

Martin

The result was the same as mine ....... just a zero.

I tested it also with the following more simplified example:-


Column A Column B Column C Column D
27/12/2005 27/12/2005 Collections 20
27/12/2005 Customer Services 35
28/12/2005 Customer Services 202
27/12/2005 Collections 52

So, if the dates in Column A = the 1 date in Column B AND if any rows in Column C = Collections, add up the values in Column D. So for the above, I expect a total of 72 (being the 1st and last rows being added up)

=SUMPRODUCT(A2:A5=B2)*(Sheet1!C2:C5="Collections")*(D2:D5)
This returns zero. If I put in the { }, still zero.

{=SUM(IF(A2:A4=B2,IF(C2:C5="Collections",D2:D5))}
This returns 329 - summing all rows ..... for some unknown reason.

Any thoughts other than Aaaarrrrgggghhhhhh ? :-)

What does

=SUMPRODUCT(--(A2:A5=B2))

return?

(Should be count of the dates you put in B2)

If it's still a zero, you have an issue with text format versus date format. What is the number format of column A?

Martin

This returns 3 - which is correct as there are 3 dates of 27/11/05.

The format of columns A & B are the same ..... date dd/mm/yy format.

I've just reformatted the dates to custom dd/mm/yyyy and tried the {SUM(IF......,IF( ))} and its worked on the
sample above.

Its still not working on my original spreadsheet in my first post .... I'll reformat dates and see if that helps

Now working, Thanks. Problem appeared to be the date formats - although both appeared to be the same :-)