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

#### K415

##### New Member
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
37.2 KB · Views: 32

### 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
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
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
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
That's strange I don't know why its not working for me.

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

Replies
2
Views
161
Replies
6
Views
471
Replies
3
Views
53
Replies
3
Views
63
Replies
3
Views
121

### Forum statistics

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.

### Which adblocker are you using?

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