help with nest countif

[nnguys]

Dear all,

I have been stumped at the first stage of doing a nest if countif formula.

My problem is I need to isolate one particular group of text from other this is the if formula

I have a list like below

aeb123
kg234
qe965
aeb250
qe1001


etc

I want to isolate the aeb only but as each has a unique number after it I thought I would try "aeb*"=if(a1:a2000="aeb*" hoping it would ignore the number but it does not and return a 0 result.

This works fine in the countif section

Until I get past this bit I can continue.
Any help much appreciated

 

[Aladin Akyurek]

What is the result that you want to see with:

aeb123
kg234
qe965
aeb250
qe1001

for the "aeb" criterion?

 

[nnguys]

Hi
The end result I want is =countif(A1:A300,"AEB") giving me a total of AEB but the if command is not recognising AEB unless i type the whole number as well

 

[Aladin Akyurek]

Hi
The end result I want is =countif(A1:A300,"AEB") giving me a total of AEB but the if command is not recognising AEB unless i type the whole number as well

I'm not asking whether you have a formula. I'm asking what outcome you expect to see for the sample you posted.

 

[steve the fish]

Try this:

=COUNTIF(A1:A300,"=AEB*")

 

[Aladin Akyurek]

Try this:

=COUNTIF(A1:A300,"=AEB*")

Try to allow the OP to formulate his/her problem as asked for...

 

[steve the fish]

Hi
The end result I want is =countif(A1:A300,"AEB") giving me a total of AEB but the if command is not recognising AEB unless i type the whole number as well

Presumed he/she had here ^^

Only became a syntax issue.

 

[nnguys]

maybe I did not explain the problem well let me start again.

A B
aeb2123 5c2
qe543 5c2
kg555 5c2
aeb115 49998
qe478 49998
qe887 5na
aeb999 5na
kg12 5na

I want a countif of 5c2, 49998 and 5na but ONLY for any fields in column A that starts with aeb I thougt an If command nesting a countif would do it but cant get the IF command to work as suck "aeb*"

Hope that makes it clearer

 

[Aladin Akyurek]

maybe I did not explain the problem well let me start again.

A B
aeb2123 5c2
qe543 5c2
kg555 5c2
aeb115 49998
qe478 49998
qe887 5na
aeb999 5na
kg12 5na

I want a countif of 5c2, 49998 and 5na but ONLY for any fields in column A that starts with aeb I thougt an If command nesting a countif would do it but cant get the IF command to work as suck "aeb*"

Hope that makes it clearer

Try...

=SUM(COUNTIFS(A:A,"aeb*",B:B,{"5c2",49998,"5na"}))

 

[nnguys]

im using 2003 at work as to tight to upgrade and did not think countifs was in that version