sumproduct formula taking too long to calculate

[hkg003]

Hello,
I am using Excel 2010 and wondering if someone could expedite my calculations for the following:

I have a worksheet titled "Raw Data"

The Raw data contains the following:
A1 ID#
A2 123ABC
A3 345DEF
A4 123ABC
A5 456HKG
A6 123ABC

B1 Month
B2 Apr
B3 May
B4 Apr
B5 May
B6 Apr


On another worksheet, lets label it "Data Results" it contains the following:

A1 ID#
A2 123ABC
B1 Month
B2 Apr
C1 Total ID#
C2 has the following formula:

=SUMPRODUCT(('Raw Data'!$A:$A=$A2)*('Raw Data'!$B:$B=$B2))

So the answer I am looking for in cell C2 of my Data Results worksheet is "3"

Is there another way to obtain the same results without having my calculations take too long?


Thank You

 

[mikerickson]

You could use COUNTIFS if you have a more recent version of Excel.
Or you could restrict the range to less than the whole column, e.g.
=SUMPRODUCT(('Raw Data'!$A1:$A99999=$A2)*('Raw Data'!$B1:$B99999=$B2))
Even if the specified range is large, it would be faster than using a whole column reference.

 

[hkg003]

You could use COUNTIFS if you have a more recent version of Excel.
Or you could restrict the range to less than the whole column, e.g.
=SUMPRODUCT(('Raw Data'!$A1:$A99999=$A2)*('Raw Data'!$B1:$B99999=$B2))
Even if the specified range is large, it would be faster than using a whole column reference.

Thank You Mikerickson! The COUNTIFS formula worked great! I simply did the following:
=COUNTIFS('Raw Data'!$A:$A,$A2,'Raw Data'!$B:$B,$B2)


tHANK YOU FOR YOUR HELP!