More spreadsheeting help required……

Home Forum Bike Forum More spreadsheeting help required……

Viewing 12 posts - 1 through 12 (of 12 total)
  • More spreadsheeting help required……
  • ac505

    I am sure there is a way to do this but it is well beyond my skill set….. How would you construct a formula in excel to count the number of different items is a column which are grouped against the items in another column. Poorly explained, so best that I give an example.

    In the “team” column, team “A” and team “B”, if the column named “order number” contained the below, excel would return a result of 3 against team “A” and a result of 4 against team “B” ie the number of different order numbers grouped against each team?

    Team order number
    A…….. 123
    A…….. 234
    A…….. 345
    A…….. 345
    A…….. 345
    B…….. 345
    B…….. 456
    B…….. 456
    B…….. 567
    B…….. 678
    (i’ve just used the…’s to space things out to make it more readable)

    No idea where to start, maybe the collective hive (or just stoner!) could help! Cheers all

    Premier Icon leffeboy

    yep. was going to suggest pivottables as well. You set it with something like Team for columns and ‘Order Number’ for rows and set the value to be the count of the values (rather than the average). Then set another row to ‘count’ the values of ‘order number’ for each team

    Works but ugly as. Must be a better way

    Premier Icon njee20

    Edit: actually, having read the question that’s wrong.

    Google count unique distinct values

    Premier Icon stumpyjon

    Sort the two columns first column A then B. Put the following formula in column C .=if (B1<> B2, A1,””). Copy down the full column. You should now have an A or B everytime the number changes. Finally stuck a formula in another cell, =countif (C:C,”A”) to count up the results.

    OK. there’s a few ways you could do it.
    An easy way would be to add a new column for A and B teams and use equations to only fill that with the relevant items.
    Then use one of the functions like “count” to count up the number of unique items in the new column.

    I can’t do any more – I don’t have the analysis toolpack enabled at home. You can use array formulas on something like this too. Ctrl + Shift + Enter makes an equation apply to elements in an array.


    In cell C2 copy the below


    and then copy down.

    Premier Icon nickdavies


    That’s the formula I think you want, modified to suit your data. It will count a range of values and tell you how many unique values there are.

    1 Team A 123
    2 Team A 123
    3 Team A 245
    4 Team A 456
    5 Team A 456

    The above formula would return you a result of 3.
    Is that what you’re after?

    You just have a function for each team then, and define the range of cells for each.

    Leading on from Nick’s formula, if you want to specify the letter to count then use this (the target letter is in lower case in the example below)

    Formula to count the As

    formula1 by reggiegasket, on Flickr

    Formula to count the Bs
    formula2 by reggiegasket, on Flickr

    And so on… This assumes the letters are in column A and the numbers in column B. You could also put the target letter in a cell and use a cell reference, instead of “a”…


    Thanks all!!! Will have a pop at it tomorrow morning. Too much nectar to think straight at the moment

Viewing 12 posts - 1 through 12 (of 12 total)

The topic ‘More spreadsheeting help required……’ is closed to new replies.