SUMIF with a range as Criteria

tdh222 · Mar 27th, 2002, 07:52 AM

I need to do a SUMIF, except instead of using a value as criteria, i need to compare to a range of cells, and if it matches any of those cells, then I want it to be summed. I'm guessing if this can be done it would be an array formula of some kind, but I've been unable to make it work so far.

Tim Francis-Wright · Mar 27th, 2002, 08:19 AM

I put the numbers in the criteria range in B1:B5 and the numbers ot be added in D1:D5.

The following array formula seems to work:
=SUM(SUMIF(D1:D5,$B$1:$B$5,D1:D5))
(Be sure to hit control-shirt-enter instead of enter upon input.)

You can use a 2-D range in place of either $b$1:$b$5 or D1:D5.

tdh222 · Mar 27th, 2002, 01:36 PM

Now, suppose I ALSO want $c$1:$c$5 to be equal to "c" in order for it to sum, how would I incorporate that into your suggested formula?

Aladin Akyurek · Mar 27th, 2002, 01:52 PM

Quote:

On 2002-03-27 12:36, tdh222 wrote:
Now, suppose I ALSO want $c$1:$c$5 to be equal to "c" in order for it to sum, how would I incorporate that into your suggested formula?

What about:

=SUMPRODUCT((ISNUMBER(MATCH(crit-range,Range1,0)))*(Range2="c"),Range2)

where Range1 is tested whether it meets any of the conditions in crit-range, Range2 is tested whether it has "c", and Range2 is summed.

Mar 27th, 2002, 08:19 AM	#2
Tim Francis-Wright Board Regular Join Date: Feb 2002 Posts: 74	I put the numbers in the criteria range in B1:B5 and the numbers ot be added in D1:D5. The following array formula seems to work: =SUM(SUMIF(D1:D5,$B$1:$B$5,D1:D5)) (Be sure to hit control-shirt-enter instead of enter upon input.) You can use a 2-D range in place of either $b$1:$b$5 or D1:D5. __________________ "Interfere? Of course we should interfere! Always do what you're best at, that's what I say!" -- The Doctor, Nightmare of Eden

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 27th, 2002, 07:52 AM	#1
tdh222 New Member Join Date: Mar 2002 Posts: 3	I need to do a SUMIF, except instead of using a value as criteria, i need to compare to a range of cells, and if it matches any of those cells, then I want it to be summed. I'm guessing if this can be done it would be an array formula of some kind, but I've been unable to make it work so far.

Mar 27th, 2002, 01:36 PM	#3
tdh222 New Member Join Date: Mar 2002 Posts: 3	Now, suppose I ALSO want $c$1:$c$5 to be equal to "c" in order for it to sum, how would I incorporate that into your suggested formula?