Multiple Sumif conditions

shaegens

New Member
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))

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

shaegens

New Member
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

Well-known Member
just off the cuff suggestion.

the array should be the same

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

New Member
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

MrExcel MVP
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)

Replies
8
Views
180
Replies
0
Views
134
Replies
2
Views
233
Replies
8
Views
307
Replies
5
Views
523

1,190,807
Messages
5,983,026
Members
439,813
Latest member
monvarona

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back