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 1190C14 = a single date in ddmmyy formatColumn U on the Raw Data worksheet contains various departments including CollectionsColumn F on Raw Data worksheet contains Amounts that I'm looking to sumAny assistance appreciated.

The result was the same as mine ....... just a zero. tested it also with the following more simplified example:-Column A Column B Column C Column D27/12/2005 27/12/2005 Collections 2027/12/2005 Customer Services 3528/12/2005 Customer Services 20227/12/2005 Collections 52So, 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 ? :-)

KingMartin

Dec 6 2005, 10:23 AM

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

Artorius

Dec 6 2005, 10:44 AM

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

Artorius

Dec 6 2005, 10:58 AM

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

This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.