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.

Smartsheet Formula Question.xlsx

Any help will be much appreciated.

Thank you!

Sheila

Find more posts tagged with

Accepted answers

All comments

heyjay

=SUMIF([Plan DT]:[Plan DT],Shift:Shift,1) +
COUNTIFS(Shift:Shift,1,[Item No]:[Item No],"") * 480

Sheila B.

Thanks, heyjay. But when I use that formula, I get #INCORRECT ARGUEMENT SET.

Paul Newcome

Try this:

=SUMIFS([Plan DT]:[Plan DT], Shift:Shift, @cell = "1") + COUNTIFS([Item No]:[Item No], @cell = "", Shift:Shift, @cell = "1") * 480

Sheila B.

Hello Paul, Thank you for your suggestion. With it I get #UNPARSEABLE, but maybe that is my fault. What does the @cell do?

Paul Newcome

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?

Sheila B.

Ah! Yes I wasn't referencing the exact column names. However, the result is 0 and I was hoping to get 137,665.

Sheila B.

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.