Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

How can I use the * wildcard in an array formula

1,657 views
Skip to first unread message

Bruce

unread,
Nov 18, 2009, 3:09:01 AM11/18/09
to
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

Jacob Skaria

unread,
Nov 18, 2009, 3:16:01 AM11/18/09
to
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
---------------
Jacob Skaria

Ashish Mathur

unread,
Nov 18, 2009, 3:20:28 AM11/18/09
to
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

"Bruce" <Br...@discussions.microsoft.com> wrote in message
news:EF302F39-43B5-4A6C...@microsoft.com...

Bruce

unread,
Nov 18, 2009, 3:57:01 AM11/18/09
to
Cool - thanks guys

Bruce

Ashish Mathur

unread,
Nov 18, 2009, 4:00:33 AM11/18/09
to
You are welcome.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Bruce" <Br...@discussions.microsoft.com> wrote in message

news:B72B0909-949E-4C69...@microsoft.com...

0 new messages