# SUM/IF/FREQUENCY does not count TEXT

1. ## SUM/IF/FREQUENCY does not count TEXT

Hi all,

I need to count the number of SKUs that have sales (>0) without counting them twice if there's duplicates. The formula works well if there are only numbers in my Style Number column, but it returns a #VALUE error if there's text inbetween. How can I make it work for both, text and numbers?  Register To Reply

2. ## Re: SUM/IF/FREQUENCY does not count TEXT

Try:
=SUMPRODUCT((\$B\$3:\$B\$183<>"")*(\$C\$3:\$C\$183<>0)/COUNTIF(\$B\$3:\$B\$183,\$B\$3:\$B\$183&""))  Register To Reply

3. ## Re: SUM/IF/FREQUENCY does not count TEXT

It needs to be SUM/IF/FREQUENCY, your SUMPRODUCT/COUNTIF slows my file down dramatically (been using such a formula before).  Register To Reply

4. ## Re: 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  Register To Reply

5. ## Re: SUM/IF/FREQUENCY does not count TEXT Originally Posted by canapone 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))
It seems to return the correct results!

Could you explain how that formula works? Not sure about the last part ``Please Login or Register  to view this content.``
Also, I was hoping there would be an "easier" fix by just adding another 'criteria' to ``Please Login or Register  to view this content.``
such as ``Please Login or Register  to view this content.``
to convert it all to text or numbers?? Would such an approach work as well?  Register To Reply

6. ## Re: SUM/IF/FREQUENCY does not count TEXT

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.  Register To Reply

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Tags for this Thread #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1