Two criteria for a subtotal

R

rob nobel

I think I've asked this some years ago but can't seem to find the answer to
it.
I have =subtotal(9,K26:K1525) but I need it to only subtotal the rows if
the value in I26:I1525=G5
Can someone suggest a formula for this?
Rob
 
P

Peo Sjoblom

One way

=SUMPRODUCT(--($I$26:$I$1525=G5),--($K$26:$K$1525),--(SUBTOTAL(3,OFFSET($I$2
6,ROW($I$26:$I$1525)-MIN(ROW($I$26:$I$1525)),,))))
 
R

rob nobel

Thanks Peo! This one works very well.
Rob

Peo Sjoblom said:
One way

=SUMPRODUCT(--($I$26:$I$1525=G5),--($K$26:$K$1525),--SUBTOTAL(3,OFFSET($I$2
6,ROW($I$26:$I$1525)-MIN(ROW($I$26:$I$1525)),,))))

--

Regards,

Peo Sjoblom
Click to expand...
 
R

rob nobel

Thanks mudraker, but I think my question was interporated incorrectly as it
was not the sum of that column but the actual value of each cell in that
column that needs to equal G5. Nevertheless, Peo has given a reply that
works great.
Rob
 
Top