how can I simultaneously divide each corresponding cells of two columns and sum all the results by ignoring blank cells

[kiran5388]

how can I simultaneously divide each corresponding cells of two columns and sum all the results by ignoring blank cells.

Please find the screenshot.
B12=SUMPRODUCT(1/A9:A11,B9:B11)
B19=SUMPRODUCT(NOT(ISBLANK(A16:A18)),1/A16:A18,B16:B18) - not the expected result

Thanks in Advance.

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this what you want?

21 09 07.xlsm
	A	B	C	D
1
2	2	4		3
3		6
4	2	2
Divide

Cell Formulas
Range	Formula
D2	D2	=SUM(FILTER(B2:B4,A2:A4<>0)/FILTER(A2:A4,A2:A4<>0))

 

[kiran5388]

=SUM(FILTER(B2:B4,A2:A4<>0)/FILTER(A2:A4,A2:A4<>0))

Exactly what I am looking for. Thankyou very much

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[kiran5388]

How can I get the result using Product 1 criteria in Below Screenshot.?

 

[Fluff]

How about

=SUM(FILTER(C1:C5,(C1:C5<>0)*(A1:A5="Product1"))/FILTER(B1:B5,(C1:C5<>0)*(A1:A5="Product1")))

 

[kiran5388]

How about

=SUM(FILTER(C1:C5,(C1:C5<>0)*(A1:A5="Product1"))/FILTER(B1:B5,(C1:C5<>0)*(A1:A5="Product1")))

This is amazing.

 

[Fluff]

Glad to help & thanks for the feedback

 

[Peter_SSs]

What about

=SUM(FILTER(C1:C5/B1:B5,(C1:C5<>0)*(A1:A5="Product1")))

.. and a similar simplification for the original question

=SUM(FILTER(B2:B4/A2:A4,A2:A4<>0))

 

[kiran5388]

What about

=SUM(FILTER(C1:C5/B1:B5,(C1:C5<>0)*(A1:A5="Product1")))

.. and a similar simplification for the original question

=SUM(FILTER(B2:B4/A2:A4,A2:A4<>0))

this "=SUM(FILTER(C1:C5/B1:B5,(C1:C5<>0)*(A1:A5="Product1")))" will be div/0 if denominator is 0 in any of the cell i.e in B column. Therefore the correct formula is "=SUM(FILTER(C1:C5/B1:B5,(B1:B5<>0)*(A1:A5="Product1")))"

The original question answer is perfectly fine. Thank you.
I learnt something new.