+ Reply to Thread
Results 1 to 6 of 6

SUM/IF/FREQUENCY does not count TEXT

  1. #1
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    480

    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?
    Attached Files Attached Files

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,053

    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&""))

  3. #3
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    480

    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).

  4. #4
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,796

    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
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  5. #5
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    480

    Re: SUM/IF/FREQUENCY does not count TEXT

    Quote Originally Posted by canapone View Post
    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?

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    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.
    Last edited by jason.b75; 06-06-2018 at 08:29 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need help with Count If Frequency
    By debralee in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-23-2018, 06:04 PM
  2. Please help Count if Frequency
    By debralee in forum Excel General
    Replies: 1
    Last Post: 05-23-2018, 04:24 PM
  3. count frequency
    By kimudao in forum Excel General
    Replies: 1
    Last Post: 10-06-2017, 04:05 AM
  4. [SOLVED] Count If Function To Count Frequency Of Long Numbers
    By RowanB in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-21-2013, 05:18 AM
  5. Using FREQUENCY to sum instead of count
    By Emma_Fairclough in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-04-2008, 01:33 PM
  6. Count Frequency?
    By melnikok in forum Excel General
    Replies: 2
    Last Post: 01-21-2008, 03:11 PM
  7. Frequency count
    By topgunnerp in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-30-2007, 03:06 PM

Tags for this Thread

Bookmarks

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