Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to use * wildcard in a sum(if((cond),range)) array formula
Hi,
Can anyone tell me howcome the * wildcard does not work in my sum if array formula. I realise that it will work with the standard SUMIF function but I need to use the array version because I will be adding more conditions to what is below. =SUM(IF(('F1'!$C$7:$C$1000="*wedge*"),'F1'!$U$7:$U $1000)) Bruce |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to use * wildcard in a sum(if((cond),range)) array formula
Simple equality relationships do not support wildcards.
-- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Bruce" wrote in message ... Hi, Can anyone tell me howcome the * wildcard does not work in my sum if array formula. I realise that it will work with the standard SUMIF function but I need to use the array version because I will be adding more conditions to what is below. =SUM(IF(('F1'!$C$7:$C$1000="*wedge*"),'F1'!$U$7:$U $1000)) Bruce |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to use * wildcard in a sum(if((cond),range)) array formula
=SUM(IF(ISNUMBER(FIND("wedge",'F1'!$C$7:$C$1000)), 'F1'!$U$7:$U$1000))
replace FIND with SEARCH if you want it not case-sensitive. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Bruce" wrote in message ... Hi, Can anyone tell me howcome the * wildcard does not work in my sum if array formula. I realise that it will work with the standard SUMIF function but I need to use the array version because I will be adding more conditions to what is below. =SUM(IF(('F1'!$C$7:$C$1000="*wedge*"),'F1'!$U$7:$U $1000)) Bruce |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to use * wildcard in a sum(if((cond),range)) array formula
You might try something like this *array* formula:
=SUM(IF(ISNUMBER(SEARCH("wedge",$C$7:$C$1000)),$U$ 7:$U$1000)) Since you say that you'll be adding more conditions, why not try a non-array SumProduct approach: =SUMPRODUCT((ISNUMBER(SEARCH("wedge",$C$7:$C$1000) ))*$U$7:$U$1000) Where adding additional criteria is as easy as adding another argument ... something like this: =SUMPRODUCT((ISNUMBER(SEARCH("wedge",$C$7:$C$1000) ))*($D$7:$D$1000=condition 2)*($E$7:$E$1000=condition3)*$U$7:$U$1000) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Bruce" wrote in message ... Hi, Can anyone tell me howcome the * wildcard does not work in my sum if array formula. I realise that it will work with the standard SUMIF function but I need to use the array version because I will be adding more conditions to what is below. =SUM(IF(('F1'!$C$7:$C$1000="*wedge*"),'F1'!$U$7:$U $1000)) Bruce |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formula Not Working with Range with Formulas | Excel Discussion (Misc queries) | |||
Array Formula to Pick Average | Excel Discussion (Misc queries) | |||
Question regarding wildcard in multi-criteria IF formula | Excel Worksheet Functions | |||
problem with Array Formula | Excel Worksheet Functions | |||
What instead of an array formula part 2 | Excel Discussion (Misc queries) |