Thread: Converting Countifs to 2003 formula

=Sumproduct(('Data_Q2 2008'!$S$2:$S$9806="N")*('Data_Q2 2008'!$B$2:$B$9806='Metric Upload'!C2)*1)

Thank you! Your formula worked. I have been working on that all night.

How would you develop the formula if you want to do multiple filters. Right now, there is only one filter for "N"? Thanks.

Put the criteria between a set of {} s

('Data_Q2 2008'!$S$2:$S$9806="N")

would become

('Data_Q2 2008'!$S$2:$S$9806={"Y","N","MAYBE"})

mdbct,
Thank you! That worked. Ok, last related question. Your last post worked for multiple filters in the same column. What if you have multiple filters, say 3, and they are each in a separate column? Thank you for your prompt posting. Saving me a lot of time!

Rob

Just keep adding the criteria. The following will count the rpw matching your original criteria with the added criteria of the "ALL" being the data in column T and "NEW" being the data in column U

=Sumproduct(('Data_Q2 2008'!$S$2:$S$9806="N")*('Data_Q2 2008'!$B$2:$B$9806='Metric Upload'!C2)*('Data_Q2 2008'!$T$2:$T$9806="ALL")*('Data_Q2 2008'!$U$2:$U$9806="NEW")*1)

I tried this formula is no value is calculating. There is a blank as opposed to an error message. Below is what I did from your post

=SUMPRODUCT(('Step 1_Data_Q2 2008'!T3:T10000="N")*('Step 1_Data_Q2 2008'!U3:U10000="*")*('Step 1_Data_Q2 2008'!C3:C10000='Metric Upload'!C2)*1)

N=First filter
*= is filtering for any value present
The last statement is a lookup

Change this

('Step 1_Data_Q2 2008'!U3:U10000="*")

to

('Step 1_Data_Q2 2008'!U3:U10000<>"")

SUMPRODUCT doesn't play well with wildcards.

Successful!!! Thank you again! Great site!

For the future if I need to use a wildcard, what is the correct character to use? Thanks

I should have said that SUMPRODUCT doesn't play with wild cards at all.

There are a couple methods you could use to check the text is portions of a cell.

For cells starting with specific characters you can use the left function (Left, Mid and Right functions will all work). The following checks for cells that start with the letter A:

(LEFT(G1:G20,1)="A")

If you want to find specific text within the cells you have to use either a Find (case sensitive) or Search (not case sensitive) on the column. The following looks for the letter "a" or "A" within the cells.

NOT(ISERROR(SEARCH("A",G1:G20,1)))

I am having trouble with the syntax with that formula. How would I change the following to incorporate it for searching for "Picu":

=SUMPRODUCT(('Step 1_Data_Q2 2008'!T2:T10000="N")*('Step 1_Data_Q2 2008'!C2:C10000='Metric Upload'!C2)*1)*('Step 1_Data_Q2 2008'!U2:U10000="Picu*")*1))

Try this one:

=SUMPRODUCT(('Step 1_Data_Q2 2008'!T2:T10000="N")*('Step 1_Data_Q2 2008'!C2:C10000='Metric Upload'!C2)*(left('Step 1_Data_Q2 2008'!U2:U10000,4)="Picu")*1)