HELP! How to Utilize Multiple Formulas to Achieve one Sum Total

[joshua.easter]

Good day everyone,

I am new to this site and I truly hope someone is able to assist with my current dilemma. I am in the process of building a spreadsheet which contains a section for providing a sum total for multiple separate departments; this section will calculate the sum total of each department via two separate formulas:

1. COUNTIF
2. GETCOLORCOUNT

The COUNTIF function will be looking for the specific department indicator/verbiage (i.e. Elect. or Marine) and the GETCOLORCOUNT will be looking for a specific color once the cell is highlighted. If I utilize each formula on its own it works with no issues. I encounter issues when trying to utilize together.

Let me see if I can provide an example: The Electrical department has a total of 100 requirements but only 30 of those are considered critical. The critical requirements will be highlighted a specific color and the total sum in the sum total section per department is to read 30 - I have included a screen shot below.

If someone is able to provide some insight as to how I am able to achieve this I would greatly appreciate it!




Annotation 2020-03-26 124912.jpg
Annotation 2020-03-26 125357.jpg

[joshua.easter]

Anyone have any ideas

[MrShorty]

I don't like to use "color" as a datum, so I would probably figure out how to build a COUNTIFS() function based on the department and whatever criteria is being used by the formatting/conditional formatting to choose the colors.

Sometimes, something like this is better approached using a pivot table.