Thread: SUMPRODUCT
View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.newusers
joeu2004[_2_] joeu2004[_2_] is offline
external usenet poster
 
Posts: 829
Default SUMPRODUCT

PS....

I wrote:
I suspect that what you want is:
=SUMPRODUCT(--(MONTH(CountDate)=MONTH(B$2)),--(AreaCol=$A4),
--((SRACol<0.98)+(SRACol1.02)0))

[....]
So you could write:
=SUMPRODUCT((MONTH(CountDate)=MONTH(B$2))*(AreaCol =$A4)
*((SRACol<0.98)+(SRACol1.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.