Using a range with SUMIF

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
The SUMIF syntax is
SumIf( range, criteria, sum_range )
What I want to do is to use a range as the criteria.
Unfortunately the criteria I need is a large number of explicit values,
rather than being able to use wildcards like 'R*' or whatever. I have
therefore created a range which holds all of the criteria values. Is there
any way I can tie this into the SUMIF function, so that if the value finds a
match in the criteria range, then its evaluated to true and then does the
usual summing?
Manythanks in advance.
Andy
 
Hi Andy

try:
=SUMPRODUCT(--(ISNUMBER(MATCH(lookup_range,criteria_range,0))),sum_rang
e)
 
Frank,
Wow! Many thanks for that.
I've looked in help and still can't understand how it works, but it
certainly does.
Many thanks again for this post and your previous posts (I've seen your
replies dotted about quite a bit).
Regards,
Andy
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUMIF with the criteria range 4
sumif wildcard for text and numbers 2
sumif when criteria range is larger but includes criteria 1
SUMIF and SUMIFS Error 3
SUMIF criteria 1
Criteria 4
How to use Sumifs with vertically and horizontally criterias 3
Sumif number is greater than a number but less than another number 2

Back
Top