Sum IF Help With a Formula - I think it is SumIF

BESP10

Hello all,

I have a sheet with a column that has this formula in a column labeled "This Month Available Hrs Remaining"

=NETWORKDAYS(TODAY(), IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1, DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 11, 1) - 1)) * 8 * Count@row

This formula lets me know how many man hours I have available in my company with team members working 40/hr weeks as a baseline

Some months we have too work much, and some months we don't have enough work.

I have also added two additional columns to the sheet: 'OT/Reduction This Month' and 'Add/Reduce Team Member This Month'. These columns are meant to be used in a formula that adjusts the value in the 'This Month Available Hrs Remaining' column based on overtime and changes in the team size.

How would I write a formula that would increase of decrease the value in the "This Month Available Hrs Remaining" column IF I made the value in the "OT/Reduction This Month" 110% (10% OT) and added one team member in the Add/Reduce Team Member This Month…or any combination of % and team member increase/decrease?

Trying to set this up for a Monday meeting so any ideas would be great and helpful

Thanks

example.pdf

KPH

Hi @BESP10

Sorry, I was typing that on my phone and the column name I needed to add took so long that I forgot a closing parenthesis when I got to the end. That should have made the formula unparseable. But if you added the missing one at the end, you would get 16. Try this:

=NETWORKDAYS(TODAY(), IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1, DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 11, 1) - 1)) * (8 + (8 * [OT/Reduction This Month]@row)) * (Count@row + [Add/Reduce Team Member This Month]@row)

KPH

Hi @BESP10

I don't think you need a SUMIF. The SUMIF function sums the value of cells if certain things are true. I think all you need is some arithmetic changes to your formula.

You have this formula that finds the number of working days between now and the month's end, and multiplies it by 8 (8 hours per day) to give the number of hours to the month's end, and by the value in the Count column.

=NETWORKDAYS(TODAY(), IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1, DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 11, 1) - 1)) * 8 * Count@row

I think Count must be a count of team members. So the result of the formula is man-hours left to the end of the month. Therefore, if you want to add more team members in the "Add/Reduce Team Member This Month" column you can just add whatever is in this column to the value in the Count column. Like this:

=NETWORKDAYS(TODAY(), IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1, DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 11, 1) - 1)) * 8 * (Count@row+[Add/Reduce Team Member This Month]@row)

You can do something similar for the "OT/Reduction This Month". Instead of multiplying by 8 hours a day, you can multiply by 8 plus the increase/decrease, 8 plus 10% of 8, for example. You might need to play with this if my understanding of your data isn't quite right, but I expect it will be something like this:

=NETWORKDAYS(TODAY(), IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1, DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 11, 1) - 1)) * (8+(8* [OT/Reduction This Month]@row)* (Count@row+[Add/Reduce Team Member This Month]@row)

I hope that is what you need for your meeting.

BESP10

OK thank you

The "=NETWORKDAYS(TODAY(), IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1, DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 11, 1) - 1)) * 8 * (Count@row+[Add/Reduce Team Member This Month]@row)"

It worked perfectly but the

"=NETWORKDAYS(TODAY(), IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1, DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 11, 1) - 1)) * (8+(8* [OT/Reduction This Month]@row)* (Count@row+[Add/Reduce Team Member This Month]@row)"

did not work, it is giving me a value of 16 for all the columns, hmnmmm

I am going to see if I can figure out the steps here

Thank you

KPH

Hi @BESP10

Sorry, I was typing that on my phone and the column name I needed to add took so long that I forgot a closing parenthesis when I got to the end. That should have made the formula unparseable. But if you added the missing one at the end, you would get 16. Try this:

=NETWORKDAYS(TODAY(), IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1, DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 11, 1) - 1)) * (8 + (8 * [OT/Reduction This Month]@row)) * (Count@row + [Add/Reduce Team Member This Month]@row)

BESP10

that is great @KPH

Thankyou

That worked

how in the world you can do that on a phone is awesome LOL

Thanks

KPH

It would be awesome if I got it right first time 🤣

Glad we got there.