Forum Discussion
Help with SUMIFS formula
Looking for help to create a formula for Cell I2. I think this will be a SUMIFS formula but not sure the details of the formula, or if there is an easier formula to use. I need to calculate the total quanity for Item 123455 that follows three TRUE arguments:
1) Is only an "Incremental" Request Type (Column C)
2) Is "Y" from Approved (Column F)
3) The Request ETA can be any date prior to Launch Date, but cannot be anything later than 3 months (90days) after launch date
** Note how line 6 is not included in calulation example bc the request ETA (7/4/2025) is more than 3 months past launch date (2/13/2025)
Please Note: all three arguments must be true when calculating the total qty in Cell I2.
3 Replies
Is 90 days good enough? then
=SUMPRODUCT( (E2:E10)*(A2:A10="123455")*(C2:C10= "Incremental")*(F2:F10="Y")*(D2:D10<=B2:B10+90))
the problem with SUMIFS and also the problem with using EDATE(..., 3) is if Launch Date is not a fixed/single value. If it is a fixed value then:
=SUMPRODUCT( (E2:E10)*(A2:A10="123455")*(C2:C10= "Incremental")*(F2:F10="Y")*(D2:D10<=EDATE($B$2,3)))
or
=SUMIFS( E2:E10,A2:A10,"123455",C2:C10, "Incremental",F2:F10,"Y",D2:D10, "<="&EDATE($B$2,3))
or you could get 'fancy' and use LAMBDA to overcome the problem:
=SUMPRODUCT( (E2:E10)*(A2:A10="123455")*(C2:C10= "Incremental")*(F2:F10="Y")*(D2:D10<=MAP(B2:B10,LAMBDA(q,EDATE(q,3)))))
Formula: =SUMIFS(E:E, A:A, H2, C:C, "Incremental", F:F, "Y", D:D, ">="&B2-90, D:D, "<="&B2+90)
Or use this sumproduct like this:
=SUMPRODUCT((A2:A100=$H$2)*(C2:C100="Incremental")*(F2:F100="Y")*(D2:D100<=B2:B100+90)*( (D2:D100<B2:B100)+(D2:D100>B2:B100) )*(E2:E100))
to be more flexible, and ensure the date in column D is within 90days before or after in column B by row.
