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

    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 2019
    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

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

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

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    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
    176

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    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