Full Version: SUM .... IF ........ IF ........
UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel
Artorius
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.
KingMartin
Hi,
ry:
=SUMPRODUCT((Z2:Z1190=C14)*('Raw Data'!U2:U1190="Collections")*('Raw Data'!F2:F1190))
Martin
Artorius
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 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 ? :-)
KingMartin
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
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
Now working, Thanks. Problem appeared to be the date formats - although both appeared to be the same :-)
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.