counting function

polshek

New Member
Joined
Jun 24, 2008
Messages
8
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.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Yes, base it on whatever the conditional formatting formula is. What is the formula you are using for the conditional formatting?
 
Upvote 0

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Just replicate the CF formula to do that counting over the range. What is the CF formula?
 
Upvote 0

polshek

New Member
Joined
Jun 24, 2008
Messages
8
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.
 
Upvote 0

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
=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).
 
Upvote 0

polshek

New Member
Joined
Jun 24, 2008
Messages
8
Richard,
I tried your formula, but the result returned a "0" and should have returned 43. The range is F10:F56.
Any thoughts?
Felicia
 
Upvote 0

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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...
 
Upvote 0

polshek

New Member
Joined
Jun 24, 2008
Messages
8
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.
 
Upvote 0

Forum statistics

Threads
1,191,518
Messages
5,987,066
Members
440,074
Latest member
Emmanuelian

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top