I need to calculate a column having many cells but I want to not calculate particular cells on condition. For example:
我需要计算一个有很多单元格的列,但我想不计算条件下的特定单元格。例如:
Scenario:
Sr No Marks
1 46
2 33
3 44
4 32
5 11
6 99
7 27
8 98
I want to get the sum of marks but only those cells should be added whom marks are more than 50. What formula should use?
我想得到标记的总和,但只有那些标记超过50的细胞应该添加。应该使用哪种公式?
1 个解决方案
#1
3
We can use SUMIF here:
我们可以在这里使用SUMIF:
=SUMIF(B2:B9, ">50")
This assumes that your Marks column is in column B, and that the first data point is on the second row.
这假定您的Marks列在B列中,并且第一个数据点在第二行。
Edit:
If you want to take the average value of marks, where the mark is over 50, then we can also use COUNTIF:
如果你想取标记的平均值,标记超过50,那么我们也可以使用COUNTIF:
=SUMIF(B2:B9, ">50") / COUNTIF(B2:B9, ">50")
#1
3
We can use SUMIF here:
我们可以在这里使用SUMIF:
=SUMIF(B2:B9, ">50")
This assumes that your Marks column is in column B, and that the first data point is on the second row.
这假定您的Marks列在B列中,并且第一个数据点在第二行。
Edit:
If you want to take the average value of marks, where the mark is over 50, then we can also use COUNTIF:
如果你想取标记的平均值,标记超过50,那么我们也可以使用COUNTIF:
=SUMIF(B2:B9, ">50") / COUNTIF(B2:B9, ">50")