shorten formula if possible

[John Caines]

Hello All;
I have a formula for a cell that will show the number of entries in the mentioned cells.
Formula is;

=COUNTA(C5:C57,E5:E57,G5:G57,I5:I57,K5:K57,M5:M57,O5:O57,C62:C111,E62:E111,G62:G111,I62:I111,K62:K111,M62:M111,O62:O111,C116:C165,E116:E165,G116:G165,I116:I165,K116:K165,M116:M165,O116:O165,C170:C219,E170:E219,G170:G219,I170:I219,K170:K219)

Isn't it long and ugly!

Is there any way to shorten this?
Basically its data all in columns C (Miss column) then E (miss column) then G etc etc,,,I'm wondering if there is any way this can be shortened?
It's above me I'm affraid,, not sure even if COUNTA is the best way to do this,, I did try watching a youtube tutorial that spoke about AREA,,,, but I couldn't get that to work!

Any help here would be very appreciated.
Yours sincerely
John C

 

[Michael M]

Maybe

=COUNTA(C:C,E:E,G:G)

 

[John Caines]

Maybe

=COUNTA(C:C,E:E,G:G)

Hi Michael,
Many thanks for your reply.

I have in column C for example headings in cells C61, and C115 and C169, so these have to be left out (they can't be counted).
Or another way of putting it maybe is,,,,,
Rows 4, 61, 115, and 169 cannot be counted.(As these have headings)
(Row 4 can be excluded as in the formula I had it starts from Row 5).

Would this help in creating a smaller formula Michael?

Many thanks Michael
John C

 

[Michael M]

OK, maybe this then

=COUNTA(C:C,E:E,G:G)-COUNTA(4:4,61:61,115:115,169:169)

 

[Michael M]

Further question....what is the reason to miss columns D, F, etc ?
Are they formulas or text or soemthing else.
I was thinking you may be able to apply a VBA solution !!

 

[John Caines]

Further question....what is the reason to miss columns D, F, etc ?
Are they formulas or text or soemthing else.
I was thinking you may be able to apply a VBA solution !!

Hi Michael,
Thanks again for your reply.
No,, the are just black columns to make the spreadsheet look nice!
B,D,F,H,J,L,N,P all blank columns set narrow, 1'1' wide filled in black,,, purely asthetic.

VBA would be fine, but if there is also a shorter formula without VBA would be fine as well,,, hell both will do!

Many thanks again Michael
Best regards
John C

PS Just to add,
I've just seen your code Michael,,,
I tried;

=COUNTA(C:C,E:E,G:G,I:I,K:K,M:M,O:O)-COUNTA(1:4,58:61,112:115,166:169)

But returned zero value
The cell with the formula in is cell E2,,,,
I thought I adjusted it ok, but it didn't return the correct value in the formula cell .

Many thanks again Michael.
Off to bed soon, getting late her in UK now.
Many thanks
John C

 

[Michael M]

Ok....but you can't put the formula in a column that is included in the count or you will get an error

=COUNTA(C:M)-COUNTA(4:4,61:61,115:115,169:169)

 

[Michael M]

Can't go in a row that is affected by the formula either....your formula works fine for me !!!

 

[John Caines]

Hi Michael.
Here's a dropbox link if it helps to my workbook
https://www.dropbox.com/s/0byhi9heee81stp/I Am - Beta 1.xlsx?dl=0

I'm falling asleep here now on the keyboard, it's been a long day, I can't drink any more coffee, I'm buzzing like a fridge already!

The formula is in Cell E2, that's the problem I think.


I'll have to get back online in the morning Michael.

many thanks for trying to help.
I'm sure there's a way around it somehow!

A very grateful
John C

 

[Michael M]

If this formula covers your data range

=COUNTA(C:M)-COUNTA(1:4,58:61,112:115,166:169)

put the formula in say cell A7 and see if it works !!!