Calc, sum based on two conditions and dates

[MuhammadOsta]

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.

[JohnSUN]

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:

=SUMPRODUCT(Quantities;Customers=$B$2;Products=C7;Dates>=DATE($I$2;$H$2;1);Dates<EOMONTH(DATE($I$3;$H$3;1);0)+1)

[MuhammadOsta]

Thanks, worked great!

[MuhammadOsta]

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?

[JohnSUN]

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”

[MuhammadOsta]

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