SUM/IF/FREQUENCY does not count TEXT

Hi

an attempt

=SUM(IF(FREQUENCY(IF(C3:C250>0,IF(B3:B250<>"",MATCH(B3:B250&"",B3:B250&"",0),1)),ROW($3:$250)-2),1))

array entered

Regards

I took a different approach, which may not be suitable, but I'll post it anyway.

I'm assuming that your real data set is much larger than the sample, so this method, although not so elegant, may well process more efficiently.

Firstly, a change to the count formula in column E (or a new formula in a different column if preferred, see edit notes at end of post), to omit sku's with no stock from the count, which appears to be the purpose of the formua.

=IF(N(C3),COUNTIFS($B$3:$B$250,B3,$C$3:$C$250,">0"),0)

N(C3) is used to improve the efficiency of the formula by only calculating the countif on rows with a unit quantity greater than 0 in column C.

Then to get the total, use this formula, array entered.

=SUM(IFERROR(FREQUENCY(E3:E183,ROW(INDIRECT("1:"&MAX(E3:E183)+1))-1)/(ROW(INDIRECT("1:"&MAX(E3:E183)+1))-1),0))

This counts the frequency of {0,1,2,3,4} etc (indirect and max set the upper limit based on the actual counts in column E).

Dividing result by bin size gives the the count of the unique records, with the 0 bin returning a #DIV/0 error and the overspill bin returning #N/A.

With the errors trapped, the valid results are summed to give your total, using arrays much smaller than the original method.

edit:-

With the original count formula retained in column E, and the revised formula entered into a different column, this alternative could be used to further improve efficiency.

=IF(N(C3),IF(E3=1,1,COUNTIFS($B$3:$B$250,B3,$C$3:$C$250,">0")),0)

Given that the purpose of the recount is to distinguish any repeated sku's which might have stock in some rows and 0 in others, recounting sku's that only appear once is not necessary, the 0 value check has already taken care of that, so remaining counts of 1 will always be correct.

If this is used then the frequency array will need to be edited to look at the column containing this formula instead of looking at column E.