# How to sum total sales for a store dependent on max date

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!

• 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. Commented Apr 22 at 16:32

EDIT

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. 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. 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. 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))```

```# One Store
=SUMPRODUCT(C2:C, B2:B="Store 1", A2:A=MAX(A2:A))
=750

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

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

# All Stores
=SUMPRODUCT(C2:C, A2:A=MAX(A2:A))```
• 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. 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)) ` Commented Apr 29 at 16:33