Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
SUMIFS Multiple Criteria
I'm beating myself up.
Im trying to calculate the sum of tasks, that if they have the status "Submitted" and the submitted date is in March then it would calculate the value.
Currently it is saying its #unparseable
=SUMIFS([Submitted ($)]:[Submitted ($)], [Status]:[Status], "Submitted", [Date Submitted]:[Date Submitted], ">=03/01/17", [Date Submitted]:[Date Submitted], "<=03/31/17",)
Any help would be greatly appreciated
Comments
-
Hi Nic,
I see a superfluous comma just before the closing parenthesis of the SUMIFS expression. Could that be the gremlin?
Cheers,
Rob.
-
Nic,
I think there are a couple of challenges in the formula. Mulitple criteria in SUMIFS is a bit of a challenge until you get the hange of it... Beyond syntax of getting all the commas and parens in the right place, you need to think about how you're referencing dates.
Keep in mind that text you wrap in quotes gets treated as a string which means that less-than and greater-than operations won't always do what you expect. It's best to write formulas that are explicit about dates.
See if the following formula gives you what you need:
=SUMIFS([Submitted ($)]:[Submitted ($)], Status:Status, "Submitted", [Date Submitted]:[Date Submitted], AND(MONTH(@cell) = 3, YEAR(@cell) = 2017))
Note also that I've used the @cell operator to save adding a duplicate data and criteria pair for the Date Submitted column - the help article for for @cell can be found here:
https://help.smartsheet.com/articles/775363-using-formulas#at_cell
-
Hello! I think I am having a similar issue and tried to mimic your formula above but no luck. Essentially I am trying to sum a dollar value in a column using 2 criteria from two other columns. These are also all columns from a separate sheet (all on the same sheet) so not sure if that is complicating things?
Here is my current formula-
=sumifs({Gains Worksheet Range 3}, {Gains Worksheet Range 1}, "Accounting", AND {Gains Worksheet Range 4}, "No"
Thanks!
-
=SUMIFS({Gains Worksheet Range 3},
{Gains Worksheet Range 1}, "Accounting",
{Gains Worksheet Range 4}, "No")
a. {Gains Worksheet Range 3} - is the column to be summed.
b. you don't need the "AND"
c. Smartsheet will add the closing parenthesis. If it doesn't, there is something wrong.
I hope this helps.
Craig
-
Hello,
I put the first step in smartsheet and try to use the sumifs formula that sum the sales volume by product code weekly from the other sheet that is quite simple in excel but is not working in smartsheet.
In one sheet I have:
Column A - Date
Column B - Product Code
Column C - Volume
In second sheet I try to sum the weekly volume per product:
Column A - the products codes
Column B - week 1 (01/01/18 - 08/01/18)
Column C - week 2 (08/01/18 - 15/01/18)
I will be grateful for you help.
Agnes
-
Agnes,
I assume you are using either ISO 8601 or an EU calendar to have you weeks starting on Monday.
There are only 7 days in a week so
week 1: 1st to 7th
week 2: 8th to 14th.
I would add a row at the top for the week number (1-53) - though you only need 52 this year.
If your formula were in a single sheet, then it would look something like this:
=SUMIFS(Volume:Volume, Date:Date, WEEKNUMBER(@cell) = [ColumnB]$1, [Product Code]:[Product Code], [Product Code]23)
for row 23 and each of the week number columns
Replacing them with X-Sheet references will look something like this
=SUMIFS({Volume}, {Date}, WEEKNUMBER(@cell) = [ColumnB]$1, {Product Code}, [Product Code]23)
The formula can be copied to all week columns and their rows (below row 1) after you have set up the X-Sheet references for the first one.
Craig
-
Hi,
Thank you your help.
In the meantime I tried to solve it and I have converted dates on weeks in separate column using WEEKNUMBER in first sheet and in second one used this formula
=SUMIFS({Volume}, {Code}, $Code1, {Week}, "1")
Seems it's working correctly.
Agnes
-
I am trying to use this logic in a referenced sheet and I'm coming up with the wrong value. I'm asking the sheet to sum "Qty on PO" only if the Supplier matches the Parent, in this case Supplier 1, and the month and year correspond to the column I'm currently working in.
The formula is returning "1", but, I think it should be returning "1000".
=SUMIFS({Qty on PO}, {Supplier}, =PARENT(), {Month}, MONTH(@cell) = 1, {Year}, YEAR (@cell = 2019)
Any ideas?
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives