Excel Sumproduct with multiple criteria

[PeteSmith]

Hi, Can someone help me please with the following formula. The current sumproduct formula works fine but what i really need is to add another criteria (<01/02/2007) so that it checks the whole month, not just the 1st day.

= SUMPRODUCT(SUMIF(INDIRECT("'"&D2:D100&"'!F12:F47"),">=01/01/2007",INDIRECT("'"&D2:D100&"'!H12:H47")))

I have tried various different ways but none that work. Any suggestions?

[arthurleung]

a little bit of google search gives me:

AND('criteria A','criteria B')

that you can use.

Effectively replacing:

">=01/01/2007"

With:

AND(">=01/01/2007","<01/02/2007")

[PeteSmith]

Hi, thanks for the reply, unfortunately that did not work. The problem i have is because of the Date() function in excel, even though i have formatted the cell to MMM YY, so it picks out the whole month and not just the 1st day, it still defaults to the 1st of the month 01/01/2007. Hence why i need it to check multiple criteria so that checks the whole month.

Even though the following did not return any errors, it does not work. Perhaps i have written this wrong, i'm not sure.

=SUMPRODUCT(SUMIF(INDIRECT("'"&D2:D100&"'!F12:F47"),AND(">=01/01/2007, 01/02/2007"),INDIRECT("'"&D2:D100&"'!H12:H47")))

[Salazaar]

Just formatting the cell to MMM YY wont actually change the value of the cell. Have you tried using the =MONTH() funtion to return just the value of the month?

Re-reading the original, I'm not sure this will solve your problem actually...

[Lucio]

Ok, I'm puzzled, why if you are using SUMPRODUCT, do you have SUMIF contained within the same formula?

SUMPRODUCT will multiply two arrays of the same size, line by line, SUMIF, will add the totals together IF they meet a certain criteria and AFAIK you can't combine them.


What I think you're actually looking for is an array formula, they're a pain in the arse to write but once done, should do what you really intend, which is add data from a randomly sorted list based on criteria

The basic idea is as follows SUM(IF(AND(column1=>01/01/2007,column1<=31/01/07),range1,0) and then press ctrl+shift+enter to input it. If done correctly, the formula becomes encased in { }. The end effect is that line by line, Excel evaluates the formula in order, and if the two IF's evaluate to TRUE, gives you another number to be included in the SUM, if it evaluates to FALSE, then it just returns a 0 for that line. If you want to use SUMPRODUCT, I think what you'd have to do is declare two seperate ARRAYS and put the SUMPRODUCT around that (or more simply, put a column in with the product of the two columns line by line and change range1 to target that)

[Lucio]

Just because I'm nice, here's a formula that'll work for definiate, remember to press ctrl+shift+enter to make it an array formula

SUMPRODUCT(IF(date_range>=DATE(2007,1,1),IF(date_range<=DATE(2007,1,31),range1,0),0),IF(date_column> =DATE(2007,1,1),IF(date_column<=DATE(2007,1,31),range2,0),0))

Replace range 1 and range 2 with the correct target ranges for your SUMPRODUCT and date_range is the column with the date in. It's important to make all ranges the same length and NOT to only select a column

[itambrose]

Hi - I'm trying to use this date-range validation for a SUMIF array formula:

=SUM(IF(AND(Detailed!F3etailed!F1002>=B24,Detailed!F3etailed!F1002<=C24),Detailed!Z3etailed!Z1002,0)) with Ctrl+Shift+Enter

...where Detailed!F3etailed!F1002 is a single date in DD-MMM-YY format
...where Detailed!Z3etailed!Z1002 is what need to be summed (numeric values)
...where B24 is the start date (DD-MMM-YY)
...where C24 is the end end (DD-MMM-YY)

The problem is that it doesn't sum. I don't get an error though, it just shows 0.00.
Any ideas? Thanks.