CODEWITHSUNDEEP
excelexcel-formula
john
john

Reputation: 51

when using the sumif function how do you get the [sum_range] to only add the positive numbers?

when using the sumif function how do you get the [sum_range] to only add the positive numbers?

so if the function is =sumif(A:H,C34,H:H) how do I only add the positive numbers in column H

Upvotes: 1

Views: 1041

Answers (2)

chris neilsen
chris neilsen

Reputation: 53166

In your posted formula =sumif(A:H,C34,H:H) because the test range is A:H , the range that is summed is actually H:O (the shape of A:H = 8 columns starting at top left cellof H:H) Not sure if this is what you intended. Given the overlap in the criterai and sum ranges I suspect not. BTW this means cell J34 will always be included in the sum

SUMIFS is only available in Exel 2007 and later and would work for =SUMIFS(H:H, A:A, C34, H:H, ">0")

Unlike the range and criteria arguments in the SUMIF function, in the SUMIFS function, each criteria_range argument must contain the same number of rows and columns as the sum_range argument.

If you are using Excel 2003 or earlier, or if the range behviour described above is required, you can use somthing like, 

=SUM(H:H*(A:A=$C$34)*(H:H>0))

or

=SUM(H:O*(A:H=$C$34)*(H:O>0))   ' this one won't work as is, you will have to resolve the overlapping ranges

entered as an array formula (Ctrl-Shift-Enter)

Upvotes: 1

Gerrat
Gerrat

Reputation: 29740

You can use the SUMIFS function:

=SUMIFS(H:H, A:H, C34, H:H, ">0")

The sum_range is at the beginning in this case, instead of the end in this case

Upvotes: 0

Related Questions