# COUNTIF column if cell contains both "text1" and "text2"

1. ## COUNTIF column if cell contains both "text1" and "text2"

Hi,

I'm quite good with Excel formulas, but can't seem to get this one right.
I want a COUNTIF of the column A:A, when both the cell contains "Day " and "):".

Example table:
 A Week 1: Day 1 (01-01-2012): A random text containing Day Day 2 (02-01-2012): Another random text Day 3 (06-01-2012): Another random text containing ):

What I want is a formula that counts all cells in the A-column, which contains both the text "Day " and "):".
(So in the above example it would return 3.)
I've got the correct formula for one of the two strings:
=COUNTIF(A:A;"*Day *")
=COUNTIF(A:A;"*):*")
But since I've also got some cells that contains one of them, they aren't working as planned.
I can't seem to get it right for both strings at the same time. Tried to use the AND function, but that only works for a single cell (A1="text"), but not a entire column.
Tried quite a few different approaches, all failed, so if anyone could help me it would be great.

PS: A formula that uses the following conditions is even better: "Day * (*-*-**):" (with the * = a numberic value that is different per cell and ** = year + special day, like "2012 - Halloween"), but the one that just contains "Day " and "):" is already more than sufficient for me.

Greetings,

2. ## Re: COUNTIF column if cell contains both "text1" and "text2"

Try

=COUNTIF(A:A;"*Day *):*")

3. ## Re: COUNTIF column if cell contains both "text1" and "text2"

Ah.. Of course!..

I'm thinking way too difficult ..

Thank you Bob.

Btw, do you maybe know about the thing I mentioned in the PS?

=COUNTIF(A:A;"*Day *-*-*):*")
This, but then also checking for ISNUMMERIC.

Greetings,

4. ## Re: COUNTIF column if cell contains both "text1" and "text2"

Normally I'm not really a forum-bumper, but since it doesn't register my last post and still says Bob's post is the last one, I decided to add this.. (Probably happened because my internet was disconnecting at the same time I posted. )

Greetings,

There are currently 1 users browsing this thread. (0 members and 1 guests)