Sum only positive number is only certain columns

[rfinnegan]

Hi All:

I'd like to sum the numbers in columns A, C, F, G, M, P, and Z, but only if they are positive. I want to ignore numbers in the other columns.

Thanks in advance.

 

[Eric W]

There may be something fancier, but this should work:

=SUMIF(A:A,">0")+SUMIF(C:C,">0")+SUMIF(F:F,">0")+SUMIF(G:G,">0")+SUMIF(M:M,">0")+SUMIF(P:P,">0")+SUMIF(Z:Z,">0")

 

[rfinnegan]

There may be something fancier, but this should work:

=SUMIF(A:A,">0")+SUMIF(C:C,">0")+SUMIF(F:F,">0")+SUMIF(G:G,">0")+SUMIF(M:M,">0")+SUMIF(P:P,">0")+SUMIF(Z:Z,">0")

Perfect. That's the approach I took, but apparently I dorked it up somewhere because mine didn't work.

Thanks a million.

 

[Special-K99]

How did it not work?

Suggest you insert row restrictions or this formula will be slow.

 

[Eric W]

rfinnegan: Glad to help.

How did it not work?

Suggest you insert row restrictions or this formula will be slow.

Functions such as SUMIFS are aware of the maximum row and use it when calculating whole column references, so row restrictions are not required. Array formulas, including SUMPRODUCT, are not aware of the maximum row and should use row restrictions. This is why I used multiple SUMIFS instead of a shorter SUMPRODUCT formula.

See this link:

https://msdn.microsoft.com/en-us/vb...-tips-for-optimizing-performance-obstructions

 

[rfinnegan]

How did it not work?

Suggest you insert row restrictions or this formula will be slow.

I did use restrictions, with a little extra room for larger data sets at the end of the month.

My original formula was giving an answer far larger than the one I calculated manually. I couldn't figure out why the number was so far off - I didn't have hidden rows or columns that I was accidentally picking up. I thought maybe it was giving me absolute value, but that wasn't it either.

I would have had someone here look at it, but sadly I'm the Excel "guru" in my office.

 

[rfinnegan]

Hmmm... Didn't know SUMIF did that. I've always tried to estimate my "row need" in my reports.

Thanks for the input.