Excel: Count multi intity in the same column

October 14, 2010 at 02:59:47
Specs: Windows XP

Column B Column D
T c
T.new d
J c

=SUMPRODUCT((B5:B48="t")*(P5:P48="c")) <--This will count number of t=c Result=1

Question: How do I count the howmany T & T.new = c ????? (note not only T=c but T.new=c as well)?

This is counting for two things in one row (row B) equal to one thing in another (row D)

Please let me know how I can write this function/formula on Excle

Thanks

Nazdar


See More: Excel: Count multi intity in the same column

Report •

#1
October 14, 2010 at 04:57:52
re: "This is counting for two things in one row (row B)..."

B is a Column, not a Row.

re: "...equal to one thing in another (row D)"

D? Your formula references Columns B and P. Where does Column D fit into this situation?

Is this what you are looking for?

=SUMPRODUCT((LEFT(B5:B48,1)="t")*(P5:P48="c"))


Report •

#2
October 15, 2010 at 00:11:02
Sorry for the confusion, I meant to say Column B contains (t, t.new,J) while column P (c,o,c).

I like to know how many t & t.new there that equal to c ????

The equation I know (see below) only counts for one variable "t", however, I need to count for "t.new" variable as well.

=SUMPRODUCT((B5:B48="t")*(P5:P48="c"))

Please help.


Thanks,


Report •

#3
October 15, 2010 at 06:21:44
I understand what you are trying to do. Did you try the formula I suggested?

It should do what you are asking for.

In this example the formula I suggested in Response # 1 returns 2:

	B	P
5	t	c
6	t.new	c
7	j	o

Because of the LEFT function, it only looks at the first character of the values in Column B.


Report •

Related Solutions

#4
October 15, 2010 at 06:39:38
Thanks for responding.

Your suggestion is very good. However, in my case.. since I have over 100 rows of differnt values (other then, the three i provided as examples; t, t.new, j). Therefore, I need to count for only two of the characters (t and t.new) and leave the rest as is.

So, how can I count two variables in the same column while have them match another catagory in another column.

Hope it is clear that I only want to look for t and t.new that happen to be 'c'.

Thanks.


Report •

#5
October 15, 2010 at 06:56:29
Are you saying that you have other values that start with t?

t
t.new
t.old
t.something else
t.something something else

Keep in mind that we can not see your spreadsheet from where we are sitting so you need to supply very detailed examples of you data.

Assuming you have a table that looks like this, try the formula below, which will return 8:

	B	                P
5	t	                c
6	t.new	                p
7	j	                c
8	t	                x
9	t.old	                c
10	j	                c
11	t	                c
12	t.something else         c
13	j	                c
14	t	                c
15	t.new	                c
16	j	                c
17	t	                c
18	t.new	                c
19	j	                c
20	t	                c
21	t.new	                c
22	j	                c

=SUM(SUMPRODUCT((B5:B48="t")*(P5:P48="c")),
SUMPRODUCT((B5:B48="t.new")*(P5:P48="c")))


Report •

#6
October 15, 2010 at 07:30:43
Thanks, yes this is what i meant wanting to do..

But unfortuantly when i type or past the equation it doensn't like it, and gives an error message. I am not sure why, I blieve the formula is ok, but something is trigering and not working. I am using office 2007 excel.

Anyway, like i said the formula is ok but it's not going through.


Report •

#7
October 15, 2010 at 07:47:34
As I said before, Keep in mind that we can not see your spreadsheet from where we are sitting.

Telling us that it gives an error message doesn't help us help you.

Imagine how much more help we could be if you included the text of the error message in your post.

The formula was split into two lines to make it easier to read, but that shouldn't make any difference. I copied the formula directly from my post into an Excel 2003 spreadsheet and it worked just fine.

I can't test it in 2007, but I see no reason why it shouldn't work. I can test it in 2010 when I get home tonight.


Report •

#8
October 15, 2010 at 20:44:01
Just an FYI...I tested it in 2010 and it works just fine.

Report •

Ask Question