Count the amount of times criteria appears in a cell

[aeroguy1]

Hello,

How do you count the amount of times criteria (in this case airplane seats i.e. 1A, 11A, 16C, etc.) occur in a single cell. The cell has any number of seats.

I can nest the airplane seats in a list or type it in the formula. I have 30 seats of interest.

Thank you!

 

[PaddyD]

Depends on how the data actually turns up in the cell and whether the data entry standard is reliable.

For example, if the data was always < seat number ><seatnumber>,<seatnumber>< seat number >,<seatnumber>< seat number >

...then number of seats is just number of commas +1:

=len(a1)-(len(substitute(a1,",","")))+1

...gets more complex as data gets less structured.

Post back with some real examples if it's not as simple as the above.</seatnumber></seatnumber></seatnumber>

 

[aeroguy1]

The seats appear in comment fields that are not standard.

“Replaced tray table assy 10A, 11A, 12,D”
“Seat 1A screen INOP”

I have hundreds of these cells and I would like to define seats and then have a function count the criteria in the text field.

Thank you!

Depends on how the data actually turns up in the cell and whether the data entry standard is reliable.

For example, if the data was always <seatnumber>,<seatnumber>,<seatnumber>

...then number of seats is just number of commas +1:

=len(a1)-(len(substitute(a1,",","")))+1

...gets more complex as data gets less structured.

Post back with some real examples if it's not as simple as the above.

 

[PaddyD]

Tricky - if there is no standardisation in the data entry, then it is more-or-less impossible to define an algorithm - they need some regular features to work off.

Might be somewhat more tractable using vba / reg expressions but those aren't really my sort of thing...

 

[Eric W]

You could try something like:

	A	B	C	D	E	F	G
1	Message				Count		Seats of interest
2	Replace tray table assy 10A, 11A, 12,D				2		10A
3	Seat 1A screen INOP				0		10B
4	Seats 10A 10B, 10C 12B 12D cleaned				5		10C
5	10A seat torn, 13B stained, 10C screen INOP				2		11A
6					0		11B
7					0
8					0		11C
9					0		12A
10					0		12B
11					0		12C
12							12D

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula E2 =SUMPRODUCT(--ISNUMBER(SEARCH($G$2:$G$31,A2)),--($G$2:$G$31<>"")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

It searches the list in G for matches, and counts them. It won't pick up something like 12,D because it's not in the list. If that kind of thing happens a lot, you could add 12,D to the end of the list. But with non-standard entry, you might have to settle for less than 100% accuracy.

 

[aeroguy1]

Works perfectly. Thank you so much!!

 

[Eric W]

You're welcome!