COUNTIFS not working

**nosenga** · 01-13-2011, 04:14 AM

In A2 I have a date and in A3 a text string. This continues down the sheet (A4 a date, A5 text, etc). I'm trying to figure out how to create a COUNTIFS formula that will increase the count if the date is in January (or any month for that matter) and the text equals a specific word I enter. As a start I've got:

COUNTIFS(A2,AND(A2>="1/1/2011",A2<"2/1/2011'),A3,"TEXTHERE")

However, this does not increase the count (to 1) and also does not evaluate the rest of the sheet. Any suggestions?

**zbor** · 01-13-2011, 04:18 AM

COUNTIFS is for range...

For single cell you can use:

=AND(A2>="1/1/2011",A2<"2/1/2011') + ISNUMBER(FIND("TEXTHERE", A3))

**DonkeyOte** · 01-13-2011, 04:28 AM

@zbor, you should be coercing your date strings and I suspect * rather than + the two Booleans (else it's an OR)
(COUNTIFS will interpret numbers as strings as numbers automatically - which at times is a pain)

@nosenga, if you want to process a range - given the alternating pattern:

Please Login or Register  to view this content.

note that in the above the text search is case insensitive unlike your FIND - if it's important let us know.

**nosenga** · 01-13-2011, 04:33 AM

DonkeyOte,

Thank you, that formula works great. However, I'm not sure where the end of the sheet is going to be (I'm creating this for someone else to use). Is there anyway to say A2:infinity?

**DonkeyOte** · 01-13-2011, 04:37 AM

Build in some spare capacity but entire column references aren't really viable given the alternating requirements of the ranges (A2:A100 & A3:A101)

So perhaps something like:

Please Login or Register  to view this content.

COUNTIFS is pretty efficient so large ranges like the above aren't really an issue.

Note: it is imperative that all ranges share the same dimensions - in the above that is 999999x1