Using SUMPRODUCT instead of COUNTIFS

**Whizbang** · 11-18-2011, 04:34 PM

I am not sure what you mean.

The following article is great for learning/understanding SUMPRODUCT.

http://xldynamic.com/source/xld.SUMPRODUCT.html

**SymphonyTomorrow** · 11-18-2011, 04:36 PM

I mean only that deploying this across my whole matrix is taking a very long time to calculate.

**NBVC** · 11-18-2011, 04:49 PM

You can add a helper column in D3:

=A3&"_"&B3&"_"&C3

copied down, then in H4:

=COUNTIF($D$3:$D$20,$G4&"_"&H$3&"_"&$F$3)+COUNTIF($D$3:$D$20,$G4&"_"&H$3&"_"&$F$4)+COUNTIF($D$3:$D$20,$G4&"_"&H$3&"_"&$F$5)

copied down and across...

if you had XL2007 or later, H4 would be:

=SUMPRODUCT(COUNTIFS($A$3:$A$20,$G4,$B$3:$B$20,H$3,$C$3:$C$20,$F$3:$F$5))

copied down and across...