# 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:

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

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!