formula problem

[Dave Hunt]

Hi gang,

I have a worksheet with two columns - each row in the
first column contains a value A, B, C, or D. Each row in
the second column contains a value from 1 - 36.

Example:

col1 col2
A 22
A 10
B 4
B 33
C 8
C 14
D 16
D 25

I'm trying to create a formula that allows me to count
the number of instances where the value in column 1
equals D *and* the value in column 2 is greater than 18.

So, I have this: =COUNTIF(A1:A6, "D") gives 2
and this: =COUNTIF(B1:B6, ">18") gives 3
but it's not clear to me how to link them up. I tried
AND, but it doesn't give me a count ( answer should be 1
in this example).

Bonus question: how can I use the value (18) in a
reference cell as the criteria (example: >$c$1 where $c$1
contains 18) instead of a literal string.

Thanks in advance
Dave

 

[Biff]

Hi Dave,

Try this:

=SUMPRODUCT((A1:A6="D")*(B1:B6>18))

Biff

 

[Dave Hunt]

Thanks Biff, that works great! You rule!

-----Original Message-----
Hi Dave,

Try this:

=SUMPRODUCT((A1:A6="D")*(B1:B6>18))

Biff

.

 

[Biff]

You're very welcome and no, YOU RULE!

-----Original Message-----
Thanks Biff, that works great! You rule!

.