# SUMIFS formula with criteria range

#### mrbox

##### New Member
I'm using a SUMIFS formula to sum the values in a range based on set criteria.

So far I've got "=SUMIFS(\$P\$1:\$P\$2000,\$B\$1:\$B\$2000,"

The issue is, I only want to sum values where criteria 1 is the same and where criteria 2 is equal to F1 and F6. Any ideas?

 Criteria 1 Criteria 2 (region) Sum Range Return Column 00002 F1 -40 =SUMIFS(\$P\$2.. 00002 F2 5 00002 F5 25 00002 F6 50 00045 F1 -20 00056 F1 -10 00056 F3 34 00075 F1 -15 00075 F5 35 00075 F6 40

<tbody>
</tbody>

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Hi

it will look something like this:

=SUM(SUMIFS(\$P\$1:\$P\$2000,\$B\$1:\$B\$2000,"0002",\$C\$1:\$C\$2000,{"F1","F6"}))

I have made assumptions about which columns contain criteria 1 & 2.

Hi

it will look something like this:

=SUM(SUMIFS(\$P\$1:\$P\$2000,\$B\$1:\$B\$2000,"0002",\$C\$1:\$C\$2000,{"F1","F6"}))

I have made assumptions about which columns contain criteria 1 & 2.

Thank you! The problem is that the ID's in criteria 1 are assigned randomly. So "0002" may or may not show up. I want to sum the values in the sum range if the values in criteria 1 are matching and if the values in criteria two are equal to "F1" or "F6". So if "0001" shows up four times, for just those four values, if the associated values in "criteria 2" are "F1" or "F6" then sum the associated number values for those criteria in the "sum range". I kno

On my reading, that's what my formula does. Have you considered a pivot table with the Criteria1 and Criteria2 as Row fields and the Sum range as a Data field? This would also provide you with your values

On my reading, that's what my formula does. Have you considered a pivot table with the Criteria1 and Criteria2 as Row fields and the Sum range as a Data field? This would also provide you with your values

Thank you for your formula! It does, if you know what the values in Criteria 1 will be. Those values are auto generated, random and there are thousands. I want the formula to find values that match within Criteria 1.

If you want a formula to copy down try this version of Firefly's suggestion in row 2 copied down

=SUM(SUMIFS(P:P,B:B,B2,C:C,{"F1","F6"}))

That will sum column P where column B matches the column B value on the current row and col C = F1 or F6. Is that what you need?

If you want a formula to copy down try this version of Firefly's suggestion in row 2 copied down

=SUM(SUMIFS(P:P,B:B,B2,C:C,{"F1","F6"}))

That will sum column P where column B matches the column B value on the current row and col C = F1 or F6. Is that what you need?

It worked for one instance! I got a pop-up message stating that excel isn't capable of processing the amount of data I requested and that I should shorten the range. So I am not sure if it works for every instance, just yet.

It worked for one instance! I got a pop-up message stating that excel isn't capable of processing the amount of data I requested and that I should shorten the range. So I am not sure if it works for every instance, just yet.

Ok so it works for the most part but it also returns values I don't need. For example, if the criteria isn't met and a value is Column P is 100, it will return 100. If criteria is met, it will do the summation, also.

OK, so I'm not clear on what you want, then. For the sample given in your first post what would be the expected results (assuming what is shown is the complete data)?

Replies
3
Views
1K
Replies
1
Views
108
Replies
3
Views
388
Replies
6
Views
317
Replies
16
Views
747

1,196,215
Messages
6,014,040
Members
441,801
Latest member
Aneurysm

### 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