Conditionally sum fractional cells based on column headings

[kdrymer]

Hi there, I am looking for an Excel formula that will will sum fractional values (test scores) in cells based on whether the column headings in the range equals the heading in the summation column. I need to sum the numerator and denominator separately (i.e. 5/6 + 2/2 = 7/8)

Below is an example:

So in Cell M6, I need a formula that will sum the fractional values (numerator/denominator separately as shown in above example) in the range G6:L6 conditionally based on whether the column headings for the same range (G5:L5) match the value given in cell M5. So for this example, the formula in M6 would evaluate that in M5 we have a value "OA.2" and that value appears in the range (G5:L5) in cells G5 and J5, so we would sum the values 1/3 and 2/5 together to get 3/8

I also need the values in the cells to be shown as fractions, i.e. 4/4 stays as 4/4 (but can still be used for calculation purposes). I've found that Excel was trying to convert this value be 1, unless I formatted as Text in which I don't believe I can then do calculations on it. Any help is appreciated!

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Conditionally sum fractional cells based on column headings
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Scott Huish]

What version of Excel do you have? I agree with the response in the other post that that is not how fractions work. Is this a special requirement of yours and not true fractions because 5/6 + 2/2 is not 7/8. It's 1-5/6

 

[kdrymer]

Hi, I have Office Version 2021. Yes I realize it is not a true summation of the fraction, but more the individual parts of it...

 

[Fluff]

I have Office Version 2021

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Cubist]

Try:

Book1
	G	H	I	J	K	L	M	N
5	OA.2	OA.3	OA.3	OA.2	OA.3	OA.4	OA.2	OA.3
6	1/3	5/6	4/4	2/5	2/3	3/8	3/8	11/13
Sheet3

Cell Formulas
Range	Formula
M6:N6	M6	=LET(f,FILTER($G$6:$L$6,$G$5:$L$5=M5),s,SEARCH("/",f),num,SUM(--LEFT(f,s-1)),den,SUM(--MID(f,s+1,LEN(f))),num&"/"&den)