How can I use the * wildcard in an array formula

B

Bruce

Hi, I am trying to do a sumif in an array formula with 2 conditions.

In the second condition I am using the * wildcard to get cells that contain
the characters Buy.

The formula below entered as an array formula returns 0. I have confirmed
that the array formula is treating the * literally, as a character rather
than a wildcard.

Is there a way around this?

=SUM(IF((G$16:G$1000="Share Trade")*(H$16:H$1000="*Buy*"),I$16:I$1000))

Regards,

Bruce
 
J

Jacob Skaria

Try the below

=SUMPRODUCT((G$16:G$1000="Share Trade")*
(ISNUMBER(SEARCH("Buy",H$16:H$1000))),I$16:I$1000)

If this post helps click Yes
 
A

Ashish Mathur

Hi,
You may use this formula. C17 contains Share Trade and D17 contains buy

=SUMPRODUCT(($G$16:$G$1000=C17)*(ISNUMBER(SEARCH(D17,$H$16:$H$1000,1)))*($I$16:$I$1000))

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
B

Bruce

Cool - thanks guys

Bruce

Ashish Mathur said:
Hi,
You may use this formula. C17 contains Share Trade and D17 contains buy

=SUMPRODUCT(($G$16:$G$1000=C17)*(ISNUMBER(SEARCH(D17,$H$16:$H$1000,1)))*($I$16:$I$1000))

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
Click to expand...
 

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

Excel Wildcard in Excel array formula 0
how to use * wildcard in a sum(if((cond),range)) array formula 3
Can I use wildcard characters in array formulas 1
referencing the value of a cell containing an array formula 4
How do I use wildcard characters in Excel 2003 sumif formula? 1
count(if(... using array formula: can I use a named range in my ca 1
How can I use and indexed value as the name of an array for vlooku 2
Wildcard not working in SumProduct Array 8

Top