Is there a faster way to do this??

[dexceler]

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?

 

[Mike LH]

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)))

 

[Aladin Akyurek]

For more speed, try:

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

In N2 enter (and copy down for more criteria)...

=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)

 

[Mike LH]

For more speed, try:

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

In N2 enter (and copy down for more criteria)...

=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)

Typo alert