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>

 

[AlphaFrog]

Try something like this in Sheet2 cell E4. This is not a Ctrl+Shift+Enter formula.

=IF(B4="","",
    SUMPRODUCT(--(Sheet1!B$2:B$11=B4),
               --(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)))

SUMPRODUCT function

 

[Excel_Assis]

Hi AlphaFrog thank you for the reply however when I place the formula in the cell all I see is the formula in the cell not a result.

Any ideas as to what I am doing wrong.

 

[AlphaFrog]

Do you have a space in front of the equal sign at the beginning of the formula?

 

[Excel_Assis]

No , no space

 

[AlphaFrog]

I'm not sure what the problem is. The formula works for me. I tested it with your data.

Here it is again all on one line.

=IF(B4="","",SUMPRODUCT(--(Sheet1!B$2:B$11=B4),--(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)))

 

[Excel_Assis]

Hi AlphaFrog thank you, when you gave it to me in one line it works.

Really strange as I have compared the two and that look identical

 

[AlphaFrog]

You're welcome. Glad it finally worked.

 

[Excel_Assis]

AlphaFrog when i move that working formula across to the real workbook I get a #value! error in column E or sheet2.

Will that formula work if it is looking at cells with formulas in them?

 

[AlphaFrog]

It should work with cells that have formulas in them as long as the formulas don't return errors. The formulas can return "". That would be OK.

What version of excel do you have? You could use SUMIFS in stead of SUMPRODUCT if you have Excel 2007 or later.