SUMIF with a range as Criteria

[tdh222]

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]

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]

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]

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.