Sumproduct with a AND

users
#1

What Im looking to do is, if O column has an "x",
count how a range of numbers.
AW column has numbers that fall in the 20's, 30's, 40's etc.
I want to do something like less than 40 greater than 30.
I tried AND but that caused an error.

=SUMPRODUCT(O2:O168="x", $AW$2:$AW$168 <30)

#2

You don't need AND(), since the product of logical values gives you that naturally: the product is 1 (TRUE) only if both operands are 1 (TRUE). You can simply include both limits as separate inequalities, so your SUMPRODUCT() has three parameters, not two.

In (say) column X from cell X1, enter a list of the discriminating values: 0, 10, 20, and so on.
In Y1 enter
=SUMPRODUCT(O$2:O$168="x",AW$2:AW$168>=X1,AW$2:AW$168<X2)
and fill down the column.

I trust this helps.

Brian Barker