+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Registered User
    Join Date
    11-05-2012
    Location
    Hurwenen, The Netherlands
    MS-Off Ver
    Excel 2007
    Posts
    3

    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.

    Thanks in advance,

    Greetings,
    Quuador

  2. #2
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

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

    Try

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

  3. #3
    Registered User
    Join Date
    11-05-2012
    Location
    Hurwenen, The Netherlands
    MS-Off Ver
    Excel 2007
    Posts
    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,
    Quuador
    Last edited by Quuador; 11-05-2012 at 05:50 AM. Reason: Added current formula

  4. #4
    Registered User
    Join Date
    11-05-2012
    Location
    Hurwenen, The Netherlands
    MS-Off Ver
    Excel 2007
    Posts
    3

    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,
    Quuador

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1