Count the cells in a string of cells omitting zeros and specific cells

**Hawkmoth1** · 09-23-2017, 06:58 AM

Hello All - I am having a problem with some formula, which I just can’t seem to get my head around, so I wonder if someone can help me please.

I have several columns of data for which I need to count the cells which contain a figure in each column. The number of cells I need to count are from A8:A905, this is simple enough, but there are two other factors which have to be taken into consideration.

a) All the empty cells in the column contain a zero; I don’t want to count these cells. b) Also, I don’t want to count some specific cells either whether they contain a figure or not, these cells are – A206, A686, A742, A749, & A768.

I’m sure there is probably an easy answer to this, but it’s just defeating me at the moment, I would be very grateful for any assistance, thanks.

Hawkmoth1

**alansidman** · 09-23-2017, 07:21 AM

maybe: =COUNTIF(A8:A905,"<>" & 0)-5

**Phuocam** · 09-23-2017, 07:56 AM

Try this ...

=COUNT(A8:A905/A8:A905)-COUNT(A206/A206,A686/A686,A742/A742,A749/A749,A768/A768)

Enter with Ctrl+Shift+Enter.

**samba_ravi** · 09-23-2017, 08:38 AM

Try
=COUNTIF(A8:A905,"<>"&0)-SUM(INDEX((CHOOSE({1,2,3,4,5},A100,A686,A742,A749,A768)<>0)+0,0))

**Hawkmoth1** · 09-23-2017, 08:39 AM

Hello Phuocam - thanks, that's done it a treat.

I've added reputation and the post as solved.

hawkmoth1

Count the cells in a string of cells omitting zeros and specific cells

LinkBack

Thread Tools

Rate This Thread

Display

Count the cells in a string of cells omitting zeros and specific cells

Re: Count the cells in a string of cells omitting zeros and specific cells

Re: Count the cells in a string of cells omitting zeros and specific cells

Re: Count the cells in a string of cells omitting zeros and specific cells

Re: Count the cells in a string of cells omitting zeros and specific cells

Thread Information

Users Browsing this Thread

Similar Threads

Count cells with specific year and add up cells adjacent to the counted cells

Referenced cells returning zeros: can these be made blank or string data?

Function returning cells containing text and omitting blank cells.

Median formula that does not count empty cells and zeros

[SOLVED] Unable to use SUMPRODUCT to count multiple criteria with cells with 'specific text string'

[SOLVED] How do I get the average across multiple cells not in a range and not count the zeros/text

[SOLVED] Count cells with specific values in the cells next to them?

Bookmarks

Bookmarks

Posting Permissions