# If Statement/Array Problem

#### LJC

##### New Member
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

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### Expiry

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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.

#### barry houdini

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

##### New Member
Brilliant Barry

That WORKED thanks soooooo much

Replies
1
Views
480
Replies
10
Views
546
Replies
0
Views
269
Replies
3
Views
324
Replies
4
Views
148

1,190,856
Messages
5,983,242
Members
439,833
Latest member
CDaviess

### 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?

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