CODEWITHSUNDEEP
excelexcel-formulaspreadsheet
Syntactic Fructose
Syntactic Fructose

Reputation: 20076

How to sum up a column of data that meets a condition

How can I do a simple summation of data that meets a certain condition? Say i have the data

1 2 3 4
5 6 -7 8

for finding the average I can just type in a cell =AVERAGE(A1:B4), but how can I find the average of only the positive numbers into a block of data?

I realize the IF statement would probably be used here, but how? Thanks for any help

Upvotes: 1

Views: 37

Answers (2)

Rob Wise
Rob Wise

Reputation: 5120

You can use the AVERAGEIF function of Excel. The documentation is provided here. If your data was in cells A1:D2, the following function would only average the data that was above zero:

=AVERAGEIF(A1:D2,">0")

If you want to use multiple criteria, then you can use the AVERAGEIFS function, which is similar but allows multiple conditions. The documentation is provided here. The only catch is you have to define the range to average, which in your case could just be the same range. If your data was in cells A1:D2, the following function would only average the data that was both above zero and less than or equal to 6:

=AVERAGEIFS(A1:D2,A1:D2,">0","<=6")

Upvotes: 1

user4341206
user4341206

Reputation: 647

Try: =AVERAGEIF(A1:B4;">0";A1:B4)

Works in excel 2013.

Upvotes: 1

Related Questions