Calc, sum based on two conditions and dates

Hi there!

I have a product sales report sheet with these details:

  • customer name in one cell B2, all sheet calculations include a condition to this cell.
  • product names in column C
  • column D has a formula to sum all product Quantities based on the name of in column C as follow: =SUMIFS(Quantities;Customers;$B$2;Products;C7) where the defined database ranges are columns in other sheet, I have another column with them contains Dates in the form of YYYYMMDD HH:MM AM/PM

What I’m looking for is:

I want to add in column F a formula same as in column C but to add a condition to limit the sum between two dates, year and month, assuming:
limit from month in H2 and year in I2 to month in H3 and year in I3

Note: if this can’t be achieved because of the Dates range form, I can remove the time and change it to YYYY.MM.DD but can’t remove the day. also, I can change the sum between two dates to include the day if that’s easier to achieve.

Thank you all, I appreciate all time and effort in solving this for me.

To solve such problems, the SUMPRODUCT() function or a pivot table is usually used. In the case of the SUMPRODUCT () function, your formula might look something like this:


Thanks, worked great!

Hi again John, I appreciate your help on this and my other question a while back… I wanna ask you how can I make the same formula but to account the day parameter as well? I managed to mix between your answer and something I found from other questions about the date, I tried this formula: =SUMIFS(Quantities;Customers;$B$2;Products;C8;Dates;">="&DATE($P$2;$O$2;$N$2);Dates;"<="&DATE($P$4;$O$4;$N$4)) where N is the cell containing the day… it works but only till the date not equal or less like its written in the formula >= example: if enter day 3 it only calculate till day 2 of that date, not 3 and before, I don’t know what I’m missing?

You are “hindered” by the time that is included in the date. The fact is that 2020-11-04 08:00 is slightly more than DATE (2020; 11; 4), more by 8 hours (this is the fractional part of the date-time value). Therefore, when comparing the lower end of the range, you can write >=DATE(...). But for the upper range, there is a trick - you have to compare with the midnight of the next day to take into account all values ​​of the last day up to 23:59:59. That is why in my version of the formula the comparison of the upper limit is strictly less and the value for the comparison is “last date +1”

oh I never took time parameter into consideration… I get it now Thank you!