Unable to derive Simple Multiplication result with IF(AND.... Cell Value 0 or Blank

[RapchikM]

Hello
Can anyone help me to correct the following formula uisng IF(AND....

I am trying to put value in Cell E57 as 0 or leave E57 as blank then Why does the following formula don't work ?
in cell H57 i ve incorporated the following formula
=IF(AND(E57="",E57=0), D57*1*F57, D57*E57*F57)

if Cell E57 = 0 then Multiplication result is derived But if Cell E57 is Blank then H57 = 0 instead of Calculated value

How can i derive a simple mulitplication result with Cell incoropated with 0 , 0.00 and with Cell Blank


RapchikM

 

[RapchikM]

Sir My Mistake in Posting the range Kindly Correct as Below
range is from D16: H19

D E F G H

13​		1.65​		21.45
13​	200​	4.10​	​	106.60
13​	200​	7.45​	​	193.70
13​	322​	8.25​	​	345.35

667.10
Want in H20 the sum using the IF(OR formula...... as 667.10

in H16 formula written =IF(OR(E16="",E16=0), D16*1*F16, (D16*E16*F16)/100)

Hope The above range makes it clear

RachikM

 

[Peter_SSs]

What about

=SUMPRODUCT(D16:D19*IF(E16:E19=0,100,E16:E19)*F16:F19)/100

 

[RapchikM]

=SUMPRODUCT(D16:D19*IF(E16:E19=0,100,E16:E19)*F16:F19)/100

I get error #Value

 

[Peter_SSs]

I get error #Value

Can you post your sample data with XL2BB so that I can be sure that I am using exactly the data as you?

 

[RapchikM]

sumproduct-formula.xlsx
	D	E	F	G	H
16	13	0	1.65		21.45
17	13	200	4.10	330.00	106.60
18	13	200	7.45	330.00	193.70
19	13	322	8.25	1238.90	345.35
20					#VALUE!
Sheet1

Cell Formulas
Range	Formula
G17:G19	G17	=G16+(E17-E16)*F16
H16:H19	H16	=IF(OR(D16="",D16=0), 1*F16, IF(OR(E16="",E16=0),D16*1*F16,(D16*E16*F16)/100))
H20	H20	=SUMPRODUCT(D16:D19*IF(E16:E19=0,100,E16:E19)*F16:F19)*100

 

[Peter_SSs]

Thanks for the XL2BB sample. Unfortunately it did not help determine why you have an error. When I copied your sheet directly to mine it did not reproduce that error.
I'm also wondering why you changed from dividing by 100 to multiplying by 100 as that will change completely what my formula does?

Below I have repeated your sheet and added my original formula in cell H21.

What happens if you take a new blank worksheet then copy from my post here by clicking the copy button near the top-left of the mini sheet ..

.. then select cell D16 in your fresh sheet and then Paste?

If you still get an error, try selecting the formula cell, press F2 and then re-confirm the formula with Ctrl+Shift+Enter, not just Enter.

RapchikM.xlsm
	D	E	F	G	H
16	13	0	1.65		21.45
17	13	200	4.1	330	106.6
18	13	200	7.45	330	193.7
19	13	322	8.25	1238.9	345.345
20					6670950
21					667.095
Sheet2

Cell Formulas
Range	Formula
G17:G19	G17	=G16+(E17-E16)*F16
H16:H19	H16	=IF(OR(D16="",D16=0), 1*F16, IF(OR(E16="",E16=0),D16*1*F16,(D16*E16*F16)/100))
H20	H20	=SUMPRODUCT(D16:D19*IF(E16:E19=0,100,E16:E19)*F16:F19)*100
H21	H21	=SUMPRODUCT(D16:D19*IF(E16:E19=0,100,E16:E19)*F16:F19)/100

 

[RapchikM]

Peter_SSs

press F2 and then re-confirm the formula with Ctrl+Shift+Enter, not just Enter.

Do i have to always do the ? for eg if range is copied to different sheet as this was observed and
why not just incorporate {=SUMPRODUCT(D16:D19*IF(E16:E19=0,100,E16:E19)*F16:F19)/100} and press Enter

HatsOff to your expertize guidance

Thank you so much

RapchikM

 

[Peter_SSs]

why not just incorporate {=SUMPRODUCT(D16:D19*IF(E16:E19=0,100,E16:E19)*F16:F19)/100} and press Enter

Easiest way to answer that is to try it.

Thank you so much

RapchikM

You are very welcome.

 

[RapchikM]

Sir

why not just incorporate {=SUMPRODUCT(D16:D19*IF(E16:E19=0,100,E16:E19)*F16:F19)/100}

{=SUMPRODUCT(D16:D19*IF(E16:E19=0,100,E16:E19)*F16:F19)/100}

1. If you may please explain Why have you Entered 100 marked in Bold and italic ?

2. What would be appropriate Formula to get 677.10 if My Cell E19 Value will be always 500
because in Cell C20 i've incorporated as 822 and to get result of 677.10 in cell H20 i need to 822-500 = 322 and therefore the result is 677.10
But when E19 is with value 500 then Cell Value is H20 858 i've purposely put the value in H21 677.10

sumproduct-formula.xlsx
	C	D	E	F	G	H
16		13	0	1.65		21.45
17		13	200	4.10	330.00	106.60
18		13	200	7.45	330.00	193.70
19		13	500	8.25	2565.00	536.25
20	822					858.00
21						677.10
Sheet1

Cell Formulas
Range	Formula
G17:G19	G17	=G16+(E17-E16)*F16
H16:H19	H16	=IF(OR(D16="",D16=0), 1*F16, IF(OR(E16="",E16=0),D16*1*F16,(D16*E16*F16)/100))
H20	H20	=SUMPRODUCT(D16:D19*IF(E16:E19=0,100,E16:E19)*F16:F19)/100
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

If you may please explain Why have you Entered 100 marked in Bold and italic ?

Because when column E is 0 you want to multiply by 1. Since everything is divided by 100 at the end of my formula I need 100 here so that at the end for any 0 rows the calculation will be 100/100 which is 1 as you want.

What would be appropriate Formula to get 677.10 if My Cell E19 Value will be always 500
because in Cell C20 i've incorporated as 822

What about this (still confirmed with Ctrl+Shift+Enter)

=(SUMPRODUCT(D16:D18*IF(E16:E18=0,100,E16:E18)*F16:F18)+D19*(C20-E19)*F19)/100