Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing List entries
I have a column of drop down lists with 5 possible entries, say "A", "B",
"C", "D" and "E". I am trying to return a total count for each of the 5 possible entries in the column. Any ideas how I might be able to do this? Hope this makes sense. Cheers! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing List entries
Say the drop downs are in Column A,
In B1 to B5 enter A to E, Then in C1, enter: =Countif(A:A,B1) And copy down to C5. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Zoltan" wrote in message ... I have a column of drop down lists with 5 possible entries, say "A", "B", "C", "D" and "E". I am trying to return a total count for each of the 5 possible entries in the column. Any ideas how I might be able to do this? Hope this makes sense. Cheers! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing List entries
Try something like this:
With A1:A100 containing the entries of the 5 values, or blanks B1: A B2: B B3: C B4: D B5: E C1: =COUNTIF($A$1:$A$100,B1) Copy that formula down through C5 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Zoltan" wrote: I have a column of drop down lists with 5 possible entries, say "A", "B", "C", "D" and "E". I am trying to return a total count for each of the 5 possible entries in the column. Any ideas how I might be able to do this? Hope this makes sense. Cheers! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing List entries
One way, use COUNTIF
Source data running in A2 down List in C2:C6 : A, B, C, D, E Then in D2: =COUNTIF(A:A,C2) Copy down to D6 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Zoltan" wrote: I have a column of drop down lists with 5 possible entries, say "A", "B", "C", "D" and "E". I am trying to return a total count for each of the 5 possible entries in the column. Any ideas how I might be able to do this? Hope this makes sense. Cheers! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing List entries
Thanks Guys!!!
That works great! Is it possible to refine this count further by only returning a count if a condition in another column is also met? For example, say I have my drop down lists in column A and in column B there are only "1"s and "0"s or "YES" and "NO". Can I return a count for the initial 5 possibilites only if they are paired with a "1" or "YES"? In other words, if A1 contains "C" and B1 contains "1", count 1 for "C" but if A1 contains "C" and B1 contains "0" or is blank return a count of 0 for "C" Sorry I'm sure I could have worded this better, hope it makes sense. Cheers! "Max" wrote: One way, use COUNTIF Source data running in A2 down List in C2:C6 : A, B, C, D, E Then in D2: =COUNTIF(A:A,C2) Copy down to D6 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Zoltan" wrote: I have a column of drop down lists with 5 possible entries, say "A", "B", "C", "D" and "E". I am trying to return a total count for each of the 5 possible entries in the column. Any ideas how I might be able to do this? Hope this makes sense. Cheers! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing List entries
Is it possible to refine this count further by only returning a count if a
condition in another column is also met? For multi-criteria counts, you could use sumproduct, eg: In C2: =SUMPRODUCT((A2:A100="A")*(B2:B100="YES")) will return the counts of cases satisfying col A = "A" and col B = "YES" You need to define the ranges. Entire col references, eg: A:A, B:B cannot be used in sumproduct. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Zoltan" wrote: Thanks Guys!!! That works great! Is it possible to refine this count further by only returning a count if a condition in another column is also met? For example, say I have my drop down lists in column A and in column B there are only "1"s and "0"s or "YES" and "NO". Can I return a count for the initial 5 possibilites only if they are paired with a "1" or "YES"? In other words, if A1 contains "C" and B1 contains "1", count 1 for "C" but if A1 contains "C" and B1 contains "0" or is blank return a count of 0 for "C" Sorry I'm sure I could have worded this better, hope it makes sense. Cheers! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing List entries
Thanks!!!
I got it to work with "YES" but it doesn't work if I use "1"s and "0"s. Is there any way to make it work with numbers? Sorry about the new thread I posted, I'm new to this. Cheers! "Max" wrote: Is it possible to refine this count further by only returning a count if a condition in another column is also met? For multi-criteria counts, you could use sumproduct, eg: In C2: =SUMPRODUCT((A2:A100="A")*(B2:B100="YES")) will return the counts of cases satisfying col A = "A" and col B = "YES" You need to define the ranges. Entire col references, eg: A:A, B:B cannot be used in sumproduct. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Zoltan" wrote: Thanks Guys!!! That works great! Is it possible to refine this count further by only returning a count if a condition in another column is also met? For example, say I have my drop down lists in column A and in column B there are only "1"s and "0"s or "YES" and "NO". Can I return a count for the initial 5 possibilites only if they are paired with a "1" or "YES"? In other words, if A1 contains "C" and B1 contains "1", count 1 for "C" but if A1 contains "C" and B1 contains "0" or is blank return a count of 0 for "C" Sorry I'm sure I could have worded this better, hope it makes sense. Cheers! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing List entries
If you're using numbers as the criteria, just drop the double quotes, eg:
In C2: =SUMPRODUCT((A2:A100="A")*(B2:B100=1)) The double quotes is necessary for text. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Zoltan" wrote: Thanks!!! I got it to work with "YES" but it doesn't work if I use "1"s and "0"s. Is there any way to make it work with numbers? Sorry about the new thread I posted, I'm new to this. Cheers! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing List entries
Is it possible to expand this further by only returning a count if a number
is present in a third column. This number does not have to be anything specific it is just nessasary that a number be entered in the third column. eg: D2: =SUMPRODUCT((A2:A100="A")*(B2:B100=1)*(C2:C100=ANY NUMBER?)) Thanks again for all your help. "Max" wrote: If you're using numbers as the criteria, just drop the double quotes, eg: In C2: =SUMPRODUCT((A2:A100="A")*(B2:B100=1)) The double quotes is necessary for text. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Zoltan" wrote: Thanks!!! I got it to work with "YES" but it doesn't work if I use "1"s and "0"s. Is there any way to make it work with numbers? Sorry about the new thread I posted, I'm new to this. Cheers! |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing List entries
=SUMPRODUCT((A2:A100="A")*(B2:B100=1)*isnumber(c2: c100))
Zoltan wrote: Is it possible to expand this further by only returning a count if a number is present in a third column. This number does not have to be anything specific it is just nessasary that a number be entered in the third column. eg: D2: =SUMPRODUCT((A2:A100="A")*(B2:B100=1)*(C2:C100=ANY NUMBER?)) Thanks again for all your help. "Max" wrote: If you're using numbers as the criteria, just drop the double quotes, eg: In C2: =SUMPRODUCT((A2:A100="A")*(B2:B100=1)) The double quotes is necessary for text. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Zoltan" wrote: Thanks!!! I got it to work with "YES" but it doesn't work if I use "1"s and "0"s. Is there any way to make it work with numbers? Sorry about the new thread I posted, I'm new to this. Cheers! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing numbers in a list when they meet a critieria in another co | Excel Discussion (Misc queries) | |||
Summing the last 7 non-blank entries in a row of data | Excel Discussion (Misc queries) | |||
Help...Summing Items Scattered in a list | Excel Discussion (Misc queries) | |||
Removing all entries in one list that appear in a different list | Excel Discussion (Misc queries) | |||
List only red entries | Excel Worksheet Functions |