counting function

[polshek]

I have a spreadsheet with a column (F) that is made up of the same formula dragged down. This column also has conditional formatting applied, such that it will strike out the formula response. I would like to count the number of times that the cells in the column are NOT struck through. Is this somehow possible?

Thank you in advance for your assistance.

 

[Richard Schollar]

Yes, base it on whatever the conditional formatting formula is. What is the formula you are using for the conditional formatting?

 

[xld]

Just replicate the CF formula to do that counting over the range. What is the CF formula?

 

[Richard Schollar]

Great (or puny?) minds think alike, Bob!

 

[polshek]

yes, Base It On Whatever The Conditional Formatting Formula Is. What Is The Formula You Are Using For The Conditional Formatting?

=lookup(2,1/(f396:cz396="x"),$f$4:$cz$4)

 

[polshek]

sorry, i posted the formula that i used in the cells. i don't know what the conditional formatting formula is - i set it such that if the cell value was equal to "100% SD Revised", "100% DD", or "100% SD", then strikethough the text.

 

[Richard Schollar]

=SUMPRODUCT(1-ISNUMBER(MATCH(A2:A1000,{"100% SD Revised","100% DD","100% SD"},0)))

Assuming that your cells you want to count are in A2:A1000 (amend as appropriate).

 

[polshek]

Richard,
I tried your formula, but the result returned a "0" and should have returned 43. The range is F10:F56.
Any thoughts?
Felicia

 

[Richard Schollar]

It should work as:

=SUMPRODUCT(1-ISNUMBER(MATCH(F10:F56,{"100% SD Revised","100% DD","100% SD"},0)))

A return of 0 suggests that the Match formula is finding a match in every cell (instead of just 4 of them). Why that would be the case I do not know...

 

[polshek]

I think it may be finding a match in every cell because the formula in the cells looks at a cell that has "100% SD Revised","100% DD","100% SD" text in it - so cell F10 has the formula =LOOKUP(2,1/(F10:CZ10="X"),$F$4:$CZ$4), and cells H4, I4 and J4 contain the text "100% SD Revised","100% DD","100% SD", respectively.

Is there any way around this referencing issue? thanks.