# Total Sum By Month selected on Dropdown

Options
edited 12/09/19

I need to be able to select to view the total cost of children by a certain month.

Currently, I have the dropdown values of all 12 months. I have the formula to the right of the dropdown calculating the total cost recorded for each child. But based on a different month, I want the value to change based on the date recorded for each child.

Cost entered under Parent as Children recorded by different dates.

Tags:

• ✭✭✭✭✭✭
Options

Hi,

Can you describe your process in more detail and maybe share the sheet(s) or some more screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

There are a few ways to do this. I would suggest adding a helper column that will convert January to 1, February to 2, March to 3, so on and so forth.

You could then use a formula such as the one below.

=SUMIFS(CHILDREN([Cost Column]@row), CHILDREN([Date Column]@row), IFERROR(MONTH(@cell), 0) = [Helper Column]@row)

This will look at the helper column which has the numeric value for the month selected in your dropdown and sum up the children in your cost column whose month is the same as what is in the helper column. The IFERROR accounts for blank rows in the date column as blanks would give you an error.

• Options

Thanks Guys,

Almost there. What would be the Helper Formula?

So far, I came up with =VALUE([Column21]6 ("January", 1))    >>>>Which doesn't work

So, what formula will work in the Help Column with all 12 months?

=VALUE([Column21]6 ("January", 1))("February", 2))("March", 3)) >>>>>>Dummy formula but trying provide insight in my thoughts.

• ✭✭✭✭✭✭
Options

There are two options.

1. A nested IF's statement along the lines of...

=IF([Column21]@row = "January", 1, IF([Column21]@row = "February", 2, IF([Column21]@row = "March", 3, IF([Column21]@row = "April", 4, .........................))))))))))))

2. Two additional columns to build out a chart.

Month          Value

January          1

February        2

March            3

April              4

.............

Then the formula of =INDEX(Value:Value, MATCH([Column21]@row, Month:Month, 0))

.

Both will work.

You could also just build the table and forget about pulling the Value into a helper and just drop that directly into your SUMIFS (the same could be done with the nested IF's statement).

=SUMIFS(CHILDREN([Cost Column]@row), CHILDREN([Date Column]@row), IFERROR(MONTH(@cell), 0) = insert nested IF's or INDEX/MATCH formula here)

• edited 03/29/19
Options

Not yet

Monthly Invoice (Formula) *Still not working

=SUMIFS(CHILDREN([Total Cost]12,CHILDREN(Date12, IFERROR(Month11), 0) =Helper11

Helper Formula *works

=IF(Month@row = "January", 1, IF(Month@row = "February", 2, IF(Month@row = "March", 3, IF(Month@row = "April", 4))))

• ✭✭✭✭✭✭
Options

Ok. I am a little confused by your layout. Maybe it's because I can't see the actual row numbers or the primary column that would show me what is indented as a child row. I also see some inconsistencies in the row numbers of your formula, and the syntax is a little off.

Here's what you have typed above...

=SUMIFS(CHILDREN([Total Cost]12,CHILDREN(Date12, IFERROR(Month11), 0) =Helper11

Here's what you show in your screenshot...

=SUMIFS(CHILDREN([Total Cost]12,CHILDREN(Date12, IFERROR(Month110, 0) =Helper11

.

First you need to make sure that you are closing out your CHILDREN functions...

CHILDREN([Total Cost]12)

CHILDREN(Date12)

.

Next, make sure your row references are correct. If the parent row is row 11, then that's what should be referenced in your CHILDREN function.

.

In your IFERROR statement you are referencing a column titled Month. The formula should be using the MONTH function instead. MONTH(date to pull month from)

.

Because we are looking at a range with the MONTH function being the criteria, we need to specify that we only want to look at months that are equal to a certain value.Thus we use the @cell reference.

MONTH(@cell)

.

Returning to row references...

If the formula is on the actual parent row, you can use the @row reference to avoid accidentally typing in a wrong number.

.

Try using this formula exactly as is. The ONLY changes that should be made are row references and that is only if the formula is not on the parent row itself.

=SUMIFS(CHILDREN([Total Cost]@row),CHILDREN(Date@row), IFERROR(MONTH(@cell), 0), Helper@row)

.

If the formula is in the parent row in the [Total Cost] column, you can even do away with that cell reference to get

=SUMIFS(CHILDREN(),CHILDREN(Date@row), IFERROR(MONTH(@cell), 0), Helper@row)

• ✭✭✭✭✭✭
Options

Happy to help! (but this time I did nothing)

Have a fantastic weekend!

Best,

Andrée

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Options

Hi Paul,

My apologies as I believe that I did not explain this correctly.

The formula you presented : =SUMIFS(CHILDREN([Total Cost]@row),CHILDREN(Date@row), IFERROR(MONTH(@cell), 0), Helper@row)

This did not work and I believe I understand why.

Per the screen shot attached- The goal is when a user selects a new "month" in the drop down column, the monthly invoice needs to sum (equal) the total referenced for the amounts "Total Cost" by the dates "date" that match.

Example, May should show total for items that are listed for that month 05/08/19

Your =SUMIFS brought back and error so i adjusted SUMIFS to SUMIF and all it did was add the helper number the Monthly Invoice.

I hope this clears up any misunderstanding.

• ✭✭✭✭✭✭
Options

SUMIF and SUMIFS actually have different syntax, so it would have to be written differently other than just removing the S.

To use your screenshot... In the row that you have the arrows in, if a user were to select February, it should actually return a 0, but January should return \$26.00? March and April would both also return a 0, but May would return \$13.00?

• Options

Yes. That is correct. That is exactly what I'm seeking.

Kindly assist how to perform this.

• ✭✭✭✭✭✭
Options

Try typing this in exactly as is...

=SUMIFS(CHILDREN(),CHILDREN(Date@row), IFERROR(MONTH(@cell), 0), @cellHelper@row)

• Options

Via screenshot, I've received an error again. I've also displayed both formulas. I've noticed in your most recent formula it did not account for the "total cost" row.

I feel like we are almost there. I took a few days to investigate before replying to you again.

• ✭✭✭✭✭✭
Options

My apologies. I gave you a bad formula. Somehow I managed to jack up the syntax. Here is the corrected one.

=SUMIFS(CHILDREN(),CHILDREN(Date@row), IFERROR(MONTH(@cell), 0) = Helper@row)

.

As far as not referencing the Total Cost column, I assumed that the formula would be going in the parent row of that column. If the formula is in that column, that it is assumed to be CHILDREN([Total Cost]@row). If it is NOT in that column, then you will need to specify.

• edited 04/11/19
Options

## It worked      Thank You Sir!!

• ✭✭✭✭✭✭
Options

Excellent! Sorry for the mix up, but glad it's working for you now.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!