Total Sum By Month selected on Dropdown

cramsey

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. 

Andrée Starå

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

Paul Newcome

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.

cramsey

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. 

 

Paul Newcome

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)

cramsey

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))))

Paul Newcome

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)

Andrée Starå

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

Have a fantastic weekend!

Best,

Andrée

cramsey

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. 

Paul Newcome

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?

cramsey

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

 

Kindly assist how to perform this. 

Paul Newcome

Try typing this in exactly as is...

 

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

cramsey

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. 

Paul Newcome

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.

cramsey

 

    It worked       Thank You Sir!!

 

 

Paul Newcome

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