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
Check out the Formula Handbook template!