IF Formula Help

[Excel_Assis]

Hi can anyone help fix this IF formula please.

This IF formula is in cells in column E on sheet2.

{=SUM(IF(Sheet1!B$2:B$11=B4,IF(Sheet1!C$2:C$11=C4,((Sheet1!G$2:G$11=D4)*Sheet1!H$2:H$11)+((Sheet1!I$2:I$11=D4)*Sheet1!J$2:J$11))))}

This works correctly if there is data/values in all the cells.

However in cells in columns C, H and J, on sheet1 I have fomulas to place the data I need there. If these cells are blank only contain the formula with no value I get a Value error on sheet2 in column E cells.

I then can not SUM the column.

Also if there is no result / value to be entered in column B of Sheet2 I would like it to show in column E Sheet2 a blank cell.


Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri, Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 75px"><COL style="WIDTH: 118px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BACKGROUND-COLOR: #33cccc; COLOR: #ffffff; FONT-WEIGHT: bold">Date</TD><TD style="BACKGROUND-COLOR: #33cccc; COLOR: #ffffff; FONT-WEIGHT: bold">Item</TD><TD style="BACKGROUND-COLOR: #33cccc; COLOR: #ffffff; FONT-WEIGHT: bold">Acc No</TD><TD style="BACKGROUND-COLOR: #33cccc; COLOR: #ffffff; FONT-WEIGHT: bold">US$ or R</TD><TD style="BACKGROUND-COLOR: #33cccc; COLOR: #ffffff; FONT-WEIGHT: bold"></TD><TD style="BACKGROUND-COLOR: #33cccc; COLOR: #ffffff; FONT-WEIGHT: bold">Rec</TD><TD style="BACKGROUND-COLOR: #33cccc; COLOR: #ffffff; FONT-WEIGHT: bold">Job No. A</TD><TD style="BACKGROUND-COLOR: #33cccc; COLOR: #ffffff; FONT-WEIGHT: bold">Job A US$</TD><TD style="BACKGROUND-COLOR: #33cccc; COLOR: #ffffff; FONT-WEIGHT: bold">Job No. B</TD><TD style="BACKGROUND-COLOR: #33cccc; COLOR: #ffffff; FONT-WEIGHT: bold">Job B US$</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">30/04/2011</TD><TD>Dog</TD><TD style="TEXT-ALIGN: right">9-0715</TD><TD style="BACKGROUND-COLOR: #ccccff">R4500</TD><TD style="TEXT-ALIGN: right">1.125</TD><TD></TD><TD style="BACKGROUND-COLOR: #c0c0c0"></TD><TD style="BACKGROUND-COLOR: #c0c0c0"></TD><TD style="BACKGROUND-COLOR: #ccffcc">CASO33</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">1.125</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">5/05/2011</TD><TD>Cat</TD><TD style="TEXT-ALIGN: right">9-0705</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccccff">3</TD><TD style="TEXT-ALIGN: right">3</TD><TD></TD><TD style="BACKGROUND-COLOR: #c0c0c0">CASO32</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">1.5</TD><TD style="BACKGROUND-COLOR: #ccffcc">CASO33</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">1.5</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">5/05/2011</TD><TD>Rat</TD><TD style="TEXT-ALIGN: right">9-0805</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccccff">5</TD><TD style="TEXT-ALIGN: right">5</TD><TD></TD><TD style="BACKGROUND-COLOR: #c0c0c0">CASO32</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">2.5</TD><TD style="BACKGROUND-COLOR: #ccffcc">CASO33</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">2.5</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">6/05/2011</TD><TD>Cat</TD><TD style="TEXT-ALIGN: right">9-0705</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccccff">2.4</TD><TD style="TEXT-ALIGN: right">2.4</TD><TD></TD><TD style="BACKGROUND-COLOR: #c0c0c0">CASO32</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">1.2</TD><TD style="BACKGROUND-COLOR: #ccffcc">CASO33</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">1.2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">6/05/2011</TD><TD>Dog</TD><TD style="TEXT-ALIGN: right">9-0715</TD><TD style="BACKGROUND-COLOR: #ccccff">R2000</TD><TD style="TEXT-ALIGN: right">0.5</TD><TD></TD><TD style="BACKGROUND-COLOR: #c0c0c0">CASO32</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">0.25</TD><TD style="BACKGROUND-COLOR: #ccffcc">CASO33</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">0.25</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">6/05/2011</TD><TD>Pig</TD><TD style="TEXT-ALIGN: right">9-010</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccccff">4</TD><TD style="TEXT-ALIGN: right">4</TD><TD></TD><TD style="BACKGROUND-COLOR: #c0c0c0">CASO32</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">4</TD><TD style="BACKGROUND-COLOR: #ccffcc"></TD><TD style="BACKGROUND-COLOR: #ccffcc"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">6/05/2011</TD><TD>Dog</TD><TD style="TEXT-ALIGN: right">9-0715</TD><TD style="BACKGROUND-COLOR: #ccccff">R4000</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD style="BACKGROUND-COLOR: #c0c0c0">CASO32</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">0.5</TD><TD style="BACKGROUND-COLOR: #ccffcc">CASO33</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">0.5</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">6/05/2011</TD><TD>Cat</TD><TD style="TEXT-ALIGN: right">9-0705</TD><TD style="BACKGROUND-COLOR: #ccccff">R43000</TD><TD style="TEXT-ALIGN: right">10.75</TD><TD></TD><TD style="BACKGROUND-COLOR: #c0c0c0">CASO32</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">5.375</TD><TD style="BACKGROUND-COLOR: #ccffcc">CASO33</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">5.375</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right">6/05/2011</TD><TD>Cat</TD><TD style="TEXT-ALIGN: right">9-0705</TD><TD style="BACKGROUND-COLOR: #ccccff">R30000</TD><TD style="TEXT-ALIGN: right">7.5</TD><TD></TD><TD style="BACKGROUND-COLOR: #c0c0c0">CASO32</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">3.75</TD><TD style="BACKGROUND-COLOR: #ccffcc">CASO33</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">3.75</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right">7/05/2011</TD><TD>Rat</TD><TD style="TEXT-ALIGN: right">9-0805</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccccff">10</TD><TD style="TEXT-ALIGN: right">10</TD><TD></TD><TD style="BACKGROUND-COLOR: #c0c0c0">CASO32</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">5</TD><TD style="BACKGROUND-COLOR: #ccffcc">CASO33</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">5</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4



