# Multiple conditions - SumProduct, SumIfs, CountIfs - Which do I need?

1. ## Multiple conditions - SumProduct, SumIfs, CountIfs - Which do I need?

New Microsoft Excel Worksheet.xlsx

Hello all.

The more time I spend looking at formulae the more confused I get. I am having a hard time telling when I need to use SumProduct, Countif(s), sumif(s) etc.

I was already assisted here with the following formula:
=SUMPRODUCT(--(rngDeskIss>=G3)*(rngDeskIss<=I3)*(rngDeskIss=rngSch1Sub))

However, it does not seem like I can just plug and play with this the way I intended.
This correctly matches a named_range of cells within a given date range (G/I). If the date is between G&I, and the two items match, sum.
This works perfectly for my one question, but I cannot seem to apply this to a >, <, for a different kind/set of data.

For my new example, I am looking to pull data between a set range (J3/L3), and if they data in column A is between J3:L3, count if column B is above B1.
Or worded differently:
Count the number of times the values in column B less than cell B1, which is pulled from a range of dates defined by J3/L3.

How many items between 4/1 and 4/8 are less than '730'?
4/4/2013 - 732
4/5/2013 - 730
4/8/2013 - 725

I have tried to modify the following examples I've found, without luck:
'=SUMPRODUCT(--(DataPull!\$O\$2:\$O\$10000=DataPull!\$P\$2:\$P\$10000))
'=SUMPRODUCT((COUNTIF(B2:B1000,A2:A1000)=0)*(A2:A1000<>""))
'=SUMPRODUCT(--(rngDeskIss>=G3)*(rngDeskIss<=I3)*(rngDeskIss=rngDeskSub))
'=COUNTIFS(\$A\$2:\$A\$9,">="&\$F\$1,\$A\$2:\$A\$9,"<="&\$F\$2,B\$2:B\$9,"="&\$F\$4)
'=counfifs((\$A\$2:\$A\$100>=V51)*(\$A66:X\$100<=X5)*(\$B\$2:\$B\$100))  Register To Reply

2. ## Re: Multiple conditions - SumProduct, SumIfs, CountIfs - Which do I need?

Try with this format:
=SUMPRODUCT((datecolumn>=J3)*(datecolumn<=L3)*(valuecolumn<=value))
With J3 and L3 is given date.  Register To Reply

3. ## Re: Multiple conditions - SumProduct, SumIfs, CountIfs - Which do I need?

with an helpcolumn in I.

in I1 is the result.

see the attached file.  Register To Reply

4. ## Re: Multiple conditions - SumProduct, SumIfs, CountIfs - Which do I need?

Thank you both.
"=SUMPRODUCT((A3:A1000>=V5)*(A3:A1000<=X5)*(B3:B1000>730))" seems to resolve my question, but is so so so close to the others that I've tried, that I cannot 'see' what makes this work. Take away the '--' in my original formulae and it is the 'same' (with the exception of the range names and values.)

I must have typed something wrong a few times, not seeing the error. This works though. =SUMPRODUCT((A3:A1000>=V5)*(A3:A1000<=X5)*(B3:B1000>730))

Thanks again.  Register To Reply