Wildcards in Array Function - NEWBIE

[seals]

Can anyone help a newbie?

I am trying to count the number of records that match two criteria as follows:

=SUM(IF(ORB!F10:F900="Super critical",IF(ORB!O10:O900="DLARA",1,0)))

This works fine, however I would like to use a wildcard for "DLARA" as such "*ARA", but sadly it doesn't like that much. Does anyone have any sugestions?

Thank you in advance.

Mark

 

[fairwinds]

Welcome!

Try:

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

This one does not need to be array entered.

 

[seals]

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

 

[seals]

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,

 

[fairwinds]

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))))

 

[Ekim]

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

 

[seals]

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

 

[licecomb]

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

 

[Vis]

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