Correct Result.

Sheet2

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri, Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 199px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #33cccc; COLOR: #ffffff; FONT-WEIGHT: bold" rowSpan=2>Date Entered</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #33cccc; COLOR: #ffffff; FONT-WEIGHT: bold" rowSpan=2>Account Name/Description</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #33cccc; COLOR: #ffffff; FONT-WEIGHT: bold" rowSpan=2>GL/AC No.</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #33cccc; COLOR: #ffffff; FONT-WEIGHT: bold" rowSpan=2>Job No.</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #33cccc; COLOR: #ffffff; FONT-WEIGHT: bold" colSpan=2>Amount</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #33cccc; COLOR: #ffffff; FONT-WEIGHT: bold">Debit</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #33cccc; COLOR: #ffffff; FONT-WEIGHT: bold">Credit</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">6</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD></TD><TD style="FONT-SIZE: 10pt">Cat</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccccff; FONT-SIZE: 10pt">9-0705</TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-SIZE: 10pt">CASO32</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffcc; FONT-SIZE: 10pt">$11.83</TD><TD style="BACKGROUND-COLOR: #ccffcc; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD></TD><TD style="FONT-SIZE: 10pt">Cat</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccccff; FONT-SIZE: 10pt">9-0705</TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-SIZE: 10pt">CASO33</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffcc; FONT-SIZE: 10pt">$11.83</TD><TD style="BACKGROUND-COLOR: #ccffcc; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD></TD><TD style="FONT-SIZE: 10pt">Dog</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccccff; FONT-SIZE: 10pt">9-0715</TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-SIZE: 10pt">CASO32</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffcc; FONT-SIZE: 10pt">$0.75</TD><TD style="BACKGROUND-COLOR: #ccffcc; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD></TD><TD style="FONT-SIZE: 10pt">Dog</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccccff; FONT-SIZE: 10pt">9-0715</TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-SIZE: 10pt">CASO33</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffcc; FONT-SIZE: 10pt">$1.88</TD><TD style="BACKGROUND-COLOR: #ccffcc; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD></TD><TD style="FONT-SIZE: 10pt">Rat</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccccff; FONT-SIZE: 10pt">9-0805</TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-SIZE: 10pt">CASO32</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffcc; FONT-SIZE: 10pt">$7.50</TD><TD style="BACKGROUND-COLOR: #ccffcc; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD></TD><TD style="FONT-SIZE: 10pt">Rat</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccccff; FONT-SIZE: 10pt">9-0805</TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-SIZE: 10pt">CASO33</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffcc; FONT-SIZE: 10pt">$7.50</TD><TD style="BACKGROUND-COLOR: #ccffcc; FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD></TD><TD style="FONT-SIZE: 10pt">Pig</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccccff; FONT-SIZE: 10pt">9-0710</TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-SIZE: 10pt">CASO32</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffcc; FONT-SIZE: 10pt">$4.00</TD><TD style="BACKGROUND-COLOR: #ccffcc"></TD></TR></TBODY></TABLE>

 

