# Is there a faster way to do this??

#### dexceler

##### New Member
Helllllllo,

I have been using the formula below to count the number of times a particular string in the RAW DATA! sheet has been selected to use and I need to determine the count for each month and for each city with the selected criteria.

SO, ive used the formula below that counts how many times the user inputted LA in column B, California in column E and if its the month of Jan 2015 in column C. But if I wanted to find it for Feb I'd have to change the MONTH 1 to 2 and then for March 2 to 3 and so forth. It becomes waayyyy to slow and inefficient when you have to do so for a number of other fields.

=SUMPRODUCT(1*(MONTH('Raw Data'!C7:C24374)=1)*(1*(YEAR('Raw Data'!C7:C24374)=2015)*('Raw Data'!B7:B24374="LA")*('Raw Data'!E7:E24374="California")))

IS there anyway I can have it so the formula just determines the month from the raw data sheet and inputs the count in the desired cell box in another sheet? OR something more efficient then what I have?

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Hi,

First off the 1* bits aren't necessary. Multiplying the ARRAYS is sufficient to coerce al the TRUE and FALSE into 1 and zero. I would us cell references for the lookup values like this

A1=1
A2=2015
A3= LA
A4 = California

and the formula like this. This formula calculates in ~3/100th of a second so your not going to improve that too much.

=SUMPRODUCT((MONTH('Raw Data'!C7:C24374)=A1)*((YEAR('Raw Data'!C7:C24374)=A2)*('Raw Data'!B7:B24374=A3)*('Raw Data'!E7:E24374=A4)))

For more speed, try:

J2: 1
K2: 2015
L2: LA
M2: California

In N2 enter (and copy down for more criteria)...
Rich (BB code):
``````
=COUNTIFS('Raw Data'!\$C\$7:\$C\$24374,">="&DATE(K2,J2,1),
'Raw Data'!\$C\$7:\$C\$24374,"<="&EOMONTH(DATE(K2,J2,1),0)),
'Raw Data'!\$B\$7:\$B\$24374,L2,'Raw Data'!\$E\$7:\$E\$24374,M2)
``````

For more speed, try:

J2: 1
K2: 2015
L2: LA
M2: California

In N2 enter (and copy down for more criteria)...
Rich (BB code):
``````
=COUNTIFS('Raw Data'!\$C\$7:\$C\$24374,">="&DATE(K2,J2,1),
'Raw Data'!\$C\$7:\$C\$24374,"<="&EOMONTH(DATE(K2,J2,1),0)),
'Raw Data'!\$B\$7:\$B\$24374,L2,'Raw Data'!\$E\$7:\$E\$24374,M2)
``````

Replies
0
Views
519
Replies
3
Views
433
Replies
7
Views
365
Replies
11
Views
1K
Replies
6
Views
2K

1,203,457
Messages
6,055,546
Members
444,795
Latest member
cjohnson333

### 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.

### Which adblocker are you using?

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

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