countif with second if?
 

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

countif with second if?
 

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/

countif with second if?
 

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

countif with second if?
 

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/