Sumif(s) Formula to Sum Multiple Columns with Single Criteria

[Sundance_Kid]

Hi,

I am trying to figure out the correct formula to sum multiple columns based on single criteria.

I have a list of 30 names and the range contains all 30 names with multiple columns representing months of the year.

I want to sum 3 months (3 columns) for each name(criteria) but cant seem to get it right. I can do it for a single colum but I want to sum multiple columns.

I presume it should be a Sumifs formula or maybe even a sumproduct.

I dont necessarily want to create a helper column either as the formula will be working off a pivot table.

Thanks

 

[kweaver]

Would you provide an example using XL2BB as this isn't clear (to me) that simply =SUM(B2:M2) would sum the 1st quarter for the first name in A2.

 

[Sundance_Kid]

Would you provide an example using XL2BB as this isn't clear (to me) that simply =SUM(B2:M2) would sum the 1st quarter for the first name in A2.

Hey thanks for your response. Sorry for the delay in coming back to you but I needed to figure out how to use that XL2BB

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N
2	Row Labels	Sum of Jan-21	Sum of Feb-21	Sum of Mar-21	Sum of Apr-21	Sum of May-21	Sum of Jun-21	Sum of Jul-21	Sum of Aug-21	Sum of Sep-21	Sum of Oct-21	Sum of Nov-21	Sum of Dec-21
3	Alex	16	17	18	19	20	21	22	23	24	25	26	27
4	Andrew	17	19	21	23	25	27	29	31	33	35	37	39
5	David	4	5	6	7	8	9	10	11	12	13	14	15
6	Derek	1	2	3	4	5	6	7	8	9	10	11	12
7	Donal	15	16	17	18	19	20	21	22	23	24	25	26
8	Ellen	18	19	20	21	22	23	24	25	26	27	28	29
9	Frank	11	12	13	14	15	16	17	18	19	20	21	22
10	Frankie	12	13	14	15	16	17	18	19	20	21	22	23
11	Maria	9	10	11	12	13	14	15	16	17	18	19	20
12	Mary	2	3	4	5	6	7	8	9	10	11	12	13
13	Paddy	13	14	15	16	17	18	19	20	21	22	23	24
14	Paul	7	8	9	10	11	12	13	14	15	16	17	18
15	Robbie	8	9	10	11	12	13	14	15	16	17	18	19
16	Sarah	10	11	12	13	14	15	16	17	18	19	20	21
17	Stephen	5	6	7	8	9	10	11	12	13	14	15	16
18	Stuart	17	18	19	20	21	22	23	24	25	26	27	28
19	Timmy	6	7	8	9	10	11	12	13	14	15	16	17
20	Grand Total	171	189	207	225	243	261	279	297	315	333	351	369
21
22
23
24
25	Alex
26	Andrew	#VALUE!
27	David
28	Derek
29	Donal
30	Ellen
31	Frank
32	Frankie
33	Maria
34	Mary
35	Paddy
36	Paul
37	Robbie
38	Sarah
39	Stephen
40	Stuart
41	Timmy
Sheet1

Cell Formulas
Range	Formula
C26	C26	=SUMIFS(C3:D19,B3:B19,B25)

 

[kweaver]

So, what do you want for Alex? The sum of just 2 months? Same for the others?

 

[Sundance_Kid]

Hi, typically the names will not be in order even though they are in the example.

Match the colours to sum up the relevant columns by looking up the name.

 

[kweaver]

Is this what you mean?

Book1
	B	C	D	E	F
25	Alex	33	78	69	78
26	Andrew	36	96	93	111
27	David	9	30	33	42
28	Derek	3	18	24	33
29	Donal	31	74	66	75
30	Ellen	37	86	75	84
31	Frank	23	58	54	63
32	Frankie	25	62	57	66
33	Maria	19	50	48	57
34	Mary	5	22	27	36
35	Paddy	27	66	60	69
36	Paul	15	42	42	51
37	Robbie	17	46	45	54
38	Sarah	21	54	51	60
39	Stephen	11	34	36	45
40	Stuart	35	82	72	81
41	Timmy	13	38	39	48
Sheet1

Cell Formulas
Range	Formula
C25:C41	C25	=SUMPRODUCT(($B$3:$B$19=$B25)*(C$3:D$19))
D25:D41	D25	=SUMPRODUCT(($B$3:$B$19=$B25)*(E$3:H$19))
E25:E41	E25	=SUMPRODUCT(($B$3:$B$19=$B25)*(I$3:K$19))
F25:F41	F25	=SUMPRODUCT(($B$3:$B$19=$B25)*(L$3:N$19))

 

[Sundance_Kid]

I think that is it. Thank you very much.