SUMPRODUCT

[Pete]

Hi, 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),--(SRACol>1.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.

Regards

Peter

 

[joeu2004]

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),--(SRACol>1.02))

Result is 0, should be 16. If I only include one search for <0.98 or
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)+(SRACol>1.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)+(SRACol>1.02)>0))

A few less characters to type.

 

[joeu2004]

PS....

I suspect that what you want is:
=SUMPRODUCT(--(MONTH(CountDate)=MONTH(B$2)),--(AreaCol=$A4),
--((SRACol<0.98)+(SRACol>1.02)>0)) [....]
So you could write:
=SUMPRODUCT((MONTH(CountDate)=MONTH(B$2))*(AreaCol=$A4)
*((SRACol<0.98)+(SRACol>1.02)>0))

There is always some question about whether one form performs better than
the other.

I measured this some time ago; but frankly, I don't remember the answer.

However, it usually does not matter unless and until CountDate et al
encompass tens of thousands of rows and/or you replicate this formula in
thousands of cells.

Generally, use whichever form you feel more comfortable unless and until you
encounter some performance bottleneck.

Then you might try the other form to see if it makes a difference. In my
experience, it does not because the real performance bottleneck is simply
the fact that we doing so many recalculations in the first place.

 

[Pete]

Many thanks for the Formula and the explanation, much appreciated and
exactly what I wanted.

Regards

Pete