# counting function

#### polshek

##### New Member
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
Yes, base it on whatever the conditional formatting formula is. What is the formula you are using for the conditional formatting?

#### xld

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

#### Richard Schollar

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

#### polshek

##### New Member
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

##### New Member
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

##### MrExcel MVP
=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

##### New Member
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

##### MrExcel MVP
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

##### New Member
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.

Replies
3
Views
56
Replies
11
Views
360
Replies
1
Views
78
Replies
0
Views
102
Replies
7
Views
55

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

### 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