Multiple Sumif conditions

[shaegens]

Hi,
i know i need to use a sumproduct for this kind of problem, and i have copied the formula (2 conditions) from another spreadsheet where it is working fine, but it doesnt seem to be working when i increase the number of conditions.

I have 4 columns:
A - Name
B - Job Type
C - Quantity
D - Date

i have specified the name of the worker in cell A1, the start date in cell B1, finish date in cell C1 and job type in D1, and i want to sum up the quantity of work that individual did between the two dates for a specific job type

so there's 4 conditions, name, date is greater than start date, date is less than end date, plus job type

can you give me a hand, this is what i have so far

=SUMPRODUCT(--($A$3:$A$3500=$A$1),--(D$3:$D$3500>=$B$1,($E$3:$E$3500<=$C$1),--($B$2:$B$3500=$D$1),($C$3:$C$3500))

 

[shaegens]

woops, type, the real formula i have is:



=SUMPRODUCT(--($A$3:$A$3500=$A$1),--(D$3:$D$3500>=$B$1,--($E$3:$E$3500<=$C$1),--($B$3:$B$3500=$D$1),($C$3:$C$3500))

 

[venkat1926]

just off the cuff suggestion.

the array should be the same

in your case

A3:a3500
d3:d3500
e3:e3500
B2:b3500 -----this is perhaps wrong should B3:B3500
C3Lc3500

change the fourth item and see whether you get it

 

[shaegens]

thanks for your reply, that was just a typo
all the ranges are the same size
i think it has to do with the greaterthan or equal to condition, rather than a plain equal sign

 

[Scott Huish]

Your parentheses are incorrect.
but what is the column E reference, shouldn't that be column D?

=SUMPRODUCT(--($A$3:$A$3500=$A$1),--($D$3:$D$3500>=$B$1),--($E$3:$E$3500<=$C$1),--($B$3:$B$3500=D1),C3:C3500)