Comparing and Counting Data

B

Brandy

I'm a newbie, so please simplify for me...

How many times does (B:B = "d" and G:G = "m") occur.

Suggestions?
 
B

Brandy

Thanks TJ, but perhaps I need to clarify. I'm looking for
how to add how many times BOTH of those things occur.

Make sense?
 
T

tjtjjtjt

Did you get an answer? This might work, but you'd need cell references instead of column references:
=SUMPRODUCT(--(B1:B20="d"),--(D1:D20="m"))

If you want to explore named ranges to make the ranges dynamic, check out:
http://www.cpearson.com/excel/named.htm
A drawback to the formula below is that if the Column B & D aren't the same length, it returns an #VALUE error. I haven't worked out a way to fix that.


If you define dynamic range like so (as described on the web page provided above):
=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B),1)
you can have the range automatically expand as you add letters to the bottom of you column.
Same deal for the other column:
=OFFSET(Sheet1!$D$1,0,0,COUNTA(Sheet1!$D:$D),1)

Then you can deal a formula like this:
=SUMPRODUCT(--(Column_B="d"),--(Column_D="m"))

In the formula above, Column_B is the name of the first range, Column_D is the name of the second range.

tj
 
Top