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

 

[John Caines]

Hi Michael!
I managed to get it working from your formula with a slight tweak, thanks for this.
I managed to running with the formula still in Cell E2!

It is now:

=COUNTA(C5:O219)-COUNTA(58:61,112:115,166:169)

==============
That's so much shorter/better than what I originally had, great stuff!

But I also wanted to count the number of highlighted cells in the sheet.
I read on the www that it seems to be/need a VBA solution?
I read
"COUNTIF cannot be used to check for background color or any formatting"
Can't do without vba then?

I found code here (Seemed the shortest as some websites were stating large amounts of code)..
https://excelribbon.tips.net/T011725_Using_COUNTIF_with_Colors.html

So I have copied the VBA from that website into a module and..
now also in Cell O2 I have the following formula to highlight the cells with the same colour as cell N2.

=CountColorIf(N2, C5:O219)

Works great!
Would have liked the sheet (Workbook to remain a non-macro enabled workbook),,, but I don't think it's possible?

Anywway, many thanks for your help with the formulas Michael.
Very much appreciated!

Yours sincerely
A very grateful
John C

 

[Michael M]

Glad it's working for you....You are correct. You can't count colours without a custom function or macro

 

[MARK858]

John, you haven't stated why the cells are coloured in the first place. Are the cells coloured based on some condition or are they just random coloured?

 

[John Caines]

Hi Mark858,
Thanks for getting in on this Mark.

Sorry about that,, it's hard to think of everything to explain when I'm not an excel programmer,,, I think you just assume things, miss things out without thinking about it. Sorry for that. Yes, regarding the highlighted cells.
They are just cells with words in that I deem important.
So I have highlighted all cells that are important (Gold, accent4, lighter 80%), at least that what excel fill tells me they are!
I have room for a cell (N2) that I have filled in that colour,, as row 2 isn't in my lookup table,, row 2 is a heading row.
So I have referenced that via formula in Cell O2, which is

=CountColorIf(N2, C5:O274)

This returns the correct amount of highlighted cells.
It is working ok now.

Like I mentioned earlier, I found the VBA code here for it,
https://excelribbon.tips.net/T011725...th_Colors.html

It's working fine.

All that said, I am looking to transfer JUST the highlighted cells into another sheet on the same workbook (Sheet will be called "I AM_Highlighted", so I guess I'd have to achieve that via some VBA code?

The VBA code I have at the moment is;

Function CountColorIf(rSample As Range, rArea As Range) As Long
    Dim rAreaCell As Range
    Dim lMatchColor As Long
    Dim lCounter As Long

    lMatchColor = rSample.Interior.Color
    For Each rAreaCell In rArea
        If rAreaCell.Interior.Color = lMatchColor Then
            lCounter = lCounter + 1
        End If
    Next rAreaCell
    CountColorIf = lCounter
End Function

This works fine it seems!

So somehow I really would like to get these highlighted cells into another sheet.
Any ideas as I'm all eyes and ears!

Maybe it needs to be based somehow on the above code? Then again, maybe not! God knows, it's above me all this VBA stuff.

Many thanks again Mark, and everyone here.

Yours sincerely
A very grateful
John C

 

[MARK858]

Transferring them is quite simple as long as you confirm the lookup range is still C5:O219, that cell rSample is still N2 and where you want the cells placed on sheet "I AM_Highlighted" (preferably we would also like the sheet name of the sheet you are doing the transfer from).

 

[John Caines]

Hi Mark,
Yes, to all your above Mark except lookup Range is Now C5:O274
Hope you can work something out from the above.

Many thanks Mark

All the best
JohnC

 

[MARK858]

and where you want the cells placed on sheet "I AM_Highlighted" (preferably we would also like the sheet name of the sheet you are doing the transfer from).

??????

 

[Michael M]

So, if the highlighted cells are "all over the place", do that have to transfer to the other sheet in the same location ??
Might it be also easier to simply make a copy of the original sheet and then "clean up" the copied sheet.....by VBA if necessary ?

 

[John Caines]

Hi Michael/Mark,

Many thanks for getting back to me on this.

I think I best say I'll close this post for now.
I really appreciate everyone's help here on this.
I don't want to confuse matters/stretch this out and make it a nightmare for anyone.

I think 1st I'll finish filling in the main sheet 100% so I know exactly where I am with what needs to be transferred over to another tab.
2. I've already today reformatted the main spreadsheet having deleted several 'Spacing' columns that were entered purely for aesthetic reasons, so now the data entered are all in columns next to one another, again this should help the export process I think,, and it still looks fine!

So again, thank you all for your comments and help here.
It is greatly appreciated.
I will be back later for sure when I'm stuck on how to get the data out from this main sheet into a new tab.

Thanks again
Yours sincerely
A very grateful
JohnC