Count the number of times a conditional formatting formula is true

[dooro23]

I guess this should be a simple one, but I just can't get it to work...

After the quick help I got with my first problem (http://www.excelforum.com/excel-gene...ific-text.html), I want to count the number of times (per column), that the conditional formatting formula, 6StringJazzer provided me with, is true.

Please Login or Register  to view this content.

Right now I've got a "filler" solution, a correct one would show "10" in columns J and N, instead of "11".

Verfügbarkeit 2015.xlsm

[Special-K]

Just use the same condition within a COUNTIF(condition,TRUE)

[dooro23]

Unfortunately, it's not working.

Please Login or Register  to view this content.

edit: not working as in, the output is "0".

[FDibbins]

Your formula seems to have some incorrect logic....

COUNTIF() syntax...
=COUNTIF(Range-to-count, count-criteria)
=COUNTIF(J8:J37,
...AND(
......OR(J8="x",J8="~?"),
.........SUM(
............COUNTIF(J$8:J8,"x*"),
............COUNTIF(J$8:J8,"~?")
.........)<=11,
......TRUE)
...)
Seems you have a TRUE inside the AND, but it is not being tested against anythint
Also, not sure what AND(OR(J8="x",J8="~?"),SUM(COUNTIF(J$8:J8,"x*"),COUNTIF(J$8:J8,"~?"))<=11,TRUE) would return, but that is supposed to be the COUNT criteria

Perhaps just this?
=SUM(COUNTIF(J8:J37,{"x","~?"}))

[dooro23]

No, this would be, what I already have as "filler". But you're right, the ")" was in the wrong place - thank you! Didn't get better though, now it's just an error message.

So this is my formula in conditional formatting, which works perfectly fine:

Please Login or Register  to view this content.

I want to count the times it's true (per column). After the first reply (and after FDibbins' ")" hint), I tried:

Please Login or Register  to view this content.

Not working because of too many arguments.

[FDibbins]

So your answer will only ever show if it is <= 11?

[dooro23]

The conditional formatting works fine, now I want to count what I can see. I'm having a hard time explaining it another way than in my starting post.

edit: The context of what my conditional formatting formula should do and how i got helped, is in the link in my OP. If needed, I can explain it as well.

[FDibbins]

Yes, I understand your question, but I am just making sure...so, your answer will vener be greater than 11, correct?

(because of the SUM(COUNTIF(J$8:J8,"x*"),COUNTIF(J$8:J8,"~?"))<=11) part)

[dooro23]

Yes, there is a maximum of 11 "x" per column that should get "green-lighted" as attendees.

[FDibbins]

Perhaps this?
=MIN(11,SUM(COUNTIF(J$8:J$37,{"x","~?"})))

[dooro23]

No, because it should only count the "x" for which the conditional formatting is true. So I'd remove the first "~?" part, but not the second one, as a "?" still eats up one of the 11 spots.

Please Login or Register  to view this content.

So, descending the column, as long as the number of "x" and "?" is not exceeding 11, I want the "x" counted.

1	x
2	x
3	x
4	x
5	?
6	n/a
7	x
8	n/a
9	x
10	?
11	?
12	x
13	?
14	x
15	?

In this example row 13 would be the last of 11 attendees, but only seven of them are "x" - that's the number i want.

[FDibbins]

OK Im not sure how you would do this, because you need some sort of progressive formula that will stop counting 1 thing at 11, then count something else within that range

[dooro23]

Why does the conditional formatting work then?

[newdoverman]

Conditional Formatting can be thought of as a TRUE or FALSE situation. Where the condition is TRUE apply format. You can set up the formula to render a TRUE or FALSE on the worksheet but it doesn't necessarily work to give the values that determine the TRUE or FALSE without using those conditions within a function to render the actual values.

This is the Conditional formatting formula contained in an IF formula. Where the value is TRUE the value is returned.

Please Login or Register  to view this content.

This chart shows your data in column J, the TRUE/FALSE in column K and the count in column L.

	J	K	L
8	x	TRUE	1
9	x	TRUE	2
10	x	TRUE	3
11	x	TRUE	4
12	?	FALSE
13	n/a	FALSE
14	x	TRUE	5
15	n/a	FALSE
16	x	TRUE	6
17	?	FALSE
18	?	FALSE
19	x	TRUE	7
20	?	FALSE
21	x	FALSE
22	?	FALSE

[dooro23]

Ok, so no way to do it in one formula.

[newdoverman]

Try entering this in AB8 and fill down and across (8 columns for the first section. go further if required)

Please Login or Register  to view this content.

To have the count in a convenient place ( I chose AB6 and filled across) enter this formula

Please Login or Register  to view this content.

The result is:

	AB	AC	AD	AE	AF	AG	AH	AI
5	Count of "X"
6	10	11	11	11	10	11	11	11
7
8	1	1	1	1	1
9	2	2	2	2	2	1		1
10						2	1	2
11	3	3	3	3	3	3	2	3
12
13
14	4	4	4	4		4	3	4
15	5	5	5	5	4	5	4	5
16	6	6	6	6
17			7	7	5
18
19
20	7	7	8	8	6
21		8	9	9	7	6	5
22						7	6	6
23	8	9	10	10	8	8	7	7
24
25	9	10		11	9
26	10	11	11		10	9	8	8
27
28							9	9
29
30
31
32						10	10	10
33						11	11	11
34
35
36
37

Here is your workbook with the formula filled across to cover the data to column Z.

[FDibbins]

Also, the CF formulas are progressive - the range increases as you go down the column. Once the criteria is met, the CF no longer triggers. When you want to count them, your range is now no longer progressive, it is all-inclusive...
=AND(J9="x",SUM(COUNTIF(J$8:J9,"x*"),COUNTIF(J$8:J9,"~?"))<=11)
=AND(J10="x",SUM(COUNTIF(J$8:J10,"x*"),COUNTIF(J$8:J10,"~?"))<=11)
=AND(J11="x",SUM(COUNTIF(J$8:J11,"x*"),COUNTIF(J$8:J11,"~?"))<=11)

compared to...
=AND(J8:J37="x",SUM(COUNTIF(J$8:J37,"x*"),COUNTIF(J$8:J37,"~?"))<=11)

You could probably get around this using a helper for each column, but that would be a pain

[dooro23]

Guess I'm at a dead end (edit: regarding a solution without additional columns). Thanks for all the help so far!

edit: I don't want to sound unappreciative of the help, maybe I should explain why additional columns would be a problem. While I'm roughly maintaining that sheet, there are others in the board, that eventually have to add dates and if they insert a column in between, they should be able to use it as intended, without having to add any other things. While my excel knowledge is already very basic, theirs is close to non-existent - so I need a "user-friendly" solution.

Maybe i can tackle the +1-count-solution with a macro, but despite c&p-ing the ones I found, that's even more unknown territory. :D