| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I enter formula sum(range+range)*0.15 sumif(range=3) | Excel Discussion (Misc queries) | |||
| SUMIF and range | Excel Worksheet Functions | |||
| SumIf - when I fill down the Range, Criteria & sum range changes | Excel Worksheet Functions | |||
| Sumif range? | Excel Worksheet Functions | |||
| SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
Hybrid Mode
