Use Min function within Sumproduct (or something smarter)

[cpallatto]

Good afternoon.


I have a list of clients, with multiple users within each company. I have a column that tells me the number of days since any one user has purchased a product. I need to add 2 columns that will ultimately be used in a pivot table to:


1) Identify unique clients, not unique users. (I solved this using sumproduct)

2) Determine the MINIMUM number days since any user of that client purchased a product. That number will represent how long it's been since the company purchased any products.


Specifically, I hope to display how many unique clients I have and when is the last time there as been any activity from there company, regardless of which user purchased.


I am stumped and hopeful to learn the solution.


https://docs.google.com/spreadsheet/ccc?key=0AizfNmXXHvYrdHVaaDJTd0FlZVRyYXRhU2duaVUwU3c&hl=en_US


Thanks in advance.


Chris

 

[Luke M]

I'm a little confused still as to what column(s) is the criteria. But, here goes...


Using SUMPRODUCT, you can put the MIN function inside, something like:

=SUMPRODUCT(MIN((A2:A10=Criteria1)*(B2:B10=Criteria2)*(C2:C10)))


Or, using an IF array (use Ctrl+Shift+enter) formula:

=MIN(IF((A2:A10=Criteria1)*(B2:B10=Criteria2),C2:C10))

 

[daffy333]

I've read a bit about this and it looks like the SUMPRODUCT/MIN formula does not typically work. I was going to suggest the MIN/IF array formula as it worked for me.


{=MIN(IF(($D$9:$D$24=D9),$C$9:$C$24))}


Reference http://www.mrexcel.com/forum/showthread.php?t=13192

 

[cpallatto]

I took Daffy333's suggestion and it getting me closer. It returns the MIN in the whole array, but not the MIN of each unique client. I've updated the spreadsheet:


https://docs.google.com/spreadsheet/ccc?key=0AizfNmXXHvYrdHVaaDJTd0FlZVRyYXRhU2duaVUwU3c&hl=en_US


Luke, does this clarify the my problem?


Thanks for your help,guys.


Chris

 

[daffy333]

Oh, I thought that it did work -- it gave me the MIN for each unique Unit ID. But you have to press Ctrl-Shift-Enter after copy/pasting the formula in the formula bar, as suggested by Luke, or you will only get the MIN of the entire array, as you have found. Good luck!

 

[cpallatto]

Ah - works like a charm! Hot dog! I can't thank you enough. You have no idea. I am gushing.


Chris