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
Message	Count	Seats of interest
Replace tray table assy 10A, 11A, 12,D	10A
Seat 1A screen INOP	10B
Seats 10A 10B, 10C 12B 12D cleaned	10C
10A seat torn, 13B stained, 10C screen INOP	11A
11B
11C
12A
12B
12C
12D

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=SUMPRODUCT(--ISNUMBER(SEARCH($G$2:$G$31,A2)),--($G$2:$G$31<>""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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!