We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

Calc, sum based on two conditions and dates [closed]

asked 2020-11-04 09:57:46 +0200

MuhammadOsta gravatar image

updated 2021-05-25 16:37:10 +0200

Alex Kemp gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by MuhammadOsta
close date 2020-11-16 07:59:03.056058

1 Answer

Sort by » oldest newest most voted
1

answered 2020-11-04 12:41:05 +0200

JohnSUN gravatar image

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)
edit flag offensive delete link more

Comments

Thanks, worked great!

MuhammadOsta gravatar imageMuhammadOsta ( 2020-11-04 13:39:54 +0200 )edit

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?

MuhammadOsta gravatar imageMuhammadOsta ( 2020-11-04 15:47:27 +0200 )edit

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"

JohnSUN gravatar imageJohnSUN ( 2020-11-04 16:30:31 +0200 )edit

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

MuhammadOsta gravatar imageMuhammadOsta ( 2020-11-05 08:28:50 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2020-11-04 09:57:46 +0200

Seen: 240 times

Last updated: Nov 04 '20