countif with second if?

  • Thread starter Thread starter john_t_h
  • Start date Start date
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"
 
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
 
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
 
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
 
Back
Top