Help with a formula

Michelle Barnett

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

Michelle Barnett

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

Paul Newcome

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")

Michelle Barnett

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

Paul Newcome

Try changing this column name: [Resource Name (if known)]

To remove the parenthesis from it.

Michelle Barnett

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

Smartsheet formula error 062321.ppt

Paul Newcome

For some reason I am unable to open the attachment.

Michelle Barnett

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.

Smartsheet formula error 062821.pdf

Paul Newcome

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")

Michelle Barnett

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

Michelle Barnett

It worked! thank you again for your help. 😀

Paul Newcome

Happy to help. 👍️