Sum of Cells based on Cell color format

[techgirl]

I have one workbook, with multiple sheets. Main data is on Sheet1, but I want the formula on sheet 2. I need to SUM a column of cells on sheet1, but only if it has a particular cell fill color. I will have two colors to use, but for now I am just trying to get one of them working.

Sheet 1 - Column Q (Q3:Q300)- pink fill color through column Q.
Sheet 1 - Example...Q3=11 Pink BG; Q4=22 white BG, Q5=10 Pink BG
Sheet 2 - D3, "# of items"
Sheet 2 - E3 (This is where the SUMIF calculation will go). =SUMIF(Sheet1!Q3:Q300,PinkBackground)
Sheet 2 - Answer for sumIF, if Pink BG...(Example numbers above) = 21

I just have one column with numbers, cell color is either pink or white.

I've seen this formula being explained, but I don't get any results from that.
=GET.CELL(63,!A1) + NOW()*0

 

[awoohaw]

I don't have a =GET function in my version of Excel 365? Do you have some User Defined Functions?
As far as I know there is no way to functionally assess cell formatting with a worksheet function.

I have always used the Conditional Formatting rules as my conditions in the formulas.

What is the CF formula in Sheet1!Q3, that would return a TRUE value to make the cell PINK.
and the CF formula that would make the cell TRUE to be White..
etc.

 

[techgirl]

The =Get.Cell is set up in the Name Manager. If there is an easier way to set this up would be great. I don't want to use VB code, as our facility frowns on macro enabled files. There is no CF on the cells, I think the user manaully adds the fill color.

I just need to get a total sum based off of a fill color from sheet1.

 

[Fluff]

I don't want to use VB code, as our facility frowns on macro enabled files

In that case you are out of luck. Get.Cell is an old Excel4macro function & needs to be in a macro-enabled file. That said It will not do what your want anyway.
Your only option is to use VBA.

 

[awoohaw]

@techgirl , what are the rules that the team "eyeballs" to change cell colors? If these are consistent they can be translated into conditional statements.

 

[techgirl]

There are no rules, no conditional formatting rules. The user has in columns Q and R, title row and numbers. They just know if they fill color with Pink that it is for the # of physicians, if NO fill color, it is for the # Allied Health.

 

[awoohaw]

Hummm. And there is no reference list that describes a number to "Allied Health" or to "Physicians"?
The users doing this have everyone figured out without any piece of paper to look things up, and never make a mistake?

 

[techgirl]

Column Q & R is on Sheet 1.

This is on Sheet 2. I was trying to put the get.cell formula in Column B and point to the formatting in D1 for pink and D2 for no color

# of Physicians (Type 1)		Type 1 -->	Pink color fill
# of Physicians (Type 2)		Type 2 -->	No Color
# of Allied Health (Type 1)
# of Allied Health (Type 2)

 

[awoohaw]

So how are the staff that is inputting the information to know what is a physician record and what is an allied health record. It has to be somewhere!?!?!?!

 

[techgirl]

So how are the staff that is inputting the information to know what is a physician record and what is an allied health record. It has to be somewhere!?!?!?!

They have paper records, they take the count from that and manually type in the numbers in column Q and R.