# Countifs- not count duplicate

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

2. ## Re: Countifs- not count duplicate

Hi,

in N7 to be copied below

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

Hope it helps

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

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

5. ## Re: Countifs- not count duplicate

Hi again

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.

6. ## 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. ## Re: Countifs- not count duplicate

Let us know how you get on.

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

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

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

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