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.

 

[kiran5388]

How about

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

This 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")))"

 

[Fluff]

You never made any mention of col B being blank or 0.

 

[Peter_SSs]

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")))"

Since you specifically talked about blank (=0) cells in the original question & examples but did not mention or use such examples (for col B) in the second question, like Fluff, I assumed there would be no blank cells in col B. Helpers can only go on what they are told and/or shown. ?

 

[kiran5388]

hi, Im using excel 365, using excel template and formulas I will be generating the dynamic data in .net. I am facing an issue with the formulas where "_xlws. " getting added to the formulas and the output result is wrong. For example :
Input formula is =IFERROR(SUM(FILTER($Q$16:Q25/$R$16:R25,$R$16:R25<>0)),0) and Output Formula is changed to =IFERROR(SUM(_xlws.FILTER(@$Q$16:Q25/@$R$16:R25,@$R$16:R25<>0))/100,0)
as a result answer 0 instead of 18.43.
Please find attached snip. Any help will be greatly appreciated. Thanks

 

[kiran5388]

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.

Is there any alternative for Filter function for above formula? I have issue outputting using it.

 

[Peter_SSs]

I have issue outputting using it.

Can you expand on that? What is the specific issue? Perhaps example(s)?

 

[kiran5388]

Can you expand on that? What is the specific issue? Perhaps example(s)?

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

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.

www.mrexcel.com

the above link was my issue. I have only issue with the Filter function rest of the formula functions works well like SumProduct