| |
|
Formula Help for Numbers - IF statements & counting |
| |
|
|
|
17-06-2008, 12:25 PM
|
|
Beware the Robot Mafia
Group: Administrators
Location: St. Albans, Melbourne
|
Formula Help for Numbers - IF statements & counting
I've got a column of different options, say 1, 2, 3, 4, 5 - how can I get it to count how many times a certain string appears in the data set (the column)?
e.g: count how many times "item x" appears column A then print number, count how many times "item y" appears in column A then print number, count how many times "item z" appears in column A then print number etc.
|
|
|
|
|
|
|
|
17-06-2008, 12:32 PM
|
|
Regular
Group: Regulars
Location: Brisbane
|
This is clunky, but could you create another set of columns and use IF, THEN to assign 1 or 0 to the result (i.e. if A1 has data of response 1, then if A1=1 then C1 = 1, then tally up the C column as only the ones will get a value of 1 assigned, you could then go on and do it for A1 data response 2 for column D etc)...
I know what I mean, does this make sense?
It's not pretty but I've done it before for stuff like this (I asked a similar question on here earlier this year)...
I'm sure there are better ways of doing it, but this is how I managed in my non-technologically advanced way....
__________________
Successful Trades: krazy1, Astr0b0y, soulman, areal, smdnetau, Huy, Alessiman, step_andy, tibook, mulquemi, BoxDog, Devski, The Keddi, decryption
|
|
|
|
|
|
|
|
17-06-2008, 12:34 PM
|
|
It's a cruel, cruel summer
Group: Regulars
Location: NSW
|
If it was me I would sort column A, B, C, D, E (corresponding to 1-5) in ascending order (or descending it doesn't matter).
Then I would use a COUNT for the rows where the cells differ.
e.g.
COLUMN A (sorted in descending)
X
X
X
Y
Y
Z
=COUNT(A2:A4) -- for the total of X's (3)
=COUNT(A5:A6) -- for Y's (2)
=COUNT(A7) -- for Z's (1)
Of course this is a small data set but you can expand it for bigger ones and just look at the beginning and end cells for the references.
Hope that helps,
|
|
|
|
|
|
|
|
17-06-2008, 12:39 PM
|
|
MacTalk Donor
Group: Regulars
Location: Melbourne
|
Countif
Sumif
I'm pretty sure they exist in Numbers (not using mac at present); they do what I think you want in Excel.
|
|
|
|
|
|
|
|
17-06-2008, 12:44 PM
|
|
Beware the Robot Mafia
Group: Administrators
Location: St. Albans, Melbourne
|
Quote:
Originally Posted by Rasta
This is clunky, but could you create another set of columns and use IF, THEN to assign 1 or 0 to the result (i.e. if A1 has data of response 1, then if A1=1 then C1 = 1, then tally up the C column as only the ones will get a value of 1 assigned, you could then go on and do it for A1 data response 2 for column D etc)...
I know what I mean, does this make sense?
It's not pretty but I've done it before for stuff like this (I asked a similar question on here earlier this year)...
I'm sure there are better ways of doing it, but this is how I managed in my non-technologically advanced way....
|
So an IF statement like IF A6="x" THEN B6="1" IF A6="y" THEN C6="1" then add up each column?
Quote:
Originally Posted by Huy
If it was me I would sort column A, B, C, D, E (corresponding to 1-5) in ascending order (or descending it doesn't matter).
Then I would use a COUNT for the rows where the cells differ.
e.g.
COLUMN A (sorted in descending)
X
X
X
Y
Y
Z
=COUNT(A2:A4) -- for the total of X's (3)
=COUNT(A5:A6) -- for Y's (2)
=COUNT(A7) -- for Z's (1)
Of course this is a small data set but you can expand it for bigger ones and just look at the beginning and end cells for the references.
Hope that helps,
|
Hmm, I tried this - but the answer is always 0 for some reason
e.g: =COUNT(G2:G15) just results in 0 (the values in the cells are mixed text and numbers)
(btw, love the effort put into the colours, haha)
|
|
|
|
|
|
|
|
17-06-2008, 12:52 PM
|
|
Regular
Group: Regulars
Location: Brisbane
|
Here is the post I posted in March this year trying to do what you're suggesting, it was with Excel, but I assume Numbers would be similar
Excel Guru's Help Needed
__________________
Successful Trades: krazy1, Astr0b0y, soulman, areal, smdnetau, Huy, Alessiman, step_andy, tibook, mulquemi, BoxDog, Devski, The Keddi, decryption
|
|
|
|
|
|
|
|
17-06-2008, 12:54 PM
|
|
It's a cruel, cruel summer
Group: Regulars
Location: NSW
|
Try
=COUNTIF(A1:A6, "x")
=COUNTIF(A1:A6, "y")
=COUNTIF(A1:A6, "z")
where the A1:A6 represents the ENTIRE range and x, y, z represent the individual differences (different values).
That works for me in Excel and results in 3, 2, 1 in my above data set.
|
|
|
|
|
|
|
|
17-06-2008, 12:58 PM
|
|
Beware the Robot Mafia
Group: Administrators
Location: St. Albans, Melbourne
|
Quote:
Originally Posted by Huy
Try
=COUNTIF(A1:A6, "x")
=COUNTIF(A1:A6, "y")
=COUNTIF(A1:A6, "z")
where the A1:A6 represents the ENTIRE range and x, y, z represent the individual differences (different values).
That works for me in Excel and results in 3, 2, 1 in my above data set.
|
w00t - works just the way I want it! Thanks  |
|
|
|
|
|
|
|
|
|
|
|
|
|
