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: 39

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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.
 
Upvote 0
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,
 
Upvote 0
Hi,

For me it's working...

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

NameRange: CriteriaRange and refer to G7:G18
 
Upvote 0
That's strange I don't know why its not working for me.

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

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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