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"

--
Message posted from http://www.ExcelForum.com

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

--
Regards
Frank Kabel
Frankfurt, Germany

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

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

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


---
Message posted from http://www.ExcelForum.com/

Thanks, that works.

One question though, When I record this

=SUMPRODUCT((Actual!H1:H1000="Open")*(Actual!O1:O1 000="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

--
Message posted from http://www.ExcelForum.com

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

--
Regards
Frank Kabel
Frankfurt, Germany

Thanks, that works.

One question though, When I record this

=SUMPRODUCT((Actual!H1:H1000="Open")*(Actual!O1:O1 000="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
but I can't work it out.


---
Message posted from http://www.ExcelForum.com/