2

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)

5

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! – tehDorf Jan 9 '15 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. – Andrew Apr 19 '17 at 18:46
1

One Cell Answer:

=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.

0

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 have uploaded the solution.

0

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).

-1
=COUNTIFS(B:B,"fed",C:C,"fed")

assuming both columns have the value "fed"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.