COUNTIF criteria, finding "Limit" but exluding "No Limit"

[Darth_Sullivan]

I have a range of data in column A and B that is pulled from many other sheets in the workbook. The data comes listed out of order, which is fine. I am trying to write a formula in another cell to give me averages for the numbered data in column B. My issue is my data in column A.

Examples of items that appear in column A are as follows:

Tournament
2/5 No Limit
2/4 NL
2/5 NL
2/4 No limit
3/6 Limit
4/8 Limit
4/8 Omaha

My formula for calculating the average for all "No Limit" type games, which is what "NL" stands for, is:

=SUM(SUMIF($A$2:$A$300,"=* NL",$B$2:$B$300)/COUNTIF($A$2:$A$300,"=* NL"),(SUMIF(A$2:A$300,"=* No Limit",$B$2:$B$300)/COUNTIF($A$2:$A$300,"=* No Limit")))/2

Which works perfectly fine for No Limit games. However, when I want to do the same for "Limit" games I can't simply use "=* Limit" as I get the "No Limit" values included in the calculation, which I don't want.

My simplified question, is there a way to have the criteria find all instances of "Limit" while excluding instances of "No Limit?"

Thank you for any help.

Also, will capitalization matter when the formula is looking for the criteria match?

 

[cyrilbrd]

...when I want to do the same for "Limit" games I can't simply use "=* Limit" as I get the "No Limit" ...

Have you tried "=Limit" hence remove the wildcard *.

 

[Darth_Sullivan]

Have you tried "=Limit" hence remove the wildcard *.

No cell says "Limit" only. The cell would say "2/5 No Limit" or "3/6 Limit" or any other combinations mentioned in the first post. I used the wildcard * before because the numbers preceding Limit or No Limit are several different combinations.

Is there a simpler way than using =countif($A2:$A300,"=* Limit")-countif($A2:$A300,"=* No Limit) ??

The result of this function is the outcome I am looking for but I'm trying to avoid having to add that much extra to the code as it seems lengthy already...

 

[cyrilbrd]

Right... try "=?/? Limit"

 

[Darth_Sullivan]

Right... try "=?/? Limit"

Thank you so much! That was the fix I was looking for! I did not know about using ? in that manner. Thank you again.

 

[cyrilbrd]

Thank you so much! That was the fix I was looking for! I did not know about using ? in that manner. Thank you again.

Most welcome. Thanks for the feedback.