Sum IF?

[JubberB]

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

 

[Legacy 143009]

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

MrExcelPlayground15.xlsx
	A	B	C	D	E	F	G
1	9:00 Admission	Other thing	10:00 Admission	11:00 Admission	That thing		Admissions:
2	60	68	17	35	21		3317
3	72	42	79	52	100
4	68	58	92	30	21
5	50	75	77	98	68
6	57	86	67	10	100
7	33	19	41	78	23
8	23	45	64	15	95
9	43	49	71	74	17
10	10	28	93	60	69
11	19	35	27	73	99
12	72	11	37	69	15
13	37	78	27	17	80
14	52	58	98	35	57
15	81	30	58	96	14
16	12	78	29	63	19
17	76	73	99	17	82
18	43	26	89	60	46
19	64	12	63	88	32
20	56	66	24	51	57
21	92	32	40	84	62
Sheet3

Cell Formulas
Range	Formula
G2	G2	=SUM((ISNUMBER(SEARCH("Admission",A1:E1)))*A2:E21)

 

[Fluff]

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

 

[Legacy 143009]

}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]

Just experience I suppose.

 

[JubberB]

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]

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]

MrExcelPlayground15.xlsx
	A	B	C	D	E	F	G	H	I
1	9:00 Admission rentals	Other thing	10:00 Admission	11:00 Admission	That thing slide		Admissions:		Admission
2	60	68	17	35	21		4394		Slide
3	72	42	79	52	100				Santa
4	68	58	92	30	21				Skating
5	50	75	77	98	68				Rentals
6	57	86	67	10	100
7	33	19	41	78	23
8	23	45	64	15	95
9	43	49	71	74	17
10	10	28	93	60	69
11	19	35	27	73	99
12	72	11	37	69	15
13	37	78	27	17	80
14	52	58	98	35	57
15	81	30	58	96	14
16	12	78	29	63	19
17	76	73	99	17	82
18	43	26	89	60	46
19	64	12	63	88	32
20	56	66	24	51	57
21	92	32	40	84	62
Sheet3

Cell Formulas
Range	Formula
G2	G2	=SUM(SIGN(MMULT({1,1,1,1,1},IFERROR(SEARCH(I1:I5,A1:E1),0)))*A2:E21)

 

[JubberB]

Rockin'! Thank you!