Wildcards in Array Function - NEWBIE

fairwinds

MrExcel MVP

Joined

May 15, 2003

Messages

8,638

• Apr 13, 2004

• #2

Welcome!

Try:

=SUMPRODUCT((ORB!F10:F900="Super critical")*(ISNUMBER(FIND("LARA",ORB!O10:O900))))

This one does not need to be array entered.

 

Upvote 0

S

seals

New Member

Joined

Apr 13, 2004

Messages

4

• Apr 14, 2004

• #3

Thanks for the reply,

That suggestion works but I am trying to use a wild card instead of the actual "DLARA" so I would like to pick up all the ARA's somehow.

And if I could impose , what is the best way to use the function
=SUM(IF(ORB!F10:F900="Super critical",IF(ORB!O10:O900="DLARA",1,0)))

but have a third criteria. Thanks again.

Mark

 

Upvote 0

S

seals

New Member

Joined

Apr 13, 2004

Messages

4

• Apr 14, 2004

• #4

Scratch part of the last post I have fiddled and worked it out like such:
=SUM(IF(ORB!F10:F900="Super critical",IF(ORB!O10:O900="DLARA",1,0), IF(ORB!K10:K900="4",1,0)))

However I would still like to know how to use a wildcard in the second part of the function "DLARA" something like "*ARA"



Cheers,

 

Upvote 0

fairwinds

MrExcel MVP

Joined

May 15, 2003

Messages

8,638

• Apr 14, 2004

• #5

To answer your first question:

=SUMPRODUCT((ORB!F10:F900="Super critical")*(ISNUMBER(FIND("ARA",ORB!O10:O900))))

I use the "ISNUMBER FIND" combination as you cannot use a wildcard in this function.

Added one condition should be:

=SUMPRODUCT((ORB!F10:F900="Super critical")*(ORB!K10:K900=4)*(ISNUMBER(FIND("ARA",ORB!O10:O900))))

 

Upvote 0

E

Ekim

Well-known Member

Joined

Jun 30, 2002

Messages

1,416

• Apr 14, 2004

• #6

Another way:

As noted by Fairwinds, SUMPRODUCT does not accept wildcards. However, you can use explicit matching to obtain your results.

Using columns A and B as an example:

Wildcards - SUMIF and SUMPRODUCTS.xls
	A	B	C	D	E	F	G
1	A	DLARA	1		3	3
2	B		2
3	SuperCritical	ABARA	3
4	D		4
5	SuperCritical	ARS	5
6	SuperCritical	CSARA	6
7	G		7
8	H		8
9	I		9
10	SuperCritical	XYARA	10
11
Sheet2

E1:
=SUMPRODUCT((A1:A10="Super Critical")*(RIGHT(B1:B10,3)="ARA"))

F1 (Fairwind’s method):
=SUMPRODUCT((A1:A10="Super critical")*(ISNUMBER(FIND("ARA",B1:B10))))


HTH

Mike

 

Upvote 0

S

seals

New Member

Joined

Apr 13, 2004

Messages

4

• Apr 14, 2004

• #7

To both Fair Winds and Mike,

Thank you very much for your help.

If there is anything I can do in return please let me know.


Cheers,

Mark

 

Upvote 0

L

licecomb

New Member

Joined

May 19, 2010

Messages

1

• May 19, 2010

• #8

this was of great value. one point to keep in mind, the find function is case sensitive.

 

Upvote 0

V

Vis

New Member

Joined

Nov 16, 2010

Messages

2

• Nov 16, 2010

• #9

I'm a random passer by and just wanted to say thank you, this was brilliant.

 

Upvote 0