Determine the right formula

[canthony24]

I have a very large data set, over 600k rows, that will eventually be a pivot table, but I want to summarize the data. Creating a pivot table and trying to summarize using that isn't working. I need to figure out whats the count of customers that receive 4 or more cases in a single delivery.

The data set is rather small as far as columns. Columns include Customer Name, Invoice Date, Invoice Number, Year and Cases Sold. The dates are random and the invoices are random.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(Customer=""),IF(CaseAmount>=4,MATCH(Customer,Customer,0))),ROW(Customer)-ROW(INDEX(Customer,1,1))+1),1))

Hope this helps.

 

[canthony24]

I'm testing it now. Is there a way to run the formula down the end of the last column to call out which customers they are?

 

[Aladin Akyurek]

I'm testing it now. Is there a way to run the formula down the end of the last column to call out which customers they are?

The formula I posted does a count. Are you wanting to list the customers with CaseAmount >= 4?

 

[canthony24]

Precisely. That way I can find the average case amount per invoice.

 

[Aladin Akyurek]

Precisely. That way I can find the average case amount per invoice.

Let's A1 in a sheet, which is different from the one with the source data, house the first formula, i.e.

=SUM(IF(FREQUENCY(IF(1-(Customer=""),IF(CaseAmount>=4,MATCH(Customer,Customer,0))),ROW(Customer)-ROW(INDEX(Customer,1,1))+1),1))

In A3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$3:A3)>$A$1,"",INDEX(Customer,SMALL(IF(FREQUENCY(IF(1-(Customer=""),IF(CaseAmount>=4,MATCH(Customer,Customer,0))),ROW(Customer)-ROW(INDEX(Customer,1,1))+1),ROW(Customer)-ROW(INDEX(Customer,1,1))+1),ROWS($A$3:A3))))