[Excel_Assis]

Hi AlphaFrog here is the first few lines of the production sheets.

This is how I modified the formula to work with this workbook.

=IF(B13="","",SUMPRODUCT(--('Claim Form'!B$10:B$50=B13),--('Claim Form'!C$10:C$50=C13),(('Claim Form'!G$10:G$50=D13)*'Claim Form'!H$10:H$50)+(('Claim Form'!I$10:I$50=D13)*'Claim Form'!J$10:J$50)))

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
9	Date	Item	Acc No	US$ or R		Rec	Job No. A	Job A US$	Job No. B	Job B US$
10	30-Apr-11	Camp Food & Water	9-0715	R4500	$1.13		CASO32	$0.56	CASO33	$0.56
11	05-May-11	Accommodation & Meals	9-0705	$3.00	$3.00		CASO32	$1.50	CASO33	$1.50
12	05-May-11	Communications	9-0805	$5.00	$5.00		CASO32	$2.50	CASO33	$2.50
13	06-May-11	Accommodation & Meals	9-0705	$2.40	$2.40		CASO32	$1.20	CASO33	$1.20
14	06-May-11	Camp Food & Water	9-0715	R2000	$0.50		CASO32	$0.25	CASO33	$0.25
15	06-May-11	Camp Food & Water	9-0715	R4000	$1.00		CASO32	$0.50	CASO33	$0.50
16	06-May-11	Accommodation & Meals	9-0705	R43000	$10.75		CASO32	$5.38	CASO33	$5.38
17	06-May-11	Accommodation & Meals	9-0705	R30000	$7.50		CASO32	$3.75	CASO33	$3.75
18	06-May-11	Fuel	9-0761	R43000	$10.75				CASO33	$10.75
Claim Form

I have only placed the formula at the moment in E13. As you can see the result is a #value! error.

Excel Workbook
	A	B	C	D	E	F
10	Date Entered	Account Name/Description	GL/AC No.	Job No.	Amount
11					Debit	Credit
12	1	2	3	4	5	6
13		Accommodation & Meals	9-0705	CASO32	#VALUE!
14		Accommodation & Meals	9-0705	CASO33	$24.26
15		Camp Food & Water	9-0715	CASO32	$3.23
16		Camp Food & Water	9-0715	CASO33	$3.23
17		Communication Meals/Receiption	9-0710	CASO32	$22.38
18		Communication Meals/Receiption	9-0710	CASO33	$22.38
Cash Payment Voucher

 

[AlphaFrog]

Your updated formula works for me. I got $11.83 as a result in E13.

You can "step" through a formula to find where it gives you an error.

• Select cell E13.
• Select from the menu; Tools\ Formula Auditing\ Evaluate Formula
• Click the Evaluate button to step trough the formula

 

[Excel_Assis]

Hi you must be one of the very few without Skype.

I have stepped through and I know where the problem is but do not know why. How can I take a shot of the result in the Evaluate Formula window and send you.

 

[Excel_Assis]

I believe the error occures because there is the below formula in the blank cells in columns H and J on sheet Clain Form (sheet1)

=IF(G18="","",$E18/MAX(1,COUNTA($G18,$I18)))

Any ideas.

 

[AlphaFrog]

Try this in E13

=IF(B13="","",SUMPRODUCT(--('Claim Form'!B$10:B$50=B13),--('Claim Form'!C$10:C$50=C13),--('Claim Form'!G$10:G$50=D13),'Claim Form'!H$10:H$50)+SUMPRODUCT(--('Claim Form'!B$10:B$50=B13),--('Claim Form'!C$10:C$50=C13),--('Claim Form'!I$10:I$50=D13),'Claim Form'!J$10:J$50))

 

[Excel_Assis]

Hi AlphaFrog thank you so much it appears to work properly now.

Would you mind explaining to me the difference between the two fofmulas so I understand why one works and the other does not.

 

[AlphaFrog]

The original formula was trying to multiply text "". That caused the error.
...('Claim Form'!G$10:G$50=D13)*'Claim Form'!H$10:H$50)

If your Claim Form formulas returned zeros instead of "", then it should have worked; e.g.
=IF(G18="",0,$E18/MAX(1,COUNTA($G18,$I18)))