Alternative to adding multiple SUMIFS?

DoktorPhill

New Member
Joined
Sep 23, 2020
Messages
3
Office Version
  1. 365
  2. 2010
  3. 2007
Platform
  1. Windows
This is an hourly results table which can be extended quite far to the right, I want to add the values which correspond to all instances of each code (A,B,C etc), so in the table below "A" would be 1050 (100+100+200+250+100+300).
The codes are listed in a table elsewhere and the total will be next to them.

Currently I'm achieving this by using SUMIF($E$7:$E$16, $B41,$C$7:$C$16)+SUMIF($H$7:$H$16, $B41,$F$7:F$16)+SUMIF.... 24 times.. (B41 is the cell containing "A") but that seems inefficient

Export.jpg


Is there a way to say, combine the whole table into one range and find the values at RC[-2] of each "A" and sum them?
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,943
Office Version
  1. 365
Platform
  1. Windows
You could use
Excel Formula:
=SUMIF($E$7:$Z$16, $B41,$C$7:$X$16)
This method will not always work if there is the possibility of other intermediate columns containing numeric values and entries that meet the criteria but with your layout there would be no problems.
 
Solution

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,079
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You could do something like:

=SUMPRODUCT(($E$6:$Z$6="G")*($E$7:$Z$16="A")*($C$6:$X$6="C"),($C$7:$X$16))

note the the second pair of ranges are two columns to the left of the first, but the same size. This should allow for the possibility of any A values appearing in other columns.
 

DoktorPhill

New Member
Joined
Sep 23, 2020
Messages
3
Office Version
  1. 365
  2. 2010
  3. 2007
Platform
  1. Windows
You could use
Excel Formula:
=SUMIF($E$7:$Z$16, $B41,$C$7:$X$16)
This method will not always work if there is the possibility of other intermediate columns containing numeric values and entries that meet the criteria but with your layout there would be no problems.

Thanks Jason, I knew it would be simple, but not THAT simple. Works perfectly.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,943
Office Version
  1. 365
Platform
  1. Windows
You're welcome!

To follow up on my comments that the method might not always work, it will sum any numbers which are 2 cells to the left (based on the current ranges) of a cell containing the criteria.
With your layout this would mean that if you had "A" in one of the HC columns with a number in the same row of the previous G column then it would be included in the total. Looking at your example I wouldn't expect that to happen but if it is possible then the method that @RoryA suggested would prevent the error.
 

DoktorPhill

New Member
Joined
Sep 23, 2020
Messages
3
Office Version
  1. 365
  2. 2010
  3. 2007
Platform
  1. Windows
It still works with numbers in the HC columns as intended, and it is all Data Validated so letters cant be in the C or HC columns. Thanks again.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,661
Messages
5,597,435
Members
414,142
Latest member
Banyangt

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
Top