Sumifs to count color symbols

Prashant Thankappan

Hello,

I have a spreadsheet where I am tracking initiatives by team for each month and there is a color assigned to that initiative. For each month I assign these colors. I have a total of 12 columns. Green for on track, Yellow for at-risk, Red for breached, and Gray for not started. I really wished I could add one more for Complete but that is not the question now.

I am trying to add all greens against the teams so the teams. For example, HR had initiatives to update employee data and update time management software and the first initiative is on track and the other one has breached. So I have a total count of 1 against green and 1 against red.

I will appreciate any help.

Paul Newcome

You are actually going to want a COUNTIFS.

=COUNTIFS(Jan@row:Dec@row, "Green")

Prashant Thankappan

Thank you Paul,

I think I am doing something wrong.

I see, so is there a way to get a count of greens against departments?

Prashant Thankappan

Also, note that I am trying to get the results on a different sheet.

Paul Newcome

How are you differentiating between departments? Your screenshot only shows the different months.

Prashant Thankappan

It is in another spreadsheet called "Yearly plans".

Paul Newcome

Ok. So how are we to know which "green" goes to which team?

Prashant Thankappan

My bad, I realize I wasn't clear.

I have another spreadsheet named "Dashboard data for Yearly Plans". I intend to have formulas on this spreadsheet that refers to the "Yearly Plans" spreadsheet.

Here is a complete screenshot with columns hidden to show months.

Many thanks!

Paul Newcome

My suggestion would be additional text/number helper columns. Each one would be named a separate color (Green/Yellow/Red/Grey).

In each of these columns you would use a COUNTIFS to look across the month columns to count how many are present for each color on each row.

=COUNTIFS(Jan@row:Dec@row, "Green")

Then in your summary sheet, you would use something along the lines of

=SUMIFS({Green Total Column}, {Team Column}, @cell = "Team A")

Prashant Thankappan

I replaced the symbols with RGBYS (Red, Green, Blue, Yellow, Silver) and put conditional formatting on it to change cell color accordingly. Besides the month column (at the end of December), I put 5 columns where I count each color.

I listed all the teams in my summary spreadsheet, and against it, I added 5 color columns.

Below the Green color column, I am trying to get the total count of all the PMO team activities that are Green.

I tried adding the count of colors using the sumifs formula but no go.

Paul Newcome

@Prashant Thankappan You won't be able to count based on cell fill color. You would need to use the actual "Green" value.