Formula Needed
- Thread starter bloubul
- Start date
Frank
Member
Hi All
I'm in need of a formula to count columns that contains colored cells and put the count above each column, if no color than obviously = 0
I thank you.
https://www.mediafire.com/file/1ulcof00bkk5641/COLOR.xlsx/file
BlouBul
Before anyone gets involved with this , could you please respond the replies you got for your LAST enquiry ??
Frank
Member
I dont think you can do this without using VBA to identify the color index of the cell colour you are interested in. An Excel function can then be used to count the colours.
I got this off the internet to save time, the function and how to use it are included with your spreadsheet.
You end up (in cell B2) with a function =colorfunction($A$2,B3:B13,FALSE) ..copied across
Where $A$2 is the address of the cell whose colour you are interested in (it reads the index of this colour). (For the pink ones)
where B3:B13 is the range of cells you want to count over
FALSE is a switch which means count the colours.
or TRUE means sum the values of the contents of the coloured cells.
For the yellow it is ( in cell z2) =colorfunction($Y$2,Z3:Z13,FALSE) ..copied across
Here is where I got it:-
https://www.extendoffice.com/documents/excel/1155-excel-count-sum-cells-by-color.html
your file:-
https://www.mediafire.com/file/i22d3izn91opwod/COLOR.xlsm/file
Have a look and see what you think.
Frank
I got this off the internet to save time, the function and how to use it are included with your spreadsheet.
You end up (in cell B2) with a function =colorfunction($A$2,B3:B13,FALSE) ..copied across
Where $A$2 is the address of the cell whose colour you are interested in (it reads the index of this colour). (For the pink ones)
where B3:B13 is the range of cells you want to count over
FALSE is a switch which means count the colours.
or TRUE means sum the values of the contents of the coloured cells.
For the yellow it is ( in cell z2) =colorfunction($Y$2,Z3:Z13,FALSE) ..copied across
Here is where I got it:-
https://www.extendoffice.com/documents/excel/1155-excel-count-sum-cells-by-color.html
your file:-
https://www.mediafire.com/file/i22d3izn91opwod/COLOR.xlsm/file
Have a look and see what you think.
Frank
Frank
Member
Hi Frank
Thank you for the formula... I have one problem with it, when you add another row to the current selection it does not count that color cells to the current count.
BlouBul
Thats because I set the range over which it counts to the exact size of your grid. You need to change B3:B13 in the formula to B3:B10000 to cover up to 10000 rows. Same for Z3:Z13 ... copy across of course.