Sumproduct only visible rows

[Jagat Pavasia]

Dear sir,
I have item name in "A2 to A6".
"B2 to B6" have quantity of items,
"C2 to C6" have cost of items.

I want answer in D1 that if I selected item only " apple' than automatically sum-product visible raw.

I have attach screenshot with 3 example, please help me that which formula for "D1" ?


help

 

[Sufiyan97]

Try

=SUBTOTAL(9,C2:C50)

 

[Jagat Pavasia]

Try

=SUBTOTAL(9,C2:C50)

dear sir, I did not want total, I want sumproduct means average cost of items

 

[Sufiyan97]

Try

=SUBTOTAL(1,C2:C50)

 

[Jagat Pavasia]

Try

=SUBTOTAL(1,C2:C50)

I try this but wrong answer displayed

 

[Fluff]

Add a helper column in D with

=SUBTOTAL(103,A2)

and then you can use

=SUMPRODUCT(B2:B6,C2:C6,D2:D6)/SUMPRODUCT(B2:B6,D2:D6)

 

[Jagat Pavasia]

Add a helper column in D with

=SUBTOTAL(103,A2)

and then you can use

=SUMPRODUCT(B2:B6,C2:C6,D2:D6)/SUMPRODUCT(B2:B6,D2:D6)

yes, it is working as it should,

but, is it necessary for helper column ? other any idea, sir ?

 

[Fluff]

No it's not necessary, but it's by far the simplest & most efficient way.

 

[Sanjeev1976]

Try this formula in D1 without using a helper column :

=SUMPRODUCT(--(A1:A6=INDEX(A2:A6,MIN(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A6)-ROW(A2),0)),ROW(A2:A6)-ROW(A2)+1)))),B1:B6,C1:C6)/SUMPRODUCT(--(A1:A6=INDEX(A2:A6,MIN(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A6)-ROW(A2),0)),ROW(A2:A6)-ROW(A2)+1)))),B1:B6)