0

I have a data range from A1:Z300, the row (A1:Z1) contains dates of a typical month, and the column (A1:A300) contains text (ingredients). I want a formula to sum the data present in the corresponding row of a particular reference (ingredient) in column A (say "Salt") after filtering a given date range, say ">=Today()+1" and "<=today()+7" in row 1.The formula is used to compare data. My data range typically looks: enter image description here

1 Answer 1

0

Formula

=SUM(
   FILTER(
     FILTER(B1:Z, A1:A=AA2),
     B1:Z1>=IF(AB2="Any", B1:Z1, AB2),
     B1:Z1<=IF(AC2="Any", B1:Z1, AC2)))

Sample Data

  • Formula is in AD2
  • lookup text is in AA2
  • from and to dates are in AB2:AC2 (will also accept "Any").
AA AB AC AD
1 INGREDIENT FROM TO FORMULA
2 Sabut Fennel 12 Jan 24 Any 0.15175

Explantion

  1. Filters the range for those rows where the INGREDIENT is in Column A and returns an array
  2. Then filters that array for those columns where the date is on or after FROM and on or before TO.
  3. Sums the result
2
  • 1
    I used the formula, and it works perfectly. Thank you so much for your help and guidance. The double-filter function was the solution. Commented Jan 27 at 1:40
  • @SeshadevPanda Glad it worked and welcome to Web Applications! Take the Tour and hope to see you around again.
    – Blindspots
    Commented Jan 27 at 1:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.