help with nest countif

nnguys

New Member
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

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

MrExcel MVP
What is the result that you want to see with:

aeb123
kg234
qe965
aeb250
qe1001

for the "aeb" criterion?

nnguys

New Member
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

MrExcel MVP
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

Well-known Member
Try this:

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

MrExcel MVP
Try this:

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

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

steve the fish

Well-known Member
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

Only became a syntax issue.

nnguys

New Member
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

MrExcel MVP
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

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

Replies
5
Views
628
Replies
11
Views
3K
Replies
1
Views
611
Replies
3
Views
311
Replies
2
Views
215

1,191,501
Messages
5,986,923
Members
440,067
Latest member
Swatts1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back