Sum if and Subtotal formula

[tarnpiet]

Hi, I need some help please. I have trying to subtotal column Z for cells where column AA=No.

The formulas work individually but I can't work out how to put them together in the one cell/formula.

Currently using:
SUBTOTAL(109,Z4:Z9600)
SUMIF(AA4:AA9600,"No",z4:Z9600)

But how do I string them together?? Thanks in advance.

 

[Aladin Akyurek]

Hi, I need some help please. I have trying to subtotal column Z for cells where column AA=No.

The formulas work individually but I can't work out how to put them together in the one cell/formula.

Currently using:
SUBTOTAL(109,Z4:Z9600)
SUMIF(AA4:AA9600,"No",z4:Z9600)

But how do I string them together?? Thanks in advance.

Try...

=SUMPRODUCT(
  SUBTOTAL(109,OFFSET(Z4,ROW(Z4:Z9600)-ROW(Z4),0)),
  (AA4:AA9600="No")+0)