# Sum IF?



## JubberB (Dec 21, 2022)

Question: I have a report with several columns & rows and I want to sum the entire range if the column header (first cell in column) _contains_ certain text - meaning, Column A1 may contain "9:00AM Admission" or "10:00AM Admission", etc.  If the column header contains the word "ADMISSION" I want to sum all cells in a range that includes several columns an rows.  There are columns included in the range that do not include the word "ADMISSION" So, A1 may = "9:00AM Admission", B1 may = "Equipment Rental", C1 may = "10:00AM Admission"  So I would want to sum all rows in columns A, C, (because the headers contain "ADMISSION") but not B.  How do I do that?
Thanks!
JohnB


----------



## Flashbond (Dec 21, 2022)

```
=SUMPRODUCT((ISNUMBER(SEARCH("ADMISSION",UPPER($A$1:$C$1))))*$A$2:$C$100)
```
This is an array formula. Hitting only Enter is not enough.
You must press Ctrtl+Shift+Enter together after paste.


----------



## JamesCanale (Dec 21, 2022)

MrExcelPlayground15.xlsxABCDEFG19:00 AdmissionOther thing10:00 Admission11:00 AdmissionThat thingAdmissions:2606817352133173724279521004685892302155075779868657866710100733194178238234564159594349717417101028936069111935277399127211376915133778271780145258983557158130589614161278296319177673991782184326896046196412638832205666245157219232408462Sheet3Cell FormulasRangeFormulaG2G2=SUM((ISNUMBER(SEARCH("Admission",A1:E1)))*A2:E21)


----------



## Fluff (Dec 21, 2022)

@Flashbond as the search function is not case sensitive, there is no need to use Upper.


----------



## Flashbond (Dec 21, 2022)

Fluff said:


> }USER=143009]@Flashbond[/USER] as the search function is not case sensitive, there is no need to use Upper.


How do you memorize all? Which are, and which aren't


----------



## Fluff (Dec 21, 2022)

Just experience I suppose.


----------



## JubberB (Dec 21, 2022)

James Canale,
THANK YOU!  Worked perfectly!
One last question, please: Same desired result, but rather than searching for "Admission" I want to search for a few "keywords"?  Key words are:
Slide
Santa
Skating
Rentals

THANK YOU AGAIN!
JubberB


----------



## Fluff (Dec 21, 2022)

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


----------



## JamesCanale (Dec 21, 2022)

MrExcelPlayground15.xlsxABCDEFGHI19:00 Admission rentalsOther thing10:00 Admission11:00 AdmissionThat thing slideAdmissions:Admission260681735214394Slide372427952100Santa46858923021Skating55075779868Rentals657866710100733194178238234564159594349717417101028936069111935277399127211376915133778271780145258983557158130589614161278296319177673991782184326896046196412638832205666245157219232408462Sheet3Cell FormulasRangeFormulaG2G2=SUM(SIGN(MMULT({1,1,1,1,1},IFERROR(SEARCH(I1:I5,A1:E1),0)))*A2:E21)


----------



## JubberB (Dec 21, 2022)

Rockin'!  Thank you!


----------

