Thread: Converting Countifs to 2003 formula

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

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.

One way:

=SUMPRODUCT((C1:C5000>=DATE(2008,9,1))*(C1:C5000<=DATE(2008,10,17))*1)

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

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)

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

Try this:

=SUMPRODUCT((Data!$BS$5:$BS$1000=TRUE)*(Data!$D$5:$D$1000='summary New'!E$8)*(Data!$C$5:$C$1000=1)*1)

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"

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.