SUMIFS formula with criteria range

mrbox

New Member
Joined
Jan 17, 2013
Messages
21
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 1Criteria 2 (region)Sum Range Return Column
00002F1-40=SUMIFS($P$2..
00002F25
00002F525
00002F650
00045F1-20
00056F1-10
00056F334
00075F1-15
00075F535
00075F640

<tbody>
</tbody>
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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)?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

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