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

[ExcelRoy]

Hi All,

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

Thanks

 

[Jonmo1]

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"}))

 

[ExcelRoy]

Perfect Jonmo1,

Thanks

 

[Jonmo1]

Glad to help, thanks for the feedback.

 

[farmerscott]

ADVERTISEMENT

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]

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]

ADVERTISEMENT

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]

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]

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]

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