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

Question

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!

Answer 1

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.

Answer 2

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

# 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))