How to Create a Formula to Count Number of Text Items?

[eirlymeyer]

Excel Version: 2003
Summary: Seeking formula or function to count number of times a text appears, and to enter a count and a text reference based on specific conditions.

Details:

1. Win Calculation: (COLUMN H) A “WIN” is when all 5 filters receive a “PASS” for that selected item.
   1. If all 5 Filters receive a “Pass”, then enter: WIN.
   2. If Column H has a “Win”, then enter a “1” into a field in Column “J”
   3. If Column H has a “Miss”, then enter a “0” into a field in Column “J”
   4. Count Wins: Is there an easier formula to count just the wins.
2. Win Analysis Dashboard: (Column K): Pass/Fail Analyzing if the “WIN” count Passes or Fails. It “Passes” if there are 5 or more total WINS. .
3. # of Wins: Count/Sum the total number of “Wins”
4. Pass/Fail: If the number of Wins are 5 or more, then enter: Pass

Note: The data that will be filled in are the Filtered Data (Pass/Fail). The data that will be calculated are the number of Wins, and whether or not all 5 filter Pass which constitutes a WIN.

Concept: https://www.dropbox.com/s/5dzz61sp3trrx3x/analysis_windash_v2.xls?dl=0

Thanks for your help. Any guidance would be truly helpful.

							# of Wins	Pass/Fail
							7	Pass
Ranks 1	Filter 1	Filter 2	Filter 3	Filter 4	Filter 5	WIN Passes all 5 Filters
1	Pass	Pass	Pass	Pass	Pass	Win	1
2	Pass	Pass	Pass	Pass	Pass	Win	1
3	Fail	Pass	Pass	Pass	Pass	Miss	0
4	Pass	Pass	Pass	Pass	Pass	Win	1
5	Pass	Pass	Pass	Pass	Pass	Win	1
6	Pass	Pass	Pass	Pass	Pass	Win	1
7	Pass	Pass	Pass	Pass	Pass	Win	1
8	Pass	Pass	Pass	Fail	Pass	Miss	0
9	Pass	Pass	Fail	Pass	Pass	Miss	0
10	Pass	Pass	Pass	Pass	Pass	Win	1

<tbody>
</tbody>

 

[mikerickson]

If Ranks1 is in cell A3, then the "Win Passes all 5 filters" is in G3

You could put =IF(COUNTIF(B4:F4,"Pass")=5, "Win", "Miss") in G4 and drag down.
The 1/0 column is not needed

To could the number of "Win"s in column G you could use the formula =COUNTIF(G:G, "Win")

And cell I2 could hold the formula =IF(5<=COUNTIF(G:G, "Win"), "Pass","Fail")

 

[eirlymeyer]

Outstanding! Thank you so much & thanks for responding so quickly.

One question I had was:

How to display the Rank Amount (Column A) if there's a corresponding Win (Column G), to display in Column H.
Condition: If there is a WIN (Column G), display Rank # from Column A. If there's a Miss, display 0 or nothing.

Thanks again for the guidance above. That's exactly what I needed.

A	B	C	D	E	F	G	# of Wins	Pass/Fail	J	K	L
							7	Pass
Ranks 1	Filter 1	Filter 2	Filter 3	Filter 4	Filter 5	WIN Passes all 5 Filters
1	Pass	Pass	Pass	Pass	Pass	Win	1
2	Pass	Pass	Pass	Pass	Pass	Win	2
3	Fail	Pass	Pass	Pass	Pass	Miss	0
4	Pass	Pass	Pass	Pass	Pass	Win	4
5	Pass	Pass	Pass	Pass	Pass	Win	5
6	Pass	Pass	Pass	Pass	Pass	Win	6
7	Pass	Pass	Pass	Pass	Pass	Win	7
8	Pass	Pass	Pass	Fail	Pass	Miss	0
9	Pass	Pass	Fail	Pass	Pass	Miss	0
10	Pass	Pass	Pass	Pass	Pass	Win	8

<tbody>
</tbody>

 

[yesterdays]

Try this :
H4 : =IF(G4="Win",A4,0)

Hope this help

 

[mikerickson]

Try
=REPT($A6,(COUNTIF(B6:F6,"Pass")=5))