Apple Event: May 7th at 7 am PT
I need to add cells in column "H" if the cell in the same row, column "C" contains the word "Mag". The cells in column "C" can contain multiple words, so the formula would have to look for "Mag" in the whole cell and not disregard it because it is not a perfect match. Also I would like a formula that would do the opposite, that is add the cells in column "H" if the cell in the same row, column "C" did not contain the word "Mag". Thanks!
The only thing i can think of right off is to use the SEARCH function to determine if the phrase is in the cell opposite, then do a countif to see how many are greater than zero. Search returns the first character number if the string is found in the cell, and an error if it is not found. so i surround it with a IFERROR.
something like this, in an extra column (maybe a hidden D column) place:
in D2 =iferror(search("mag",c2),0)
Then in another cummary cell
=countif(D:D,">0")
Hope that helps,
Jason
Assuming you're looking to sum values in H rather than count cells, you could do something like this:
The formula in E2, copied down:
=IFERROR(IF(SEARCH(E$1,C2)>0,TRUE,FALSE),FALSE)
Then where you want your subtotal for rows with 'Mag' in column C, you could do this:
The formula in A3, copied to B3, is: =SUMIF(Table 1::$E,A$2,Table 1::$H)
B3 answers the second part of your question, how to sum the values in H where C does not contain 'Mag'.
SG
This works perfectly! Thanks!
sum cells based on text in different cell
