How to get total QTY from two columns, and get QTY based on location and date range

Question

So I have been trying to get an answer to this situation and I have tried a slew of functions from countifs, sumifs and its not working as I had hoped.

What I am trying to do: I have two tabs in excel one for a metrics deck, the second holding raw data. on the deck I have Week date ranges (week 19, week 20 ETC, with dates like 4/08/2024), I need the QTY totals of columns B-C*-1 (In sheet 2) to show within specific date & location, what the QTY stock is.

Answer 1

Try using SUM() or SUMPRODUCT() functions:

---

=SUM(($D$2:$D$31>=F$1)*($D$2:$D$31<=F$1+6)*($A$2:$A$31="TX")*$B$2:$C$31)

---

Or, Using BYCOL()

=BYCOL(F1:M1,LAMBDA(α, SUM((D2:D31>=α)*(D2:D31<=α+6)*(A2:A31="TX")*B2:C31)))

---