Multiple Sumif conditions

shaegens

New Member
Joined
Jul 4, 2007
Messages
4
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
Joined
Jul 4, 2007
Messages
4
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))
 
Upvote 0

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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
 
Upvote 0

shaegens

New Member
Joined
Jul 4, 2007
Messages
4
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
 
Upvote 0

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,961
Office Version
  1. 365
Platform
  1. Windows
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)
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top