If Statement/Array Problem

[LJC]

Hi

Can anyone help.

I have three columns

A B C
Category Name Week 1 Week 2

xxxxx 500.00 43.00
xxxxx 438.00
xxxxx 4,354.00 1,468.00

What I would like to do is find a formula that can identify the following:
Subtract Column B from Column C , then to sum the entire results not including the empty cells in the calculation. ( If that makes sense)

For the example above the answer should be: 343.00

Thanks in advance

 

[Expiry]

I don't understand how you're getting 343. The difference on your 3rd example is c2,000.

Also, do you mean b-c or c-b?

 

[LJC]

Sorry the information should read:

Column B heading = Week 1

With the figures as follows:

Row 1 = 500
Row 2 = Empty Cell
Row 3 = 4,354.00

Column C Heading = Week 2

With the figures:

Row 1 = 43.00

Row 2 = 438.00

Row 3 = 1,468.00

Hope that makes more sense

 

[MrKowz]

LJC, can you please provide a step-by-step explanation as to how you achieved 343.00? Following the logic posted, I came up with -2905.

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Category Name</td><td style=";">Week 1</td><td style=";">Week 2</td><td style=";">Difference</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">xxxx</td><td style="text-align: right;;">500</td><td style="text-align: right;;">43</td><td style="text-align: right;;">-457</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">xxxx</td><td style="text-align: right;;"></td><td style="text-align: right;;">438</td><td style="text-align: right;;">438</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">xxxx</td><td style="text-align: right;;">4354</td><td style="text-align: right;;">1468</td><td style="text-align: right;;">-2886</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Sum</td><td style="text-align: right;;">-2905</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=C2-B2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D7</th><td style="text-align:left">=SUM(<font color="Blue">D2:D4</font>)</td></tr></tbody></table></td></tr></table><br />

 

[barry houdini]

If you want to ignore rows with blanks

=SUMPRODUCT(ISNUMBER(B2:B10)*ISNUMBER(C2:C10),B2:B10-C2:C10)

which gives 3343 for your example, I think

 

[LJC]

sorry looks like i have totally confused everyone

to achieve 343 i did the following

=IF(B2="","",B2-D2)

then sum the 3 resulting answers

 

[barry houdini]

Well my suggestion almost does the equivalent of that in one go....revised for the specific ranges

=SUMPRODUCT(ISNUMBER(B2:B4)+0,B2:B4-D2:D4)

 

[LJC]

Brilliant Barry

That WORKED thanks soooooo much