Without using VBA, is there a way to count how many GREEN cells there are in a row and display the sum of said green cells in Cell names Total

Kevalson

New Member
Joined
Apr 16, 2002
Messages
22
We'll start there in the subject.
I have been racking my brains.

In a row, you have a persons name.
I want next to that name how many GREEN cells are on that row that he is on.
He is on A4.
The data range for his name is from L4 to NL4.
Come cells have an orange, others yellow. Just want the green one summed up for now.

So for instance is he has 8 green cells in that range.
I would like it to display 8 in D4.

Confusing enough?

No VBA unfortunately.

My best to you all...

Kevalson
 
Last edited:

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,832
Office Version
365
Platform
Windows
How are the cells getting their colour?
Manually, or via conditional formatting?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,700
Office Version
365
Platform
Windows
Take a look here: https://trumpexcel.com/count-colored-cells-in-excel/

Also, if the cells are colored by Conditional Formatting, you should be able to use the same conditions used in Conditional Formatting in COUNTIF, COUNTIFS, or SUMPRODUCT formulas to get the counts.
 

Kevalson

New Member
Joined
Apr 16, 2002
Messages
22
Manually
And it needs to count on the row., not column.
So how many green cells are from L4 through NL4 for instance.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,832
Office Version
365
Platform
Windows
You could use the Get.Cell approach
In the Name manager select "New" give it a name (I've used IsColoured) and enter this formula
=GET.CELL(63,INDIRECT("rc[-365]",FALSE))
Then in column NM put =IsColoured and fill right to column ABM
You than then use a countif to count those cells.

However changing a colour will not force a recalculation so you will have to do that yourself. It is also likely make your workbook a lot larger & possibly slower.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
What is the criterion (logic) that is used to manually color cells green? Greater than a certain value? Does it contain some keyword? Or any other criteria (?)
I believe they are not randomly colored, or are they?

M.
 

Kevalson

New Member
Joined
Apr 16, 2002
Messages
22
no not randomly.

It's a calendar that is someone takes a day off with pay, we manually turn that cell green on that particular date.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
no not randomly.

It's a calendar that is someone takes a day off with pay, we manually turn that cell green on that particular date.
Then you should use the solution suggested above by Fluff or VBA. Unfortunately, as far as I know, a formula can't identify a colored cell.

M.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,832
Office Version
365
Platform
Windows
Are the cells empty?
 

Watch MrExcel Video

Forum statistics

Threads
1,102,633
Messages
5,488,017
Members
407,617
Latest member
Samanthad2007

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top