SUMIF w/ Negative numbers

Dan B.

Hello, I got a quick question for you amazing people. Long story short, I'm trying to create an attendance tracker that tracks things like tardiness, call outs, pto, fmla, and overtime. I feel like I got something substantial, but I'm missing a small component.

So the idea is that we track all these different occurrences and have a rolling 365 day total so we don't have to track them falling off everyday. The issue I'm running into is that we allow the employees to pick up overtime shifts to erase a point. We have 3 different values associated with Overtime: Weekday, Weekend, Holiday.

So my vision is to be able to select an occurrence from the "Occurrence column" and have it add to the rolling total.

Let's look at my formulas. Let me first state that I've only included 1 version of overtime and it's value is "1".

Occurrence Point Value

=IF(Occurrence@row = "Tardy (up to 15 minutes)", 0.4, IF(Occurrence@row = "Late (more than 15 minutes)", 0.4, IF(Occurrence@row = "Leave Early less than half shift", 1, IF(Occurrence@row = "Leave Early more than half shift", 0.4, IF(Occurrence@row = "Unscheduled Absence", 1, IF(Occurrence@row = "Unscheduled Absence on a Denied Day Off", 2, IF(Occurrence@row = "No Call No Show", 4.5)))))))

I had an additional if statement in here that was IF(Occurrence@row = "Overtime", "-1"), but this did nothing. I wasn't sure why that wasn't counting, so I decided to create an overtime column

Overtime

=IF(Occurrence@row = "Overtime", "1")

Simply put, would just show the value of the OT shift.

Occurrence Sum

=SUMIFS([Occurrence Point Value]:[Occurrence Point Value], [Employee Name]:[Employee Name], @cell = [Communicator Name]@row, [Occurrence Date]:[Occurrence Date], @cell >= TODAY(-365))

This is my rolling point total. When I had that Overtime IF Statement (inside the Point Value column) the total would not change. So this led me to my new idea, which is:

Occurrence Sum w/ Adjusted Overtime

=[Occurrence Sum]@row - Overtime@row

This is where I'm getting an #INVALID OPERATION, but I am not sure why. It should just be a simple Column A - Column B kind of thing.

Final thought:

If I could skip the creation of both and Overtime and Occurrence Sum w/Adjusted Overtime column, I would appreciate it, but I get the feeling that smartsheet is doesn't want to play nice with me.

Any help is greatly appreciated.

Genevieve P.

Hi @Dan B.

This actually has a simple solution! It all has to do with your Overtime column and formula.

In your Overtime formula, you have quotes around the number 1, which turns it into text instead of a number (see how the 1 appears on the Left side of the cell instead of the right?).

Try changing that formula to this:

Overtime

=IF(Occurrence@row = "Overtime", 1)

You should actually be able to add this back in to your original IF statement...

=IF(Occurrence@row = "Tardy (up to 15 minutes)", 0.4, IF(Occurrence@row = "Late (more than 15 minutes)", 0.4, IF(Occurrence@row = "Leave Early less than half shift", 1, IF(Occurrence@row = "Leave Early more than half shift", 0.4, IF(Occurrence@row = "Unscheduled Absence", 1, IF(Occurrence@row = "Unscheduled Absence on a Denied Day Off", 2, IF(Occurrence@row = "No Call No Show", 4.5, IF(Occurrence@row = "Overtime", -1))))))))

See the question Why are my numeric values not calculating correctly? in the Help Article Frequently Asked Questions About Using Formulas

Cheers,

Genevieve

Genevieve P.

Hi @Dan B.

This actually has a simple solution! It all has to do with your Overtime column and formula.

In your Overtime formula, you have quotes around the number 1, which turns it into text instead of a number (see how the 1 appears on the Left side of the cell instead of the right?).

Try changing that formula to this:

Overtime

=IF(Occurrence@row = "Overtime", 1)

You should actually be able to add this back in to your original IF statement...

=IF(Occurrence@row = "Tardy (up to 15 minutes)", 0.4, IF(Occurrence@row = "Late (more than 15 minutes)", 0.4, IF(Occurrence@row = "Leave Early less than half shift", 1, IF(Occurrence@row = "Leave Early more than half shift", 0.4, IF(Occurrence@row = "Unscheduled Absence", 1, IF(Occurrence@row = "Unscheduled Absence on a Denied Day Off", 2, IF(Occurrence@row = "No Call No Show", 4.5, IF(Occurrence@row = "Overtime", -1))))))))

See the question Why are my numeric values not calculating correctly? in the Help Article Frequently Asked Questions About Using Formulas

Cheers,

Genevieve

Dan B.

Brilliant! I can't believe I had it in quotations. I kept staring at it thinking about what I was missing.

Genevieve P.

I'm glad I could help! Sometimes all that's needed is an extra set of eyes. 🙂