+ Reply to Thread
Results 1 to 21 of 21

SUM/IF/FREQUENCY with blanks and duplicates

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

    SUM/IF/FREQUENCY with blanks and duplicates

    Hi all,

    I am using a SUM/IF/FREQUENCY formula to count the number of bought items by store if certain conditions are met. Everything works fine until some of the items (style number/ CC) are removed in between. It doesn't seem to handle the blanks well for some reason, does anyone know why this messes it up and how to fix it?
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,176

    Re: SUM/IF/FREQUENCY with blanks and duplicates

    What is the formula meant to be counting? Give details of the criteria in words.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

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

    Re: SUM/IF/FREQUENCY with blanks and duplicates

    Count if cell contains the following:

    e.g.

    x
    xx
    xxx
    " x" (ignoring spaces)
    5
    8
    10 (all numbers greater than 0)

    but don't count above if row is "INACTIVE", "DROP" or column "CC" is blank and do not count duplicates in "CC" column twice if both have any of the above
    Last edited by esbencito; 03-27-2018 at 01:25 AM.

  4. #4
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    2016
    Posts
    3,834

    Re: SUM/IF/FREQUENCY with blanks and duplicates

    Better if your can manually represent some expected result.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

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

    Re: SUM/IF/FREQUENCY with blanks and duplicates

    That's exactly what I did in my sample file! Green coloured row is the expected result, red the current formula...
    Last edited by AliGW; 03-27-2018 at 01:44 AM. Reason: Unnecessary quotation removed.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,176

    Re: SUM/IF/FREQUENCY with blanks and duplicates

    This seems to get the results you want:

    =COUNTIFS(J$13:J$170,"<>",$E$13:$E$170,"<>INACTIVE",$D$13:$D$170,"<>DROP")

  7. #7
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    2016
    Posts
    3,834

    Re: SUM/IF/FREQUENCY with blanks and duplicates

    Ali H column CC don't have duplicate value, U column result wouldn't correct

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

    Re: SUM/IF/FREQUENCY with blanks and duplicates

    Quote Originally Posted by AliGW View Post
    This seems to get the results you want:

    =COUNTIFS(J$13:J$170,"<>",$E$13:$E$170,"<>INACTIVE",$D$13:$D$170,"<>DROP")
    Nope... you are neglecting the duplicates as well as the fact that some cells might contain other inputs, such as "-" or "NA", also if there's no item number but an "x" it will still count it. It needs to be a SUMIFFREQUENCY formula not COUNTIFS

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,176

    Re: SUM/IF/FREQUENCY with blanks and duplicates

    That's the only column that doesn't work. We just need to find a way round it. The problem with the OP's original formula is it's looking for unique sets of criteria, hence the low number. In other words, he's coming at it from the wrong angle.

    Tim - please feel free to correct my mistake if you can.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,176

    Re: SUM/IF/FREQUENCY with blanks and duplicates

    Quote Originally Posted by esbencito View Post
    Nope... you are neglecting the duplicates as well as the fact that some cells might contain other inputs, such as "-" or "NA", also if there's no item number but an "x" it will still count it. It needs to be a SUMIFFREQUENCY formula not COUNTIFS
    Well, my start is a lot closer to the result you need than yours, but you obviously know better, so I'll leave you to it. Good luck!

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

    Re: SUM/IF/FREQUENCY with blanks and duplicates

    Quote Originally Posted by AliGW View Post
    Well, my start is a lot closer to the result you need than yours, but you obviously know better, so I'll leave you to it. Good luck!
    close only in this sample file, in my actual data set, your formula would be off by about 20%!

  12. #12
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    2016
    Posts
    3,834

    Re: SUM/IF/FREQUENCY with blanks and duplicates

    First result should be 60, how you suppose it 63 ? I have manually checked it ??

  13. #13
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    2016
    Posts
    3,834

    Re: SUM/IF/FREQUENCY with blanks and duplicates

    According my manual data check the result is vary from, First result is 60, 0, 43 and so on.

    Try
    K8
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,176

    Re: SUM/IF/FREQUENCY with blanks and duplicates

    Quote Originally Posted by esbencito View Post
    close only in this sample file, in my actual data set, your formula would be off by about 20%!
    Then you need to provide more accurate sample data, don't you?

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

    Re: SUM/IF/FREQUENCY with blanks and duplicates

    Quote Originally Posted by shukla.ankur281190 View Post
    According my manual data check the result is vary from, First result is 60, 0, 43 and so on.

    Try
    K8
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Unfortunately that's not what I meant. I had just given you a few examples of the criteria it should look for. It should count ALL numbers above 0 not just 5, 8 and 10. Also it should count all "x" unless there's more than 3, so not "xxxx" and ignore spaces.

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

    Re: SUM/IF/FREQUENCY with blanks and duplicates

    Quote Originally Posted by AliGW View Post
    Then you need to provide more accurate sample data, don't you?
    Yup, my bad!

  17. #17
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,958

    Re: SUM/IF/FREQUENCY with blanks and duplicates

    J7
    Please Login or Register  to view this content.
    Try this array formula and copy towards right
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

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

    Re: SUM/IF/FREQUENCY with blanks and duplicates

    Is there no way to simply adjust my original formula? I feel like things a becoming unnecessarily complex and long.

    Please Login or Register  to view this content.
    The only problem with the above is below part:

    Please Login or Register  to view this content.
    It somehow does not neglect blanks and counts wrong. If blank cells are filled in the "Attribute_CC" column, suddenly results are displayed correctly...

  19. #19
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,958

    Re: SUM/IF/FREQUENCY with blanks and duplicates

    your formula can be modified like below

    =SUM(IF(FREQUENCY(IF((ISNUMBER(MATCH(SUBSTITUTE(J$13:J$170," ",""),REPT("x",{1,2,3}),0))+(ISNUMBER(J$13:J$170)*(J$13:J$170>0)))*(Attribute_Drop<>"DROP")*(Attribute_Status<>"INACTIVE")*(Attribute_CC<>""),MATCH(Attribute_CC,Attribute_CC,0)),ROW(Attribute_CC)-12),1))

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,176

    Re: SUM/IF/FREQUENCY with blanks and duplicates

    Quote Originally Posted by esbencito View Post
    Is there no way to simply adjust my original formula? I feel like things a becoming unnecessarily complex and long.
    The root of the problem is the overly complex way you have of recording your data. A nice, neat, normalised layout would make all manner of analysis easier, but it would mean starting from scratch, which you probably don't want to do, or maybe can't as it is beyond your control.

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

    Re: SUM/IF/FREQUENCY with blanks and duplicates

    Quote Originally Posted by AliGW View Post
    The root of the problem is the overly complex way you have of recording your data. A nice, neat, normalised layout would make all manner of analysis easier, but it would mean starting from scratch, which you probably don't want to do, or maybe can't as it is beyond your control.
    Totally agree, unfortunately as you have already guessed, this is beyond my control...

+ 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. Remove Duplicates While Leaving Blanks
    By gitpicker in forum Excel General
    Replies: 2
    Last Post: 08-30-2016, 12:31 PM
  2. [SOLVED] VBA that eliminates blanks and duplicates from a column
    By norseman1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-07-2016, 11:50 AM
  3. Replies: 13
    Last Post: 04-19-2015, 11:51 PM
  4. Compare 2 columns and sum frequency of duplicates
    By jntslvdrt in forum Excel General
    Replies: 8
    Last Post: 03-24-2015, 01:49 PM
  5. Frequency of occuring values excluding duplicates
    By Ollypetcon in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-23-2014, 01:03 PM
  6. Identifying duplicates and their frequency
    By helpmeplz55 in forum Excel General
    Replies: 3
    Last Post: 10-12-2011, 06:14 PM
  7. Counting Frequency of duplicates for each unique value and corresponding data
    By fredman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-04-2009, 12:55 AM

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