SUMIF with a range of criteria instead of using SUMIFS and doing one by one

K415

New Member
Joined
Aug 30, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I was wondering if there was a way to use the SUMIF function for a range of criteria instead of using SUMIFS and typing each criteria one by one (criteria range 1, criteria 1, criteria range 2, etc.) theoretically the criteria range for my example would remain the same.

Point being that I want to be easily able to manipulate the criteria range and have it update automatically, instead of going in to the SUMIFS function one by one and removing each one as I will be using this formula for multiple cells throughout my worksheet for different columns, etc.

Please see attached screenshot for example.

The way I have shown it is by using {} and typing in manually, but again if I want to be able to go in and update quickly when I use the same formula cross multiple cells it takes up too much time and I will be using this formula for rows of cells.

Apologies if this is a novice question, I am not an excel wiz by any means.

Thank you
 

Attachments

  • Screen Shot 2020-08-29 at 11.04.34 PM.png
    Screen Shot 2020-08-29 at 11.04.34 PM.png
    37.2 KB · Views: 32

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Tom.Jones

Active Member
Joined
Sep 20, 2011
Messages
337
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi,

Use Name Range (for Criteria Range, make dynamic if you want) instead of names, between braces.
Then you can add or delete any item in the Criteria Range.
 

K415

New Member
Joined
Aug 30, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Thanks for your response. When I name it "IG" and then try and type IG in between the brackets it gives me an error message. If I remove the brackets it gives me a 0.

Am I doing something wrong?

Thanks again,
 

Tom.Jones

Active Member
Joined
Sep 20, 2011
Messages
337
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi,

For me it's working...

=SUM(SUMIF($E$9:$E$13,CriteriaRange,$C$9:$C$13))

NameRange: CriteriaRange and refer to G7:G18
 

K415

New Member
Joined
Aug 30, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
That's strange I don't know why its not working for me.

I'll try and figure it out. Thanks for your help!
 

Watch MrExcel Video

Forum statistics

Threads
1,126,931
Messages
5,621,663
Members
415,849
Latest member
PhoenixRising2015

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