software

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=quot;*wedge*quot;),'F1'!$U$7:$U $1000))

Bruce

Simple equality relationships do not support wildcards.--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.comquot;Brucequot; gt; wrote in message
...
gt; Hi,
gt;
gt; Can anyone tell me howcome the * wildcard does not work in my
gt; sum if array
gt; formula.
gt;
gt; I realise that it will work with the standard SUMIF function
gt; but I need to
gt; use the array version because I will be adding more conditions
gt; to what is
gt; below.
gt;
gt; =SUM(IF(('F1'!$C$7:$C$1000=quot;*wedge*quot;),'F1'!$U$7:$U $1000))
gt;
gt; Bruce
=SUM(IF(ISNUMBER(FIND(quot;wedgequot;,'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)

quot;Brucequot; gt; wrote in message
...
gt; Hi,
gt;
gt; Can anyone tell me howcome the * wildcard does not work in my sum if array
gt; formula.
gt;
gt; I realise that it will work with the standard SUMIF function but I need to
gt; use the array version because I will be adding more conditions to what is
gt; below.
gt;
gt; =SUM(IF(('F1'!$C$7:$C$1000=quot;*wedge*quot;),'F1'!$U$7:$U $1000))
gt;
gt; Bruce
You might try something like this *array* formula:

=SUM(IF(ISNUMBER(SEARCH(quot;wedgequot;,$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(quot;wedgequot;,$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(quot;wedgequot;,$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!
==============================================
quot;Brucequot; gt; wrote in message
...
gt; Hi,
gt;
gt; Can anyone tell me howcome the * wildcard does not work in my sum if array
gt; formula.
gt;
gt; I realise that it will work with the standard SUMIF function but I need to
gt; use the array version because I will be adding more conditions to what is
gt; below.
gt;
gt; =SUM(IF(('F1'!$C$7:$C$1000=quot;*wedge*quot;),'F1'!$U$7:$U $1000))
gt;
gt; Bruce