enter image description hereenter image description here

I'm trying to sum the sales for different stores with the criteria to use the maxdate in a column, in google sheets. Below is the data I have:

Column A: Dates (Weekly) Column B: All store names Column C: Sales

Lookup values: The specific stores to look up in Range (Column B)

I tried to do the following to get the sales for Store 1 dependent on maxdate in Column A:

= SUMIF(ColumnB,Store1,ColumnC)*(MAXIFS(ColumnA,ColumnB,Store1)=ColumnA)

Currently I don't get the correct answer.

Please let me know if you know other ways of doing this, thank you in advance!

enter image description here

  • Please provide an example of your input data and the desired output, preferably using markdown tables. It's very challenging to answer a question without this. Commented Apr 22 at 16:00
  • Hi, can you see the attached image? That would be the input data and the output I'm looking for in this case would be the total sales of Store 1 dependent on the max date which in this case is '2024-04-22'. Please let me know if you have any questions, thank you.
    – Crose
    Commented Apr 22 at 16:32

2 Answers 2



You can try something like:

=sum(filter(M2:M,D2:D="Store 1",A2:A=max(filter(A2:A,D2:D="Store 1"))))

The inner filter returns every row in A2:A (dates) where D2:D (stores) contains 'Store 1'), we then take the max of this and use it in the outer filter to return every row in M2:M (sales) where A2:A matches that maximum date (and D2:D still contains 'Store 1'), then finally we take the sum of those values.

  • Thank you for your reply, not sure if there is any missing in the formula but it returns a higher Sales number than expected. So it does not return "750" which is the desired output if looking at the example input data.
    – Crose
    Commented Apr 23 at 8:17
  • I'm getting the desired result of 750 at my end from the formula provided based on the example table given residing in A:C of a sheet, and the formula provided being entered alongside. Can you provide some more context - what output are you seeing? Commented Apr 23 at 8:23
  • I will give you some more context, please see attached image for input data. The important columns here will be ColA(Date), ColD (Store) and lastly ColM (Sales) but I need to keep the other columns as is since I pull this data every week and just copy paste it in the sheet. ///// Can you please check what result you get for Store 1? The correct answer should be 661 (622+39) in Sales for Store 1 for the date 2023-04-10 which is the max date in this case. However I get 1857 as you can see attached.
    – Crose
    Commented Apr 23 at 9:01
  • Or if you could you see if I'm missing anything in my formula, since you don't have access to the input data and can try it yourself? Thank you very much.
    – Crose
    Commented Apr 23 at 9:19
  • I've updated my answer to match your real data set - this should provide the desired answer now. Commented Apr 23 at 9:59

To get the sum of store sales based on the maximum date for all stores, the array formula SUMPRODUCT is a good option.

For example,

# One Store 
=SUMPRODUCT(sales, stores=store, dates=MAX(dates))

# Two Stores 
=SUMPRODUCT(sales, stores=store_1 + stores=store_2, dates=MAX(dates)) 

# All Stores
=SUMPRODUCT(sales, dates=MAX(dates))

Using Your Sample Data

OP's example image
# One Store 
=SUMPRODUCT(C2:C, B2:B="Store 1", A2:A=MAX(A2:A)) 

=SUMPRODUCT(C2:C, B2:B="Store 2", A2:A=MAX(A2:A)) 

# Two Stores 
=SUMPRODUCT(C2:C, B2:B="Store 1" + B2:B="Store 2", A2:A=MAX(A2:A))

# All Stores
  • I'm starting to think you want to return 0 if the store's maxdate is less than the maxdate from all stores. You've never edited your question to include a visual of expected results based on sample data, and your explanations are imprecise.
    – Blindspots
    Commented Apr 29 at 15:57
  • I have changed my answer to match what I now think you want. Please confirm. =SUMPRODUCT(C2:C, B2:B="Store 1", A2:A=MAX(A2:A))
    – Blindspots
    Commented Apr 29 at 16:33

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.