Sum IF Help With a Formula - I think it is SumIF
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
Best Answer
-
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)
Answers
-
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.
-
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
-
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)
-
that is great @KPH
Thankyou
That worked
how in the world you can do that on a phone is awesome LOL
Thanks
-
It would be awesome if I got it right first time 🤣
Glad we got there.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 135 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!