• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

help with nested COUNT IF within an IF Statement

Jason Morris

New Member
Hey everyone,

I am trying to count the unique number of times a specific string of text ("CCO" appears within cells in either of two columns. However, it may appear in both columns, and I would not want that to count twice.

Example:
Column A Column G Desired Count
CCO CCO 1
(blank) (blank) 0
(blank) CCO 1
CCO (blank) 1

I was able to get the count from Column 2 by using =IF(ISTEXT('G&A'!G:G),COUNTIF('G&A'!G:G,"*CCO*"),0)

But I cannot seem to get Row 4 to count as 1, I tried using =IF(ISBLANK('G&A'!G:G),COUNTIF('G&A'!A:A,"*CCO*"),) but that is not working.

Any help would be greatly appreciated!!
Thanks
 
@Jason Morris

Well my formula was counting row 1 as 2 but than I saw you wanted it to count as 1 so, Discard my earlier formula and try this one:

=--OR(ISNUMBER(SEARCH("*CCO",A1)),ISNUMBER(SEARCH("*CCO",G1)))

Regards,
 
Either SM's new formula, or mine, will count row 1 as a single count.
From your example, CCO is the only value in cell. Is this correct, or is it within the cell's contents? If the latter, my formula won't work anymore, and you should use SM's.
 
Back
Top