sumproduct formula taking too long to calculate

hkg003

Active Member
Joined
Apr 6, 2008
Messages
257
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
 
Last edited:

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,910
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

Active Member
Joined
Apr 6, 2008
Messages
257
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,689
Messages
5,597,559
Members
414,156
Latest member
WDMix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top