# formula to get the total count of rows containing value in excel

I have a spreadsheet with data in this format:

``````User | Group       | ParentGroup | ID
joe  | US fed grp  |  fed        | 123
bill | dev         |  IT         | 234
jim  | UK fed grp  |  fed        | 345
``````

I need a formula that would search the Group and ParentGroup columns across the entire sheet and give the total rows count of rows containing 'fed'

In the example above, the formula would return 2 (not 4, which is the number of times 'fed' appears in the entire sheet). 2 is the number of rows that 'fed' appears on.

I am trying

``````=COUNTIF(1:4, "*" & "fed" & "*")
``````

but it's returning 4 (I want 2)

There's no need for a helper column:

``````=SUMPRODUCT(--ISNUMBER(SEARCH("fed",Group & ParentGroup)))
``````
• Group refers to B2:Bn (where n is equal to or greater than the last row)
• ParentGroup refers to C2:Cn
• Group and ParentGroup must be the same size

And you can substitute a cell reference for "fed" if you want.

• +1 Great answer, nice and simple! I wish I would have looked at it more closely yesterday, could have saved myself some grief! Jan 9, 2015 at 19:11
• Amazing stuff, thanks!! Just for those who may not understand, the last parameter must be written like `A27:A36&B27:B36&....`.Too bad `COUNTIF` doesn't allow joining ranges like this, it would have solved this much more simply. Apr 19, 2017 at 18:46

``````=COUNTIF(B:C, "*fed*") - COUNTIFS(B:B, "*fed*", C:C, "*fed*")
``````

This will count the number of cells that contain `fed`, then subtract one for each row that contains two cells with `fed`. This is pretty straight forward, but won't scale well (e.g. you have 6 columns and you want to count the number of rows that contain `fed` in any of them)

Update: Scalable Version

This was really bugging me, so did some more digging and came up with this array formula:

``````=SUM(IF(ISNUMBER(SEARCH("fed", B1:B4)) +
ISNUMBER(SEARCH("fed", C1:C4)),
1, 0))
``````

Important Note: This is an array formula, so you need to press CTRL + ALT + ENTER, instead of just typing ENTER or clicking outside of the cell. You can tell if you did it right because the formula will show up `{` and `}` around it (and the result will always be 0 if you don't).

You can now expand this to take any number of columns by adding another `+ ISNUMBER(SEARCH("fed", X1:X4))` to the function. You can specify searching the entire column by using `X:X`, where `X` is the column letter, but depending on your system that may start running slow. I had a noticeable delay searching the whole column, but switching to `X1:X100` made it feel instant. I believe you will need to make the range the same for all columns (e.g. you can't have `B:B` and `C1:C10`), but I didn't test it.

Here is how I solved it.
If in column B is Group, column C is ParentGroup and in I2 is the word you are looking for, in your case fed.
Make another column named FED and enter this formula and pull it down the sheet. =IF(OR(EXACT(B2;\$I\$2);EXACT(C2;\$I\$2));1;0)
With this you will get number 1 even if there are few "fed" in each row.
Then sum the column FED.

I believe that Davidenko is correct in suggesting that you need a helper column for this.  I suggest that the formula in, say, cell `S2`, could be something like

``````=OR(ISNUMBER(SEARCH("fed", B2)), ISNUMBER(SEARCH("fed", C2)))
``````

Which will evaluate to TRUE if `B2` or `C2` contains “fed”, case insensitive.  Replace `SEARCH` with `FIND` if you want a case-sensitive test.  If you want the searched-for text to come from a cell, you can do that with, for example,

``````=OR(ISNUMBER(SEARCH(\$I\$2, B2)), ISNUMBER(SEARCH(\$I\$2, C2)))
``````

If you need to check columns `A` and `D` too, add them.

A quick & dirty shortcut would be

``````=ISNUMBER(SEARCH("fed", B2&C2))
``````

but this would count rows where the value in column `B` ended with “f” and the value in column `C` began with “ed”.  To fix that, go with something like

``````=ISNUMBER(SEARCH("fed", B2&"|"&C2))
``````

### Next,

• Fill this helper column (which I called `S` above) by dragging it down, and
• do something like

``````=COUNTIF(S:S, TRUE)
``````

Alternatively, use one of the common tricks to convert the values in column `S` to 1s and 0s, and then `SUM` it (as Davidenko suggested).

``````=COUNTIFS(B:B,"fed",C:C,"fed")
``````

assuming both columns have the value "fed"