Converting Countifs to 2003 formula

**rgold** · 10-06-2008, 01:14 PM

Hello,
I am having trouble converting the following Countifs formula developed in 2007 to work in 2003 Excel.

Basically, I want to count the frequency of "N" in 'Data_Q2 2008'!$S$2:$S$9806, where 'Data_Q2 2008'!$B$2:$B$9806 = 'Metric Upload'!C2.

'Data_Q2 2008'!$B$2:$B$9806 is a list of physicians. 'Metric Upload'!C2 is a pick list for selecting the physican that is in the larger data set using Data Validation.

Thank you!

2007 Countifs Formula:

=COUNTIFS('Data_Q2 2008'!$S$2:$S$9806,"N",'Data_Q2 2008'!$B$2:$B$9806,'Metric Upload'!C2)

**mdbct** · 10-06-2008, 01:23 PM

Please Login or Register  to view this content.

**rwgrietveld** · 10-06-2008, 01:36 PM

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

**rgold** · 10-06-2008, 01:51 PM

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

**rgold** · 10-06-2008, 02:41 PM

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

**mdbct** · 10-06-2008, 02:47 PM

Put the criteria between a set of {} s

Please Login or Register  to view this content.

would become

Please Login or Register  to view this content.

**rgold** · 10-06-2008, 03:01 PM

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

**mdbct** · 10-06-2008, 03:08 PM

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

Please Login or Register  to view this content.

**rgold** · 10-06-2008, 03:28 PM

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

**mdbct** · 10-06-2008, 03:32 PM

Change this

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

SUMPRODUCT doesn't play well with wildcards.

**rgold** · 10-06-2008, 03:37 PM

Successful!!! Thank you again! Great site!

**rgold** · 10-06-2008, 03:42 PM

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

**mdbct** · 10-06-2008, 04:40 PM

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:

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

**rgold** · 10-06-2008, 05:22 PM

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

**mdbct** · 10-06-2008, 08:59 PM

Try this one:

Please Login or Register  to view this content.

**rgold** · 10-07-2008, 10:26 AM

Thank you! It worked. Still building my formula skills.

**HARLEY0627** · 10-24-2008, 09:32 PM

Okay, perhaps I'm just missing something here. It seems quite simple to convert the COUNTIFS function to a 2003 equivalent but I'm missing it.

I have a worksheet with several thousand rows of data and need to get a count of one column ("C") with dates that fall between a certain range.

I got the following example to work in 2007 but am not sure how to convert it for 2003:

=COUNTIFS(C:C,">=9/1/2008",C:C,"<=10/17/2008")

Any help would be greatly appreciated.

**mdbct** · 10-24-2008, 10:08 PM

One way:

Please Login or Register  to view this content.

**HARLEY0627** · 10-24-2008, 11:02 PM

Thank you so much for your quick reply. That worked exactly as expected!!!

**mbond380** · 07-21-2011, 03:17 PM

I am trying to convert this to 2003 in a hurry. My 4th up manager doubts I can get this done.... at all! Any help will help. Thank you.

=_xlfn.COUNTIFS(DATA!$BS$5:$BS$1000,TRUE,DATA!$D$5:$D$1000,'summary NEW'!E$8,DATA!$C$5:$C$1000,1)

**mdbct** · 07-21-2011, 03:33 PM

You should really have started a new thread. Threads marked as solved are often ignored.

Try this:

Please Login or Register  to view this content.

**mbond380** · 07-21-2011, 06:54 PM

This did not work. It is giving me a 1 as a result.

This is the information I am trying to count:

DATA!$BS$5:$BS$1000 is either "TRUE" or "FALSE"
DATA!$D$5:$D$1000 is a year (2007)
DATA!$C$5:$C$1000 is either a "1" or "0"

**mdbct** · 07-21-2011, 09:15 PM

Actually it is working. If it were not working, the formula result would either be an error or 0 (zero). It is finding one occasion where a row has a TRUE (the Boolean, not the text "TRUE") value in the BS column, a value in the D column that matches the value in E8 on the "summary New" tab and a 1 (the number, not a text "1") in the C column.

I've attached a sample sheet. To eliminate scrolling, I changed the BS column to the B column and linked the value on the summary New tab to cell B2 on the Data tab.