+ Reply to Thread
Results 1 to 10 of 10

Countifs- not count duplicate

  1. #1
    Forum Contributor
    Join Date
    04-29-2011
    Location
    South Korea
    MS-Off Ver
    Excel 2019
    Posts
    136

    Countifs- not count duplicate

    Hi,

    I have spreadsheet to store information of boxes in the offsite storage company. Inside boxes, we file folders for different clients' building. So, one box number may have more than one building.


    I attach the spreadsheet. At column Cell N8, I want to use countif formula to count how many boxes we retrieve on April 11, 2016. The correct number should be 2. However, I can't fix the formula (it shows 5 as I use countif).

    If the formula successful, I do not need column: Number of box. Every time I need to manually input

    We have 10000 boxes, I make this sample excel for your reference.

    I also try to use formula, Frequency, however, not sucessful , so i do not put in excel

    =SUM(IF(FREQUENCY(IF($E$3:$E$9998<>"",IF($D$3:$D$9998="BUTLER BOX permanently removal",MATCH($E$3:E9998,E3:$E$9998,0))),ROW($E$3:$E$9998)-ROW($E$3)+1),1))

    Thanks very much
    Attached Files Attached Files

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

    Re: Countifs- not count duplicate

    Hi,

    in N7 to be copied below

    =SUMIFS(E:E,D:D,M7,B:B,L7)

    Hope it helps
    -----------------------------------------------------

    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.

  3. #3
    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
    46,869

    Re: Countifs- not count duplicate

    Try this:

    =SUM(--(FREQUENCY(IF($D$3:$D$12=M7,IF($B$3:$B$12=L7,MATCH($F$3:$F$12,$F$3:$F$12,0))),ROW(B1:B12)-ROW(B1)+1)>0))

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

  4. #4
    Forum Contributor
    Join Date
    04-29-2011
    Location
    South Korea
    MS-Off Ver
    Excel 2019
    Posts
    136

    Re: Countifs- not count duplicate

    Hi,

    Thanks. It works. I ask my last question:

    For this formula, can I set D3:D12 to D3:d9999
    B3:b12 set to B3:B9999?

    So even I have more rows, I do not need to change formula again.

    It seems not work... but if i use D3:D12 and B3:b12 , it works again.

    =SUM(--(FREQUENCY(IF($D$3:$D$12=M7,IF($B$3:$B$12=L7,MATCH($F$3:$F$12,$F$3:$F$12,0))),ROW(B1:B12)-ROW(B1)+1)>0))
    Last edited by ronlau123; 04-12-2017 at 02:24 AM.

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

    Re: Countifs- not count duplicate

    Hi again

    a made an example:

    if you'd need to count how many different types of boxes ( column F) under two conditions (M7 and L7)

    =SUM(IF(FREQUENCY(IF(E$3:E$9998<>"",IF(D3:$D$9998=M7,IF(B$3:B$9998=L7,MATCH(F$3:F$9998,F$3:F$9998,0)))),ROW($3:$9998)-ROW(A$3)+1),1))

    to be confrmed with control+shift+enter.

    Your formula is OK, but it's missing some $ reference


    =SUM(IF(FREQUENCY(IF($E$3:$E$9998<>"",IF($D$3:$D$9998="BUTLER BOX permanently removal",MATCH($E$3:$E$9998,$E$3:$E$9998,0))),ROW($E$3:$E$9998)-ROW($E$3)+1),1))


    Hope to have understood.

    edit: ciao AliGw, I had not read your answer...
    Last edited by canapone; 04-12-2017 at 02:29 AM.

  6. #6
    Forum Contributor
    Join Date
    04-29-2011
    Location
    South Korea
    MS-Off Ver
    Excel 2019
    Posts
    136

    Re: Countifs- not count duplicate

    Thanks very much:

    I like to use the formula provided by Aligw, because I do not need column E (number of box). Sorry confuse you. Before I post here, I can't find any method, so I just have a column E (number of box) to manually to count the box


    This formula is okay but not sure can change row 12 to row 9999 (so, as I have more rows, does not need to change formula) . Sometimes, I will forget.

    My modified formula:
    =SUM(--(FREQUENCY(IF($D$3:$D$9999=M7,IF($B$3:$B$9999=L7,MATCH($F$3:$F$9999,$F$3:$F$9999,0))),ROW(B1:B9999)-ROW(B1)+1)>0))

    Not sure I should put Row (B1:B9999) or (B1:B9998), it seems the answer are same.

  7. #7
    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
    46,869

    Re: Countifs- not count duplicate

    Let us know how you get on.

  8. #8
    Forum Contributor
    Join Date
    04-29-2011
    Location
    South Korea
    MS-Off Ver
    Excel 2019
    Posts
    136

    Re: Countifs- not count duplicate

    I use this one is work.

    =SUM(--(FREQUENCY(IF($D$3:$D$9999=M7,IF($B$3:$B$9999=L7,MATCH($F$3:$F$9999,$F$3:$F$9999,0))),ROW($B$1:$B$9999)-ROW($B1)+1)>0))

    By the way, the later part of formula I don't understand. What is purpose of ROW($B$1:$B$9999)-ROW($B1)+1)>0)?

    On N8, should I use =SUM(--(FREQUENCY(IF($D$3:$D$9999=M8,IF($B$3:$B$9999=L8,MATCH($F$3:$F$9999,$F$3:$F$9999,0))),ROW($B$1:$B$9999)-ROW($B1)+1)>0))

    or

    =SUM(--(FREQUENCY(IF($D$3:$D$9999=M8,IF($B$3:$B$9999=L8,MATCH($F$3:$F$9999,$F$3:$F$9999,0))),ROW($B$1:$B$9999)-ROW($B2)+1)>0))?

    It seems no difference, is it no big deal?


    Thanks. As I learn the principle of this formula, then problem should be sovled.
    Attached Files Attached Files
    Last edited by ronlau123; 04-12-2017 at 11:28 PM.

  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
    46,869

    Re: Countifs- not count duplicate

    You paste the formula I gave to you in N7 and drag copy it down. When you do you will see that the row counter increases by one each time and this is essential for the formula to work properly.

  10. #10
    Registered User
    Join Date
    11-27-2011
    Location
    paris,france
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Countifs- not count duplicate

    dear all,

    on the same idea I want to find the duplicates on the same rows, in detail I have two row (with numbers) and I want to count the similar values on the same row.
    the easy way will be to put in another column a IF condition to return 1 if the values are similar and to sum it but I don't want to use another column.
    Can you help me with a formula?
    A B
    1 1 identical
    2 1
    1 2
    3 3 identical

    answer will be 2

+ 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. [SOLVED] How to make the value of each it in COUNTIFS count as .125
    By Nathan0906 in forum Excel General
    Replies: 3
    Last Post: 04-06-2016, 12:43 PM
  2. [SOLVED] CountIFS without duplicate
    By AndyLiu in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-05-2015, 12:29 AM
  3. Multiple COUNTIFS ignoring duplicate values in a column
    By hamzahs in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-27-2015, 11:31 AM
  4. Replies: 0
    Last Post: 12-16-2011, 09:01 AM
  5. Issues with using COUNTIFS to highlight duplicate rows
    By Frank Bugeja in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-01-2011, 09:00 PM
  6. COUNTIFS - But only count once for each like item
    By Lyric in forum Excel General
    Replies: 1
    Last Post: 07-27-2011, 04:22 PM
  7. Need to duplicate COUNTIFS in Excel 2003
    By TimB in forum Excel General
    Replies: 2
    Last Post: 08-04-2010, 04:49 PM

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