Need to know if a formula is possible in Smartsheet
I have a formula from Excel that I can't get to work in Smartsheet, so I need to know if it is possible or not.
=SUM($B$3:$B$26,"=1",$E$3:$E$26)+COUNTIFS($B$3:$B$26,"=1",$C3:$C26,"")*480
The first part, SUM, is looking to see if Column B is Shift 1, then sum Column E Plan DT. Second part, COUNTIFS, is again looking for Shift 1 in Column B but is now counting the blanks in Column C the Item No and then multiplying that by 480. Both results added together to get a total I need.
I can sort of get it to work in Smartsheet. The formula as is I get a number way too big, if I remove the *480 then my result is about half what it should be.
Here is the formula working in Excel.
Any help will be much appreciated.
Thank you!
Sheila
Answers
-
=SUMIF([Plan DT]:[Plan DT],Shift:Shift,1) + COUNTIFS(Shift:Shift,1,[Item No]:[Item No],"") * 480
...
-
Thanks, heyjay. But when I use that formula, I get #INCORRECT ARGUEMENT SET.
-
Try this:
=SUMIFS([Plan DT]:[Plan DT], Shift:Shift, @cell = "1") + COUNTIFS([Item No]:[Item No], @cell = "", Shift:Shift, @cell = "1") * 480
-
Hello Paul, Thank you for your suggestion. With it I get #UNPARSEABLE, but maybe that is my fault. What does the @cell do?
-
Make sure you are using the column names in your formula that match the column names in your sheet. Are you able to provide a screenshot of the formula open in the sheet as if you are about to edit it?
-
Ah! Yes I wasn't referencing the exact column names. However, the result is 0 and I was hoping to get 137,665.
-
I played around with the formula and got it to work, but I had to populate 0's in the Item No column. Then had it search for "0" and also took out the @cell. Thank you for all of your help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!