Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    count functions (2000)

    Hello, have two columns, say one with numbers and another with product type in a string. Is there a way I can use countif or some type of count function to count on the two columms so if I has say three records reading ford 3, ford 2, ford 3 and I wanted to count the number of records with ford 3, i.e. 2 instances in this example, how can I code it?
    Many thanks Darren.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: count functions (2000)

    Say the names are in A2:A100 and the numbers are in B2:B100. The formula
    <code>
    =SUMPRODUCT((A2:A100="ford")*(B2:B100=3))
    </code>
    will count the number of occurrences of the combination of "ford" and 3.

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count functions (2000)

    thanks Hans, I thought it worked but after testing it doesn't seem to stack up all the time, perhaps i have missed something. Attached is my sheet, for instace under the sheet "summary", Answer = 3 and Area = Tecs has 4 entries yet the function returns 1, any ideas?

    Thanks Darren
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: count functions (2000)

    Column C contains a mixture of numbers and text values that look like numbers. For example, C13 contains '5. The apostrophe makes Excel interpret it as a text value. You should either convert all values in column C to numbers, or change the formula to

    =SUMPRODUCT((VALUE(C2:C300)=4)*(D2300="tecs"))

  5. #5
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count functions (2000)

    That makes sense Hans, thanks, I had inherited the sheet and didn't realise, kindest regards.

    Darren.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •