Sumif in a RYG symbol column

M_L

Hello,

I have a formula that returns a Red, Yellow, or Green icon depending on criteria in another column. Red, Yellow or Green correspond to a priority in our work order flow.

Then, in a metrics summary sheet, I have a Sumif formula based on the status (Red, Yellow, or Green). At times, we need to override the formula, and change the priority. This replaces the formula with just a plain color ball.

I believe this override is breaking the sumif function, since when I do a manual sum of all items with a yellow status, I receive a larger number than the sumif function returns.

The column was restricted to symbols only, but even with that removed the sumif does not appear to capture all the hours.

Formula for Priority:

=IF([Priority Lookup]5 = "RED", "Red", IF([Priority Lookup]5 = "YELLOW", "Yellow", "Green"))

Formula for Sumif (I tried two types, one with the @cell mention) both with the same result.

=SUMIF({Production Sheet}, "YELLOW", {Production Hours})

=SUMIF({Production Sheet}, @cell = "Yellow", {Production Hours})

Any ideas on why the sumif will not work? Or other options I might explore to attain the correct sum for a certain priority?

-Matt

Paul Newcome

Can you provide a screenshot of the source data as well as the formula "in action"?

How exactly is the data being summed populated? Is it manual entry, through a form, or a formula?

M_L

Hi Paul,

The RYG column Priority is looking at the Priority Lookup column and determining what color to be. The Lookup is coming from an external report that we copy and paste into a reference sheet.

=IF([Priority Lookup]75 = "RED", "Red", IF([Priority Lookup]75 = "YELLOW", "Yellow", "Green"))

The data being summed is is the Hours column, and is a basic formula in the sheet. The formula is dividing units by PPH (parts per hour)*efficiency factor = Hours

We want a sum of hours for all priority Red, Yellow, Green.

Does this help?

Paul Newcome

What is the formula in the Hours column?

M_L

The formula is:

=(Quantity1 / (PPH1)) * 1.15

Paul Newcome

Exactly what are you selecting for each of the ranges in your cross sheet references?

M_L

I am selecting all the columns in the cross reference sheets. The columns in the cross references are used elsewhere in the destination sheet as lookups.

I named the ranges as well, so I can keep track of what sheet I am referencing.

Paul Newcome

Are you referencing two different sheets in the same formula? Are you getting an error or an incorrect number with this formula?

=SUMIF({Production Sheet}, @cell = "Yellow", {Production Hours})

M_L

I am using the same sheet, different columns for the formula.

The formula is located in a separate metrics sheet.

With the @cell formula I get an incorrect sum. It is less than the actual sum.

Paul Newcome

You said earlier that you are selecting all the columns. Are you selecting the entire sheet or just a single column for each range?

M_L

I am selecting all the columns for the vlookup to retrieve the Yellow status from another sheet, then using a sumif on that yellow status to give me hours per status.

I broke down and re-wrote all the sumif formulas, and they now are summing correctly. No clue why I was receiving an incorrect sum or what was happening earlier.

Thank you for helping me dig into the issue! Enjoy the rest of your day.