Countif/Sumif/Sumproduct under 80 based on wild card value

hi everyone,

I have the following scenario:

Search value: OR00&"*" (so any values with OR00 in them)
Range of search: A:A (Search value will search in this range)
Range of values to be counted if under 80: G:G (if the search value number in G:G is below 80, then the instances is counted)

I'm trying to create a formula which will count the number of times any value in range A:A that has OR00 in it is below 80 in range G:G. Any ideas?

Thanks!

Originally Posted by Groovicles

Search value: OR00&"*" (so any values with OR00 in them)

By 'in them' do you mean the string OR00 could appear anywhere within the cell like abcOR00def?
Because you posted OR00&"*"
By having the wildcard only at the end, then it will only count cells that 'Begin with' OR00, not necessarily any that have it 'in them'

If that is what you meant, count cells that 'Begin With' OR00, then
Try

=COUNTIFS(A:A,"OR00*",G:G,"<80")

If you really meant OR00 could be anywhere in the string like abcOR00def, then add the wildcard before the O as well

=COUNTIFS(A:A,"*OR00*",G:G,"<80")

Hi Jonmo,

thanks. Yes I mean that it would BEGIN with OR00. Thank you for your formula, but could you provide me with an Excel 2003 equivalent?

Try

=SUMPRODUCT(--(LEFT(A1:A100,4)="OR00"),--(G1:G100<80))


Note, you should update your profile to indicate using xl2003, or if you use multiple versions, specify in your original posts when you need xl2003 compatible solutions.

That's it! Thanks very much, Jonmo.

You're welcome.