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

Sundance_Kid

Board Regular
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

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

kweaver

Well-known Member
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

Board Regular
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
BCDEFGHIJKLMN
2Row LabelsSum of Jan-21Sum of Feb-21Sum of Mar-21Sum of Apr-21Sum of May-21Sum of Jun-21Sum of Jul-21Sum of Aug-21Sum of Sep-21Sum of Oct-21Sum of Nov-21Sum of Dec-21
3Alex161718192021222324252627
4Andrew171921232527293133353739
5David456789101112131415
6Derek123456789101112
7Donal151617181920212223242526
8Ellen181920212223242526272829
9Frank111213141516171819202122
10Frankie121314151617181920212223
11Maria91011121314151617181920
12Mary2345678910111213
14Paul789101112131415161718
15Robbie8910111213141516171819
16Sarah101112131415161718192021
17Stephen5678910111213141516
18Stuart171819202122232425262728
19Timmy67891011121314151617
20Grand Total171189207225243261279297315333351369
21
22
23
24
25Alex
26Andrew#VALUE!
27David
28Derek
29Donal
30Ellen
31Frank
32Frankie
33Maria
34Mary
36Paul
37Robbie
38Sarah
39Stephen
40Stuart
41Timmy
Sheet1
Cell Formulas
RangeFormula
C26C26=SUMIFS(C3:D19,B3:B19,B25)

kweaver

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

Sundance_Kid

Board Regular
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

Well-known Member
Is this what you mean?

Book1
BCDEF
25Alex33786978
26Andrew369693111
27David9303342
28Derek3182433
29Donal31746675
30Ellen37867584
31Frank23585463
32Frankie25625766
33Maria19504857
34Mary5222736
36Paul15424251
37Robbie17464554
38Sarah21545160
39Stephen11343645
40Stuart35827281
41Timmy13383948
Sheet1
Cell Formulas
RangeFormula
C25:C41C25=SUMPRODUCT((\$B\$3:\$B\$19=\$B25)*(C\$3:D\$19))
D25:D41D25=SUMPRODUCT((\$B\$3:\$B\$19=\$B25)*(E\$3:H\$19))
E25:E41E25=SUMPRODUCT((\$B\$3:\$B\$19=\$B25)*(I\$3:K\$19))
F25:F41F25=SUMPRODUCT((\$B\$3:\$B\$19=\$B25)*(L\$3:N\$19))

Sundance_Kid

Board Regular
I think that is it. Thank you very much.

Replies
11
Views
293
Replies
16
Views
572
Replies
3
Views
634
Replies
32
Views
1K
Replies
3
Views
184

1,191,092
Messages
5,984,634
Members
439,896
Latest member
SquareCare

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back