Summing List entries

[Guest]

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!

 

[Ragdyer]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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!

 

[Guest]

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.

---

 

[Guest]

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!

 

[Guest]

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.

 

[Guest]

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.

 

[Dave Peterson]

=SUMPRODUCT((A2:A100="A")*(B2:B100=1)*isnumber(c2:c100))