# How to selectively sum multiple criteria using only one range?

1. ## How to selectively sum multiple criteria using only one range?

I have a simple problem and I don't know how to solve this issue. I've tried the sumifs function but it will only allow using multiple criteria from *different* ranges...not from the same range.

So I have two columns. One with names and the second column with prices. I want to selectively sum the prices from criteria using the names column choosing only certain names and ignoring the rest. How can I do this? Sumif only allows one criteria for one range while sumifs only allows mutliple criteria using different ranges. I need a solution to sum multiple criteria from *one* range only. Is there a solution for this?

2. ## Re: How to selectively sum multiple criteria using only one range?

oh you have i misread it!

3. ## Re: How to selectively sum multiple criteria using only one range?

You can sum for multiple criteria like this

=SUMPRODUCT(SUMIF(Names,List,Prices))

where List contains your list of names that you wish to sum for

4. ## Re: How to selectively sum multiple criteria using only one range?

You can sum for multiple criteria like this

=SUMPRODUCT(SUMIF(Names,List,Prices))

where List contains your list of names that you wish to sum for
How do I list multiple names in the sumif function using your solution? Doesn't sumif only work on one criteria?

I was thinking about something like

=sum(if((a2:a11="Tom")+(a2:a11="Jerry"),D2:D11))

But when I run that function...instead of only summing the prices for the two names I put in quotes...I get a running total listing all the names and their prices. Why isn't this one working right?

5. ## Re: How to selectively sum multiple criteria using only one range?

try
=SUM(SUMIF(\$A\$1:\$A\$10,{"martin","fred"},B\$1:B\$10))

6. ## Re: How to selectively sum multiple criteria using only one range?

Originally Posted by DorothyFan1
How do I list multiple names in the sumif function using your solution?
My suggestion assumes that you have a named range called List with the Names you need.

martin's suggestion does the same but with the names listed in the formula. If you have martin in D2 and fred in D3 then it can also be

=SUMPRODUCT(SUMIF(\$A\$1:\$A\$10,D2:D3,B\$1:B\$10))

Originally Posted by DorothyFan1
=sum(if((a2:a11="Tom")+(a2:a11="Jerry"),D2:D11))
This is also a workable solution......but it's an "array formula". Put the formula in a cell, press F2 key to select formula then hold down CTRL and SHIFT keys and press ENTER

Curly braces should appear around the formula in the formula bar and the formula should give the result you want.....

7. ## Re: How to selectively sum multiple criteria using only one range?

Originally Posted by martindwilson
try
=SUM(SUMIF(\$A\$1:\$A\$10,{"martin","fred"},B\$1:B\$10))
OMG! Thanks alot for this one. What a trick to remember!! I didn't know you could put multiple criteria in curly braces using the SUMIF function! Talk about a way to get around the sumif limitation! This is fantastic. Where did you find this trick?! I tried looking this tip up in Bill Jelen's Microsoft Excel 2010 in Depth and it doesn't even mention using curly braces as a way to do this. This tip is worth it's weight in gold! I need to remember this one. Wow.

This trick helps me get around the SUMIFS function limitation of requiring multiple ranges for different criteria. That little detail limits the SUMIFS power. This trick puts rings around that function as I rarely need to use multiple columns for criteria for summing a single range. This is an incredible find. Thanks a ton for this. Best thing about this trick is I don't have to use the Ctrl Shift Enter key to enter an array formula. I'm still kicking myself in the head why I couldn't find the curly brace trick anywhere else.

I really hope there's an explanation why Bill Jelen's book doesn't mention this curly braces trick. I'm amazed it's not mentioned at all in the function construction methodology for the SUMIF function. To be fair I don't see this information anywhere else.

A quick little note...this only works if you use the sum function in conjunction with the sumif function. I tried using it without the sum function and it ignores the second criteria. But when I use the sum function and nest the sumif formula using the curly braces trick, it works. I still can't get over how incredible this trick is. This one is going to save me a ton of grief. I've been trying without luck to using DSUM, and SUBTOTAL with filter in list...and it was just too consuming. This one really cuts down on the experimentation phase in writing a formula to conditionally sum multiple criteria in just ONE range.

Anyway, thanks for the great tip. I'll list this one as solved.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1