"Pete" wrote:
cany anyone tell me why the following SUMPRODUCT Formula works
=SUMPRODUCT(--(MONTH(CountDate)=MONTH(B$2)),--(AreaCol=$A4),
--(SRACol=0.98),--(SRACol<=1.02))

Result is 195 which is correct.

And this one doesn't
=SUMPRODUCT(--(MONTH(CountDate)=MONTH(B$2)),--(AreaCol=$A4),
--(SRACol<0.98),--(SRACol1.02))

Result is 0, should be 16. If I only include one search for <0.98 or
1.02 then I get 7 + 9 respectively. Why does it not work when they
are combined as in the first formula.

When you use SUMPRODUCT in this form, you are implicitly specifying the
"and" of all conditions.

So the first SUMPRODUCT counts the number of instances when CountDate is the
same month as B2 __and__ AreaCol equals A4 __and__ SRACol is between 0.98
and 1.02 inclusive, all in the same row or column.

Likewise, as written, the second SUMPRODUCT counts when CountDate is the
same month as B2 __and__ AreaCol equals A4 __and__ SRACol is both less than
0.98 __and__ greater than 1.02.

Obviously the latter condition is not what you intended. Zero is the
correct answer since SRACol cannot meet both conditions at the same time.

I suspect that what you want is:

=SUMPRODUCT(--(MONTH(CountDate)=MONTH(B$2)),--(AreaCol=$A4),
--((SRACol<0.98)+(SRACol1.02)0))

The plus ("+") behaves almost like OR, especially when we include "0".
(Although in this case, "0" is not necessary because the two conditions are
mutually-exclusive.)

By the way, multiply ("*") behaves like AND. So you could write:

=SUMPRODUCT((MONTH(CountDate)=MONTH(B$2))*(AreaCol =$A4)
*((SRACol<0.98)+(SRACol1.02)0))

A few less characters to type.