Help with a formula
Hi,
I'm trying to build a heat map to show if a resource is booked more than 5 person days in a week. I have three columns. Person Days, which is numeric based on the named resource. Sprint/Week which is S1-W0 - S1-W3 and the person's name, which is Dawson, Jack. I'm trying to add the person days, by the week they're assigned, but I'm getting an unparseable error. I've tried the name with and without quotes.
I'm sure someone really good at =SUMIFS would be able to see what I'm doing wrong, but I've been trying different combinations for a few hours and can't figure this out.
=SUMIFS([Person Days]1:[Person Days]9,>1,[Sprint/Week]1:[Sprint/Week]9, S1-W0, [Resource Name (if known)]@row = "Dawson, Jack")
Thank you for checking this out for me.
Michelle
Best Answer
-
Oh, I didn't know that you couldn't mix ranges and whole columns. I'll correct the syntax and try again. Thank you for your help and for getting back to me.
Michelle
Answers
-
Try putting quotes around S1-W0.
=SUMIFS([Person Days]1:[Person Days]9,>1,[Sprint/Week]1:[Sprint/Week]9, "S1-W0", [Resource Name (if known)]@row = "Dawson, Jack")
-
Hi Paul,
Thanks for the help. I got another error though, #invalid operation. I looked up the error message and it said it's normally caused by something mistyped or signs in the wrong order. since I only have one ">" I doubt that's it and nothing is mistyped that I can tell. I'm still looking for the answer, but I appreciate you looking at it for me.
Thanks,
Michelle
-
Try changing this column name: [Resource Name (if known)]
To remove the parenthesis from it.
-
Hi, I tried. I'm still getting the error. I'm uploading an image of the sheet and the formula to see if perhaps I'm trying to evaluate something incorrectly if the SumIF expression. the correct columns are highlighted but it's still giving the #invalid operation message. Thanks
-
For some reason I am unable to open the attachment.
-
Hi Paul,
I'm going to try the attachment again. If you can open the screen shot, you'll be able to see I have the formula you suggested. I'm trying to add up how many hours a person has been assigned in a given sprint so I can see if they're overallocated. From the PDF, you can see all the columns are there, it's just the error message. Invalid operation would suggest maybe I'm trying to add something that won't add up in smartsheet. Thanks for looking at this.
-
My apologies. I missed a couple of other issues.
Proper syntax for a SUMIFS is "range, range, criteria" where as you have "range, criteria, range". Your ranges also do not match. Two of your ranges specify rows 1 - 9 and the third range is the entire column. Range size and shape must match within a function.
Try this...
=SUMIFS([Person Days]:[Person Days], [Sprint/Week]:[Sprint/Week], "S1-W0", [Resource Name]:[Resource Name], "Dawson, Jack")
-
Oh, I didn't know that you couldn't mix ranges and whole columns. I'll correct the syntax and try again. Thank you for your help and for getting back to me.
Michelle
-
It worked! thank you again for your help. 😀
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!