Ignore #N/A across information (SUMIF)?

[bergen]

Hi,

I have read how to ignore #N/A using sumif in another post but I don't think this will work in my case.
I am trying to ignore all rows containing #N/A, not only the part of the formula containing the #N/A cell.

I have a file I can upload with new data all the time so just filtering away the #N/A wouldn't work, since it will mess up the count and the formulas.
This is what I am trying to do:
=ABS(SUMPRODUCT($B$2:$B$4)/SUMPRODUCT($D$2:$D$4;$C$2:$C$4)-1)
Which will give me an average discount ont the products I sell.
However whenever my formula hits an #N/A it doesn't work, and I don't want to only exclude the #N/A from the second part of the formula (because that will give me a much larger sum in the first part of the formula and will not provide me with the right discount)
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Sales Price (net)[/TD]
[TD]Quantity[/TD]
[TD]Catalogue (list) price[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Product A[/TD]
[TD]80[/TD]
[TD]2[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Product B[/TD]
[TD]70[/TD]
[TD]4[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Product C[/TD]
[TD]60[/TD]
[TD]5[/TD]
[TD]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

This seems like a pretty advanced question, but I hope someone will be able to answer.
Please ask me for more detail if needed.

Thank you!

 

[norulen]

Which version of excel you have?

 

[Aladin Akyurek]

Hi,

I have read how to ignore #N/A using sumif in another post but I don't think this will work in my case.
I am trying to ignore all rows containing #N/A, not only the part of the formula containing the #N/A cell.

I have a file I can upload with new data all the time so just filtering away the #N/A wouldn't work, since it will mess up the count and the formulas.
This is what I am trying to do:
=ABS(SUMPRODUCT($B$2:$B$4)/SUMPRODUCT($D$2:$D$4;$C$2:$C$4)-1)
Which will give me an average discount ont the products I sell.
However whenever my formula hits an #N/A it doesn't work, and I don't want to only exclude the #N/A from the second part of the formula (because that will give me a much larger sum in the first part of the formula and will not provide me with the right discount)
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]Sales Price (net)
[/TD]
[TD]Quantity
[/TD]
[TD]Catalogue (list) price
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Product A
[/TD]
[TD]80
[/TD]
[TD]2
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Product B
[/TD]
[TD]70
[/TD]
[TD]4
[/TD]
[TD]80
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Product C
[/TD]
[TD]60
[/TD]
[TD]5
[/TD]
[TD]#N/A
[/TD]
[/TR]
</tbody>[/TABLE]

This seems like a pretty advanced question, but I hope someone will be able to answer.
Please ask me for more detail if needed.

Thank you!

Shouldn't that be

=ABS(SUM($B$2:$B$4)/SUMPRODUCT($D$2:$D$4;$C$2:$C$4)-1)

In case of #N/A's...

If it's just the range in D having #N/A's...

Control+shift+enter, not just enter:

=ABS(SUM($B$2:$B$4)/
  SUM(IF(ISNUMBER($D$2:$D$4);$D$2:$D$4*C$2:$C$4))-1)

Otherwise:

=ABS(SUM(SUMIF($B$2:$B$4;{"<0",">0"}))/
  SUM(IF(ISNUMBER($D$2:$D$4*C$2:$C$4);$D$2:$D$4*C$2:$C$4))-1)

 

[Marcelo Branco]

I have a file I can upload with new data all the time so just filtering away the #N/A wouldn't work, since it will mess up the count and the formulas.
This is what I am trying to do:
=ABS(SUMPRODUCT($B$2:$B$4)/SUMPRODUCT($D$2:$D$4;$C$2:$C$4)-1)

Which will give me an average discount ont the products I sell.

However whenever my formula hits an #N/A it doesn't work, and I don't want to only exclude the #N/A from the second part of the formula (because that will give me a much larger sum in the first part of the formula and will not provide me with the right discount)

Not sure if i understood correctly what you need, but i think that to get the average discount excluding N/A also from the first part of the formula, you need an array formula like

=ABS(SUM(IF(ISNUMBER(D2:D4),B2:B4*C2:C4))/SUM(IF(ISNUMBER(D2:D4),C2:C4*D2:D4))-1)

confirmed with Ctrl+Shift+Enter
(hold down both Ctrl ands Shift keys and hit Enter)

M.

 

[Peter_SSs]

Given the different results obtained from the suggestions so far, including mine, it might help if you provided the expected result for your sample data and why that is the expected result.

I'm suggesting a helper column as well as a slightly adjusted formula.

Note that you would need to replace some commas in my formula with semicolons for your language version.

Formula in E2 is copied down.

Excel Workbook
	B	C	D	E	F	G
1	Sales Price (net)	Quantity	Catalogue (list) price
2	80	2	100	100		0.71154
3	70	4	80	80
4	60	5	#N/A	0
5
bergen

 

[bergen]

Peter your solution was just what I needed, thank you very much!!!

Thank you all for your time and guide, I apologize for the inaccurate information in my question. The column "Sales Price (Net)" should have been called just "sales volume" since it should be the total sales (otherwise no need to add the quantity). And the discount I was looking for was 0,711.