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

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"))

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,

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 oBecause of the LEFT function, it only looks at the first character of the values in Column B.

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.

Are you saying that you have other values that start with t? t

t.new

t.old

t.something else

t.something something elseKeep 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")))

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.

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 messagedoesn'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.

Just an FYI...I tested it in 2010 and it works just fine.

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History