Sumif with multiple criteria

[Jambruins]

I am trying to set up a function that will do the following:

Column A has a bunch of numbers in it (like 115, 165, 103, etc.)
Column B has either a W or and L in it
In column C I have cells as follows:
100-109
110-119
120-129

I want the cell next to the cell with 100-109 in it to count up all th
cells with a W in it if the number in Column A is between 100 and 109.


I will try and provide an example below.

A B C D
115 W 100-109 (formula here (2))
122 L 110-119
104 W 120-129
108 W 130-139

The formula needs to be set up to look down column A and find al
values between 100-109 and if a W is in column B corresponding to the
to count it. The 2 in column D above is the value I want the formul
to give me based on 104 and W and 108 and W.

Let me know if you don't understand what I am asking and I will repos
it. Thank

 

[JulieD]

Hi

=SUMPRODUCT(($A$1:$A$5>=VALUE(LEFT(C1,3)))*($B$1:$B$5="W"))-SUMPRODUCT(($A$1
:$A$5>VALUE(RIGHT(C1,3)))*($B$1:$B$5="W"))

Let us know how you go

Cheers
JulieD

 

[Domenic]

Hi,

try,

=SUMPRODUCT(($A$1:$A$10>=E1)*($A$1:$A$10<=F1)*($B$1:$B$10=G1))

where...

E1 contains lower number limit
F1 contains higher number limit
G1 contains the letter as your criteria

If you would like to copy a formula down the column so that it woul
pick up the corresponding number limits and letter for your criteria
the try the following formula:

=SUMPRODUCT(($A$1:$A$10>=--(LEFT(C1,3)))*($A$1:$A$10<=--(RIGHT(C1,3)))*($B$1:$B$10=B1))

Hope this helps!