Sum of cells containing values and text within the same cell? Hi, I\'m looking f
ID: 3562493 • Letter: S
Question
Sum of cells containing values and text within the same cell?
Hi, I'm looking for a way I can sum something up where I have cells that contain both values and text within the same cell. Here examples for two data ranges:
(1) I want a sum function that will only sum the numerators of each cell. Additionally, is there a way where I can set a limit to the value in a cell in the sum function? For this example, if I set a limit at 5, I want it to sum all of the numerators, but instead of counting 6 in the cell with 6/8, it will only count that data point as being 5? That is, 1+3+5+4+1+4?
Column A
1/5
3/15
6/8
4/8
1/15
4/5
(2) I want a function that will sum the values in a range of cells that contain functions, possibly more than one, as well as text. In this example, I want to sum only the numerator functions.
Column A
=countif(B1:B10,"Yes")&"/"&counta(B1:B10)
=countif(C1:C10,"Yes")&"/"&counta(C1:C10)
=countif(D1:D10,"Yes")&"/"&counta(D1:D10)
=countif(E1:E10,"Yes")&"/"&counta(E1:E10)
=countif(F1:F10,"Yes")&"/"&counta(F1:F10)
=countif(G1:G10,"Yes")&"/"&counta(G1:G10)
If my examples are unclear, please let me know and I will try to make them more clear.
Explanation / Answer
In your examples is the column A referenced in each one the same column A?
In other words, in (1) do you get 1/5 through a formula like the
=countif(B1:B10,"Yes")&"/"&counta(B1:B10)
formulas shown in (2)?
If that is the case, for (2) you can sum the numerators easily with a formula like:
=MIN(5,COUNTIF(B1:B10,"Yes") + MIN(5,COUNTIF(C1:C10,"Yes") +... etc.
Or put just one part of the formula on each row and SUM() that column at the end:
A H
=countif(B1:B10,"Yes")&"/"&counta(B1:B10) =MIN(5,COUNTIF(B1:B10,"Yes")
=countif(C1:C10,"Yes")&"/"&counta(C1:C10) =MIN(5,COUNTIF(C1:C10,"Yes")
.... ....
SUM(H1:H#)
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.