Home Forum Bike Forum More spreadsheeting help required……

Viewing 12 posts - 1 through 12 (of 12 total)
• ac505
Member

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

leffeboy
Subscriber

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

njee20
Subscriber

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

stumpyjon
Subscriber

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.

sunnybono
Member

In cell C2 copy the below

=IF(OR(B2=123,B2=234,B2=345),3,””)

and then copy down.

nickdavies
Subscriber

=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 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

Formula to count the Bs