Formula to Count Occurrences of 'X' Appearing Within Set Value

Hey all,

Really hard to try and explain what i need help with using the correct terminology but essentially, what i need is to be able to show how many times a specific person within the document has totally failed, in the scenario in the attached workbook, that is totally failing to eat all apples, therefore 100% apples eaten.

How can i create a count in a seperate column of how many times Person D (in the example) has eaten 100% of the apples so that i can track and trend Person Ds performance with regards to apple eating? (are you all still with me??)

Any help is greatly appreciated!

Excel Novice

Welcome to the forum.

In G2 copied down:

=IF(F2=1,COUNTIFS(B$2:B2,B2,F$2:F2,1),"")

=countifs($b$2:$b$13,"d",$f$2:$f$13,1)

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

In F2 copied down:

=IF(COUNTIFS($A$2:$A$9,A2,$E$2:$E$9,"75% - 100%")=1,"",COUNTIFS(A$2:A2,A2,E$2:E2,"75% - 100%"))

I'll have a look, but please don't provide workbooks that are NOT representative of your real data - it's a waste of our time and yours.

This is even easier:

=COUNTIFS(E$2:E2,E2,I$2:I2,"75% - 100%")

That's because you have copied it incorrectly. This is the formula you have in J8:

=COUNTIFS(E$2:E5,E5,I$2:I5,"75% - 100%")

Can you see the problem? If it had been copied down correctly (by dragging) from J2, it would be this:

=COUNTIFS(E$2:E8,E8,I$2:I8,"75% - 100%")

Start again. The formula in J2 is correct - drag it down again to get this:

Excel 2016 (Windows) 32 bit

	E	I	J
1			Number of Times FULL FAIL
2	A	75% - 100%	1
3	B	75% - 100%	1
4	C	75% - 100%	1
5	D	75% - 100%	1
6	E	75% - 100%	1
7	F	75% - 100%	1
8	F	75% - 100%	2
9	F	75% - 100%	3
10	F	75% - 100%	4
11	F	75% - 100%	5
12	G	75% - 100%	1
13	H	75% - 100%	1
14	H	75% - 100%	2
15	F	75% - 100%	6
16	F	75% - 100%	7
17	F	75% - 100%	8
18	I	75% - 100%	1
19	I	75% - 100%	2

Sheet: Sheet1

OK - well, I am glad you got it working.