countif with second if?

J

john_t_h

I want to count entries in a column that equal a value only if a valu
in another column in the same row equals another value.

I can use COUNTIF(H:H,"foo") to count all cells with "foo" in coulm
H.

How do do it if I only want to count "foo" if column J equals "bar"
 
F

Frank Kabel

Hi
try the following (as COUNTIF only accepts one condition):
=SUMPRODUCT((H1:H1000="foo")*(J1:J1000="bar"))
Note: SUMPRODUCT does not accept range references like H:H -> therefore
I used H1:H1000
 
J

john_t_h

Thanks, that works.

One question though, When I record this

=SUMPRODUCT((Actual!H1:H1000="Open")*(Actual!O1:O1000="Y"))

to a macro it is written like this in VB

Range("D7").Select
ActiveCell.FormulaR1C1 = _

"=SUMPRODUCT((Actual!R[-6]C[4]:R[993]C[4]=""Open"")*(Actual!R[-6]C[11]:R[993]C[11]=""Y""))"

I assume there is some logic behind how the cell ranges are written bu
I can't work it out
 
F

Frank Kabel

Hi
the macro has recorede the formula in R1C1 style reference (check the
help for this type of cell references). e.g. you entered this formula
in cell D7:
The reference
Actual!R[-6]C[4]:
set the starting point 6 rows (-6) above D7 and 4 columns to the right
(4) -> H1
 
Top