SUMIF and Absolute Value

Amy Evans

Hello,

I want to sum up the Project Remaining Hours of all "In Progress" projects. Sometimes I have negative project remaining hours, but I don't want to add the negative value.

How do I sum up the absolute value of the Project Remaining Hours in the formula below?

=SUMIFS({Project Remaining Hours}, {Project Status}, OR(@cell = "In Progress"))

Thanks,

Amy

Genevieve P.

Hi @Amy Evans

You can use @cell = "value" or simply use a comma between the {range} and the "criteria". The comma assumes an =.

You would more likely want to use @cell when you want something other than =, such as <> (not equal to).

The bit that's not necessary is the OR function. You only have one value you're looking for, so you don't need to look for "this" or "that", does that make sense?

Cheers!

Genevieve

Genevieve P.

Hi @Amy Evans

You can add in a criteria that says only Sum if the value is greater than 0. Try this:

=SUMIFS({Project Remaining Hours}, {Project Status}, "In Progress", {Project Remaining Hours}, > 0)

Cheers,

Genevieve

Amy Evans

Thank you! Your formula is much more clean than mine. I used @cell = "In Progress" but I guess that's not necessary?

Genevieve P.

Hi @Amy Evans

You can use @cell = "value" or simply use a comma between the {range} and the "criteria". The comma assumes an =.

You would more likely want to use @cell when you want something other than =, such as <> (not equal to).

The bit that's not necessary is the OR function. You only have one value you're looking for, so you don't need to look for "this" or "that", does that make sense?

Cheers!

Genevieve

Amy Evans

Yes, thank you! So much to learn. Thank you for your guidance.