- 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
(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 allPosted 5 years agotitusriderMember
ideas to try here but not sure myself:Posted 5 years ago
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 wayPosted 5 years agostumpyjonSubscriber
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.Posted 5 years agonickdaviesSubscriber
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.Posted 5 years agoreggiegasketMember
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
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”…Posted 5 years ago
The topic ‘More spreadsheeting help required……’ is closed to new replies.