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

Sundance_Kid

Board Regular
Joined
Sep 2, 2017
Messages
128
Office Version
  1. 365
Platform
  1. Windows
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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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.
 
Upvote 0
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
13Paddy131415161718192021222324
14Paul789101112131415161718
15Robbie8910111213141516171819
16Sarah101112131415161718192021
17Stephen5678910111213141516
18Stuart171819202122232425262728
19Timmy67891011121314151617
20Grand Total171189207225243261279297315333351369
21
22
23
24
25Alex
26Andrew#VALUE!
27David
28Derek
29Donal
30Ellen
31Frank
32Frankie
33Maria
34Mary
35Paddy
36Paul
37Robbie
38Sarah
39Stephen
40Stuart
41Timmy
Sheet1
Cell Formulas
RangeFormula
C26C26=SUMIFS(C3:D19,B3:B19,B25)
 
Upvote 0
So, what do you want for Alex? The sum of just 2 months? Same for the others?
 
Upvote 0
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.
 
Upvote 0
Is this what you mean?

Book1
BCDEF
25Alex33786978
26Andrew369693111
27David9303342
28Derek3182433
29Donal31746675
30Ellen37867584
31Frank23585463
32Frankie25625766
33Maria19504857
34Mary5222736
35Paddy27666069
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))
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top