1

enter image description here enter image description here

I would like to sum all the costs based on the products purchased between specific dates.

=SUM(ARRAYFORMULA(IFERROR(VLOOKUP('Purchased List Table'!B2:B,'Pricing Table'!A2:B,2,0),"")))

With the formula above, I managed to sum all the prices of all products in the Purchased List Table instead of a certain timeframe.

How could I modify the formula in order to sum only the costs of products within specific timeframe (e.g.: between 9/3/2021 to 9/5/2021 only)?

1
1

Use filter() or query() to choose which search keys to include, like this:

=arrayformula( 
  sum( 
    iferror( 
      vlookup(
        filter( 
          'Purchased List Table'!B2:B, 
          "2021-09-03" <= 'Purchased List Table'!A2:A, 
          'Purchased List Table'!A2:A <= "2021-09-05" 
        ), 
        'Pricing Table'!A2:B, 
        columns('Pricing Table'!A2:B), 
        false 
      ) 
    ) 
  ) 
)
3
  • It works! Appreciate your help, thanks!
    – Ryan
    Sep 20 at 5:46
  • Dates in Australia are formatted d-m-y compared to the US: m-d-y. Try as I might, I can't seem to adapt the formula for Australian dates. Possibly just a simple mistake on my part, but how would you suggest that the formula would appear if the spreadsheet locale was "Australia".
    – Tedinoz
    Nov 1 at 21:28
  • Date format has no effect on formulas that examine those dates. They see date serial values that are the same regardless of the format used. The ISO8601 format yyyy-mm-dd works the same everywhere. See this answer. Nov 1 at 21:59
0

Use SUMPRODUCT

Date range in the spreadsheet =sumproduct($B$2:$B$4,arrayformula(COUNTIFS($E$2:$E$9,A2:A4,$D$2:$D$9,">="&$H$2,$D$2:$D$9,"<="&$H$3)))

Date range in the formula

=sumproduct($B$2:$B$4,arrayformula(COUNTIFS($E$2:$E$9,A2:A4,$D$2:$D$9,">=3/9/2021",$D$2:$D$9,"<=5/9/2021")))


Sample

Screenshot

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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