Home › Forum › Bike Forum › More spreadsheeting help required……

- More spreadsheeting help required……
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

Posted 5 years agoideas to try here but not sure myself:

Posted 5 years ago

http://stackoverflow.com/questions/11876238/simple-pivot-table-to-count-unique-valuesyep. 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

Posted 5 years agoEdit: actually, having read the question that’s wrong.

Posted 5 years agoGoogle count unique distinct values

Posted 5 years agoSort 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.

Posted 5 years agoOK. there’s a few ways you could do it.

Posted 5 years ago

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.

Posted 5 years ago=SUM(IF(FREQUENCY(B1:B5,B1:B5)>0,1))

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.

Assuming:

____A_____B__

1 Team A 123

2 Team A 123

3 Team A 245

4 Team A 456

5 Team A 456The 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.

Posted 5 years agoLeading 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 FlickrAnd 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”…

Posted 5 years agoThanks all!!! Will have a pop at it tomorrow morning. Too much nectar to think straight at the moment

Posted 5 years ago

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