If Statement/Array Problem

LJC

New Member
Joined
Nov 25, 2008
Messages
25
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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?
 
Upvote 0
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
 
Upvote 0
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 />
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top