How to sort by latest date in SUMPRODUCT

[lost_in_the_sauce]

I have an array of sales data from multiple stores (180), and the data is entered by the stores at the end of each month. It all goes into an array. However, some stores will update their numbers, and in the array it adds a new line with a date/timestamp. For example, New York has 4 entries for February 2022, with different timestamps, and I only want the last one.

Currently I am using SUMPRODUCT like this:

=SUMPRODUCT((Data!$B$2:$B$1214=$B18)*(Data!$J$1:$P$1=C$2)*(Data!$F$2:$F$1214=$A18)*(Data!$J$2:$P$1214))

where $B18 is the month, C$2 is the $ category (sales, rent, etc), $A18 is the location, and $J:$2:$P$14 is the array of number values.

I have a column G with the timestamp. I've tried MAX but it looks like it pulls everything, I just want to add the condition for the latest date that also matches the other 3 conditions.

 

[awoohaw]

oh.. you want to pick the maximum date for each month when you're adding everything together?

I guess you need to explain what your sumproduct is selecting regarding Column G. Or is this an add to that formula? I don't see an caparisons with G in your SUMPRODUCT formula.

 

[awoohaw]

are the values in J1 to P1 fiscal month names/ids? I'm confused with the relations ship between B18 being February and the fiscal month being P01(Jan) - not necessarly because they are different months but in different rows in your matrix? Or is there a divider where one side is a data matrix column J-P and the other side A-I is some kind of time line? I'm really confused.

 

[awoohaw]

I think if you insert this array calc into the sumproduct formula it may work: I can't test it because I don't have the same data as you. But I think column and cell references are close (not sure about rows). i'm also not sure if my parens are correct. I cut from the above.

(--(MAX((--(B14=$B$2:$B$11))*($G$2:$G$11))=$G$2:$G$11))

 

[lost_in_the_sauce]

I thought the formula in POST 8 does that. What am I missing?

I typed it in wrong. This looks like it's working perfectly, even if I don't understand what's going on. Thank you!

 

[awoohaw]

I'm happy to help! Best wishes.

 

[lost_in_the_sauce]

I think if you insert this array calc into the sumproduct formula it may work: I can't test it because I don't have the same data as you. But I think column and cell references are close (not sure about rows). i'm also not sure if my parens are correct. I cut from the above.

(--(MAX((--(B14=$B$2:$B$11))*($G$2:$G$11))=$G$2:$G$11))

I didn't even see the second page of comments.

The formula from post 8 works but isn't differentiating by store, just the max timestamp across all entries.

>I guess you need to explain what your sumproduct is selecting regarding Column G. Or is this an add to that formula? I don't see an caparisons with G in your SUMPRODUCT formula.

Column G is the timestamp. The existing SUMPRODUCT doesn't have this because up until now we have been cleaning the data to remove all entries that weren't the most recent, I'm just trying to make it quicker going forward so that we can just paste the full dataset and not have to clean it up each time.

>are the values in J1 to P1 fiscal month names/ids? I'm confused with the relations ship between B18 being February and the fiscal month being P01(Jan) - not necessarly because they are different months but in different rows in your matrix? Or is there a divider where one side is a data matrix column J-P and the other side A-I is some kind of time line? I'm really confused.

J-P are the columns/is the array with all of the financial data, Revenue, Payroll, Rent, etc. I'm not great with SUMPRODUCT so it's what I found on the web that worked.

>I think if you insert this array calc into the sumproduct formula it may work: I can't test it because I don't have the same data as you. But I think column and cell references are close (not sure about rows). i'm also not sure if my parens are correct. I cut from the above.

I'll try this.

Also, here is an example of my output and data