CountIfs returns an unexpected number

  • Hello everyone,


    I'm trying to count how many items are diffent and I get an unexpected number in some of them. I attached a test file, but here is an image also to explain what I want:



    Have a look at "rc2a" in yellow. I'm trying to count how many "broker" (in green) are different for "rc2a" (yellow).


    I understand the "3" results (a blank cell, "broker2" and "broker3"), but I can't figure out why sometimes excel says "2" as a valid solution.


    Any ideas?


    Thanks a lot,

    Victor

    Files

    • Test.xlsx

      (9.54 kB, downloaded 71 times, last: )
  • Hello Victor,


    Probably Countifs() will not be applicable in your specific case ...


    What is your precise objective :


    1. Count DIstinct Unique items with multiple criteria

    or

    2. Count only Once the Number of Duplicates (or more)


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim,


    Thanks for answering :)


    I don't know why it is not applicable in this case, but if I can achieve it with other formulas is ok.


    I want to know, for every item in column C, how many different items are in column B (your first pointing).


    In the end, I need to have the following results:

    • mr1a = 0, because mr1a in column C has the same item in column B (e.g. "Broker1")
    • ig3a = 0
    • rc2a = 3, because for rc2a we have the following unique items in column B (a blank cell, "broker2" and "broker3").

    Another requirement is that I can't add auxiliary columns to the table...


    Any approaches to the goal?


    Thanks!

  • Hi Victor,


    Based on your latest explanations ... and my understanding ... there is a coherence issue to be raised :


    If your Rows #4 and #7 do generate a result of 3 in your count, then your Rows #2 and #3 should generate 1 ... and not 0 ... as indicated


    Thanks for your clarification ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim,


    No because, as I said, for the item mr1a there is only one broker which is the same for all of them (broker1), so there are 0 different brokers than "broker1" for that item (mr1a).


    Sorry, I don't know how to explain it better...


    Thanks

  • Hi Victor,


    If we stick to the number of "different brokers" on a line by line basis ... then your initial countifs formula does not return any inconsistency ...


    Indeed, with the same logic, for your item rc2a ... you do get 2 and 3 for Rows #4 & #5 and also 2 and 3 for Rows #7 & #8 ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Well ... let's take rc2a in your Row #4


    1. Rows #5, #7 and #8 have to be considered ... or a total of 3

    BUT

    2. Row #7 shows the same broker as the one shown in Row #4 ..

    So

    it is less 1 ... to get a result of 2, which is consistent with the logic you applied for mr1a to reach 0 ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Ok, thanks a lot man, now I see why it is not working for my purposes. Quite obvious right now that I see it haha


    Then, how can I count how many unique brokers are there for every item in column C? (I think is what you purpose in the beginning about counting unique items with multiple criterias).


    I mean, I should reach the following results:

    • Mr1a = 1
    • Rc2a = 3
    • Ig3a = 1

    Thanks!

  • You are welcome ... Glad you could sort it out ... :)


    Now we have reached the start line ... let me work out your formula ..

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks!!! It's almost a 20.000 rows worksheet so doing it manually is not an option, and I would like not to use VBA if possible... so thanks for having a look at it 🙂


    Well, and happy new year!!!

  • You are welcome ...


    Quick question regarding cell B5 ... is it empty or just blank ?


    Instead of a formula will draft a macro ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Cell B5 has no value.


    I know how to do it with a macro, but I need to be done by a formula, that is why I was trying with countifs.

    If you can't come up with a way of solving this by a formula then it's ok, don't worry, you have helped me quite a lot. I know that not everything can be solve with just formulas 😊

  • Hi royUk,


    Sorry but I don't understand what you are saying/asking.


    I need a formula because I can't use VBA in this particular sheet. It's a job for my work and there's nothing I can do with that, unfortunately.


    Thanks!

  • Post #!!

    Quote




    Thanks!!! It's almost a 20.000 rows worksheet so doing it manually is not an option, and I would like not to use VBA if possible... so thanks for having a look at it

  • As far as the formula is concerned ... attached is your Test file


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks Carim. It looks pretty what I need. :)

    Now I have to study your formula to understand it hehe.


    I will try it on the real project to see if the array formula is too heavy for calculate all that rows or if it works as good as in the test file.


    Either way, thank you very much for your time and explanations.


    Have a very nice day!

    Cheers!


  • Thanks a lot for your Thanks ...:)


    Hope the formula will help you out ...


    Should you need a macro based solution, feel free to come back to the Forum;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)