Using a range with SUMIF
 

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

Using a range with SUMIF
 

Hi Andy

try:
=SUMPRODUCT(--(ISNUMBER(MATCH(lookup_range,criteria_range,0))),s um_rang
e)

--
Regards
Frank Kabel
Frankfurt, Germany

"Andy" schrieb im Newsbeitrag
...
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

Using a range with SUMIF
 

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

"Frank Kabel" wrote:

Hi Andy

try:
=SUMPRODUCT(--(ISNUMBER(MATCH(lookup_range,criteria_range,0))),s um_rang
e)

--
Regards
Frank Kabel
Frankfurt, Germany

"Andy" schrieb im Newsbeitrag
...
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

Using a range with SUMIF
 

Hi
for an explanation see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany

"Andy" schrieb im Newsbeitrag
...
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

"Frank Kabel" wrote:

Hi Andy

try:

=SUMPRODUCT(--(ISNUMBER(MATCH(lookup_range,criteria_range,0))),s um_rang
e)

--
Regards
Frank Kabel
Frankfurt, Germany

"Andy" schrieb im Newsbeitrag
...
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