Two criteria for a subtotal

  • Thread starter Thread starter rob nobel
  • Start date Start date
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
 
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)),,))))
 
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...
 
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
 
Back
Top