# Simple Addition to ignore #Value & #Div/0!

#### ExcelRoy

##### Well-known Member
Hi All,

I am looking for a simple addition formula to calculate cells G5:G1004 but ignoring the #Value & #Div/0!

Thanks

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Jonmo1

##### MrExcel MVP
If there are no negative numbers, then you can use

=SUMIF(G5:G1004,">0")

If there are negatives, then
=SUM(SUMIF(G5:G1004,{"<0",">0"}))

Perfect Jonmo1,

Thanks

#### Jonmo1

##### MrExcel MVP
Glad to help, thanks for the feedback.

#### farmerscott

##### Well-known Member
Hi Jonmo,

I am just trying to learn something from someone more knowledgeable than myself.....

1. why the need for the {} to take the criteria for the sumif and not just ()?
2. why do you need to sum the sumif?

thanks

FarmerScott
2.

#### barry houdini

##### MrExcel MVP
If you are using Excel 2010 or later you can use AGGREGATE function like this

=AGGREGATE(9,6,G5:G1004)

The 9 indicates "SUM" and the 6 indicates that errors should be ignored

#### Jonmo1

##### MrExcel MVP
The combination of the {} and the SUM makes it process like an array.
It basically creates 2 simif formulas, 1 for <=0, another for >=0
The SUM sums the result of the 2.

#### farmerscott

##### Well-known Member
Hi Jonmo,

thanks for that.

Does sumif only take 1 criteria? Does your fcombination of formula/s then allow you to do 2 or more? Is that what you were thinking?

i need to get a better handle on arrays than I do.

can you think of any other formulas that you could use in the same context?

cheers

FarmerScott

#### Jonmo1

##### MrExcel MVP
Yes, And Yes.

Sumif only takes 1 criteria.
This formula syntax allows multiple criteria
But it's not as flexible as you might be hoping.
It can only do an OR type of criteria, and on the same column of data.

So basically you can do If ColA = "A" or "B" or "C" etc..
But you can't do
If ColA = "A" and/or ColB = "A"

#### Jonmo1

##### MrExcel MVP
can you think of any other formulas that you could use in the same context?

cheers
FarmerScott

Yes, you can do countif (same as sumif)
And you can do vlookup...this is actually pretty cool

=SUM(VLOOKUP(A1,B:D,{2,3},FALSE))
However, this must be array entered with CTRL+SHIFT+ENTER

Replies
7
Views
302
Replies
13
Views
404
Replies
4
Views
406
Replies
5
Views
76
Replies
0
Views
267

1,195,848
Messages
6,011,946
Members
441,657
Latest member
Diupsy

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

### Which adblocker are you using?

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

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