How to get the sum of a column based on two helper columns

sean.hagan1088

I have a sheet with a column of numbers that I want to add based on a date range of two columns in a sheet summary.

This is what I have so far but it keeps coming back #UNPARSEABLE:

=SUMIFS([Helper RPF]:[Helper RPF], =2, [Mississippi County 50 Slots - IAR]:[Mississippi County 50 Slots - IAR]) OR([Helper RPT]:[Helper RPT], =2, [Mississippi County 50 Slots - IAR]:[Mississippi County 50 Slots - IAR])

Basically what I want to happen is a sum of the cells in the column named Mississippi County 50 Slots - IAR in the sheet summary based on the date range for each month in Rent Period-From and Rent Period- To. To make things easier I added helper columns to give the number value for each date

DKazatsky2

Hi @sean.hagan1088 ,

The reason you are getting a higher number is because the way the formular is written, the rows where both dates are in March are being counted twice. Try this.

=SUMIFS([Mississippi County 50 Slots - IAR]:[Mississippi County 50 Slots - IAR], [Helper RPF]:[Helper RPF], =3, [Helper RPT]:[Helper RPT], <>3) + SUMIFS([Mississippi County 50 Slots - IAR]:[Mississippi County 50 Slots - IAR], [Helper RPF]:[Helper RPF], =3, [Helper RPT]:[Helper RPT], =3) + SUMIFS([Mississippi County 50 Slots - IAR]:[Mississippi County 50 Slots - IAR], [Helper RPF]:[Helper RPF], <>3, [Helper RPT]:[Helper RPT], =3)

I would expect it to be counting wrong for every month where there are rows that span months.

Hope this helps,

Dave

sean.hagan1088

I tried running this and got a sum from the column but it isn't the right amount :

=SUMIFS([Mississippi County 50 Slots - IAR]:[Mississippi County 50 Slots - IAR], [Helper RPF]:[Helper RPF], =2, [Helper RPT]:[Helper RPT], =2)

Kelly Moore

Hey @sean.hagan1088

The syntax of your SUMIFS is correct. When you say the sum isn't the correct amount, what would make this the right amount? Are you looking for either of the Rent Periods to be in February, or, as you have written above, both of the the Periods to be in February?

If you are looking for either to be in February, you have to evaluate two SUMIFS.

=SUMIFS([Mississippi County 50 Slots - IAR]:[Mississippi County 50 Slots - IAR],[Helper RPF]:[Helper RPF], =2)+SUMIFS([Mississippi County 50 Slots - IAR]:[Mississippi County 50 Slots - IAR],[Helper RPT]:[Helper RPT], =2)

Is this what you are looking for?

Kelly

sean.hagan1088

Hey @Kelly Moore ,

Let me try it and see if it works, basically what its not adding is the ones where your start date is lets say 01/28/07- and the end date is 2/01/23. Or if the start date was lets say 02/28/23 and the end date was 3/01/23. In either scenario I'm needing that row with a February date added and currently is not.

sean.hagan1088

@Kelly Moore I added yours to the Sheet Summary and it its coming back Unparseable.

sean.hagan1088

@Kelly Moore Got it to work with your syntax BUT if you look at the March total it doesn't add up. The total for March should be 30,600 for the Mississippi County 50 Slots- IAR column. But you can see its adding in extra numbers somewhere and getting a total of 48705. Any ideas?

DKazatsky2

Hi @sean.hagan1088 ,

The reason you are getting a higher number is because the way the formular is written, the rows where both dates are in March are being counted twice. Try this.

=SUMIFS([Mississippi County 50 Slots - IAR]:[Mississippi County 50 Slots - IAR], [Helper RPF]:[Helper RPF], =3, [Helper RPT]:[Helper RPT], <>3) + SUMIFS([Mississippi County 50 Slots - IAR]:[Mississippi County 50 Slots - IAR], [Helper RPF]:[Helper RPF], =3, [Helper RPT]:[Helper RPT], =3) + SUMIFS([Mississippi County 50 Slots - IAR]:[Mississippi County 50 Slots - IAR], [Helper RPF]:[Helper RPF], <>3, [Helper RPT]:[Helper RPT], =3)

I would expect it to be counting wrong for every month where there are rows that span months.

Hope this helps,

Dave

Kelly Moore

sean.hagan1088

@DKazatsky2 @Kelly Moore That looks like its working now, thank you both!!