Meetloaf13
New Member
- Joined
- Mar 10, 2019
- Messages
- 3
So, I have an excel sheet that tracks events that are booked for a certain date (Range = eventdate), and then payments (Ranges = payment1, payment2, payment3, payment4) are logged at certain correlating dates (Ranges = date1, date2, date3, date4).
I have a formula that sums all deposits within a date range (on and in between dates in cells Y2 & Y3):
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;"> =SUMIFS(Payment1,date1,">="&Y2,date1,"<="&Y3)+SUMIFS(Payment2,date2,">="&Y2,date2,"<="&Y3)+SUMIFS(Payment3,date3,">="&Y2,date3,"<="&Y3)+SUMIFS(Payment4,date4,">="&Y2,date4,"<="&Y3)
</code>This formula is summing properly.
But when I add the third requirement, to sum not only payments made on or in between Y2 & Y3, but also payments made on the 'eventdate', I get $0.
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">=SUMIFS(Payment3,date3,">="&Y2,date3,"<="&Y3,date3,eventdate)
</code>I have verified that the dates do indeed equal.
Interestingly, when I remove the other dating syntax, I get the proper summation:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">=SUMIFS(Payment3,date3,eventdate)
</code>I would love it if someone could help me figure out where I've gone awry.
Thank you!
Update:
As I've tried to further troubleshoot the issue, I decided to break the sumifs formula down to it's cell references instead of the correlating ranges, and it returns the correct value: =SUMIFS(J9,I9,">="&Y2,I9,"<="&Y3,I9,C9)
I have a formula that sums all deposits within a date range (on and in between dates in cells Y2 & Y3):
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;"> =SUMIFS(Payment1,date1,">="&Y2,date1,"<="&Y3)+SUMIFS(Payment2,date2,">="&Y2,date2,"<="&Y3)+SUMIFS(Payment3,date3,">="&Y2,date3,"<="&Y3)+SUMIFS(Payment4,date4,">="&Y2,date4,"<="&Y3)
</code>This formula is summing properly.
But when I add the third requirement, to sum not only payments made on or in between Y2 & Y3, but also payments made on the 'eventdate', I get $0.
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">=SUMIFS(Payment3,date3,">="&Y2,date3,"<="&Y3,date3,eventdate)
</code>I have verified that the dates do indeed equal.
Interestingly, when I remove the other dating syntax, I get the proper summation:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">=SUMIFS(Payment3,date3,eventdate)
</code>I would love it if someone could help me figure out where I've gone awry.
Thank you!
Update:
As I've tried to further troubleshoot the issue, I decided to break the sumifs formula down to it's cell references instead of the correlating ranges, and it returns the correct value: =SUMIFS(J9,I9,">="&Y2,I9,"<="&Y3,I9,C9)