How to count just numbers in the second column which are not the same

Snímka.PNG

Hello there,
I used this function:
=COUNTIFS(A1:A5;A1;B1:B5;"<>")

I have table with 2 columns (A,B) and 5 rows. At the first column are the same dates and at the second column are different numbers but some of them are the same. According to the function result is 5 (sum of all dates). How to count just numbers in the second column which are not the same? Thank you!

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new, I have done it for you this time.)

Hello, Ive done it, thank you

I think I need to use function COUNTIFS because according to my sheet, at first I need to pick the same dates and then I need to pick the numbers which are not the same in the second column. Simply I need the result as 1 number which says how many times are numbers in particular date without repeating numbers.

H4=SUM(IF(FREQUENCY(IF($B$3:$B$50=G2,MATCH(B3:B50&C3:C50,B3:B50&C3:C50,0)),ROW(B3:B50)-ROW(B3)+1),1))


Control+shift+enter


There are duplicates in the first attached file

in the second no

Thank you very much for your effort but it's not working. To be honest I really dont understand to your function I just put it into cell and then I can see a message with error with this function.
Maybe I make some mistakes with copying and pasting but I think there is a problem with match function. But Im not sure. I would show you this error message but it is in Slovak
Maybe it would help if you send me excel document with completed result and I could download it. Thank you very much

It is NOT that simple.


Firstly, your date column is a mixture of real dates and text fragments that look a bit like a date. NOW, they are all dates. This formula returns the count you need.

=SUM(INDEX(($B$3:$B$50=G2)/COUNTIFS($C$3:$C$50,$C$3:$C$50&"",$B$3:$B$50,$B$3:$B$50&""),0))

I have filtered the data. You can see the duplicates (in pink). My formula is returning a count of the unique numbers, whereas yours is returning a count of them all.

this is a regular formula (just use Enter). In beautiful Slovakia you might need to use ; instead of ,

Oh yes sorry the data have to be filtered, thank you for all but Glenn your answer helped me so much. I think I can apply it in my tables, thank you again
I added reputation to CARACALLA as well

Now you tell us that they meed to be filtered!!

So use this array formula:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET($B$3,ROW($C$3:$C$50)-MIN(ROW($C$3:$C$50)),)),IF($B$3:$B$50=G2,MATCH($C$3:$C$50,$C$3:$C$50,0))),ROW($C$3:$C$50)-MIN(ROW($C$3:$C$50))+1),1))

The file shows it working on a filtered list.


Array formuale are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Provide an updated sample workbook demonstrating the issue.

Well you do have to ensure that the ranges are correct. So, IF the data begins on row 5 and runs to 100... and with the formula in an Excel cell

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET($B$3,ROW($C$3:$C$50)-MIN(ROW($C$3:$C$50)),)),IF($B$5:$B$100=G2,MATCH($C$3:$C$50,$C$3:$C$50,0))),ROW($C$3:$C$50)-MIN(ROW($C$3:$C$50))+1),1))

FIRST change the bits in red. Enter.

Then change ONE of the column C ranges

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET($B$5,ROW($C$5:$C$100)-MIN(ROW($C$3:$C$50)),)),IF($B$5:$B$100=G2,MATCH($C$3:$C$50,$C$3:$C$50,0))),ROW($C$3:$C$50)-MIN(ROW($C$3:$C$50))+1),1))

Enter.

Then double click on that part of the formula which you just changed, Excel will select $C$5:$C$100.

CTRL-C.

double click on the next $C$3:$C$50 bit of the formula. CTRL-V.

Repeat as needed.

CTRL-SHIFT-ENTER.

Done.

Thank you so much! I believe you it's the quickest way. I think it's definetely solved.

There are 3 UNIQUE numbers in the filtered list. Do you want a count of those that occur only ONCE on a FILTERED list?

If that is what you want, use this:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET($B$3,ROW($C$3:$C$50)-ROW($C$3),)),IF($B$3:$B$50=G2,MATCH($C$3:$C$50,$C$3:$C$50,0))),ROW($C$3:$C$50)-ROW($C$3)+1)=1,1))


Adjust the ranges to whatever you need, and don't forget it's an array formula.

I think we are not undertanding each other here. You have not explained the criteria.

For EACH of these scenarios, what is the expected answer (filtered list) and WHY:

5021
5021
5021
5021


5021
5021
5021
5044

5021
5021
5044
5044


5021
5022
5023
5023

5021
5022
5023
5024

I do not know what you want to count... values that occur once, total unique count, values that occur twice.... we are playing giuessing games. I do not play guessing games. So please define what you want CLEARLY.

My file, which you reattached at post 25 works perfectly. You have maybe forgotten that you enter the date in G2. That's they way it has worked RIGHT from the start. Your first post was referring to G2, so I continued doing the same.

Okay friend Glenn, maybe Im stupid or I dont know but Ive downloaded your file again now. When I filter the dates 29.12.2020 there is result in cell K2 = 0 and should be 2. Right? Guys I really appreciate your patience with me and your effort!!!

Capture.PNG

What did I say about G2 in my last post. Enter the date in G2.... If you can remember back to the start, everything hinged on what was in G2.... That was YOUR requirement at the time!!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

That's the problem with lengthy threads... sometimes the original objectives get lost and misunderstood!