Counting Cells that includes exact match and also if a cell includes specific text

1. Counting Cells that includes exact match and also if a cell includes specific text

Hi Guys

I have a requirement to count the number of cells only if they include an exact match in column C and also contain specific text within a string of text in the corresponding row of Column D.

I am trying to use the countifs function with the search function as below without success.

=COUNTIFS('Incite Raw'!\$C\$2:\$D\$12241,"="&\$B3,'Incite Raw'!\$C\$2:\$D\$12241,ISNUMBER(SEARCH(E\$1,'Incite Raw'!\$C\$2:\$D\$12241)))

Is this formula incorrect and if so what is the best way to achieve the result?

Thanks

Scott

2. Re: Counting Cells that includes exact match and also if a cell includes specific text

Hi Scott,

Welcome to the forum.

I believe an array formula can help here.. please upload a sample workbook. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

3. Re: Counting Cells that includes exact match and also if a cell includes specific text

Maybe:

=COUNTIFS('Incite Raw'!\$C\$2:\$C\$12241, \$B3, 'Incite Raw'!\$D\$2:\$D\$12241, "*" & E\$1 & "*")

NOTE: Your profile shows you to be using Excel 2003 which does not have the COUNTIFS function. You would need to convert to a SUMPRODUCT for Excel 2003. If you do have a later version, be sure to update your profile.

4. Re: Counting Cells that includes exact match and also if a cell includes specific text

You could try something like this using sumproduct,
which will work across all Excel versions:
=SUMPRODUCT(EXACT(C887:C888,"ppp")*(ISNUMBER(SEARCH("owl",D887:D888))))
Note that the ranges are single columns

NB: Replace SEARCH with FIND in the formula if you need the substring search to be strictly case sensitive

5. Re: Counting Cells that includes exact match and also if a cell includes specific text

Hi Dilipandey

Regards

Scott

6. Re: Counting Cells that includes exact match and also if a cell includes specific text

The formula I gave in post #3 is correct for that workbook. It is not an array formula, just enter it normally and copy down/across.

In fact, you don't even need to define the range, this works, too:

=COUNTIFS('Incite Raw'!\$C:\$C, \$B3, 'Incite Raw'!\$D:\$D, "*" & E\$1 & "*")

7. Re: Counting Cells that includes exact match and also if a cell includes specific text

Thanks Jerry this works well.

Thanks to others for assistance.

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

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