Dynamic sum based on cell text.

[glynn1969]

Hello - any help much apprciated.

I have managed to sort a large (varying) data set as below but need a formuale to sum up the values returned, however the number of returned vlaues will not always be the same.

For example below i have 4 areas to sum with 3,3,1,1 variables however next time i could have 2,2,4,2 variable in each section.

Is there a formulae i can enterin column C that can dynamically sum the values in each section. So looking in column A and summing each sub-service but also when it sees the word service then i sums the value associated to the service section but also all the above sub-service totals.

Might be too much of an ask i think.

Column A

<tbody>
[TD="width: 161"]Column B[/TD]
[TD="width: 112"]Column C[/TD]

[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]547.25[/TD]

[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]848[/TD]

[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]84.5[/TD]

[TD="class: xl66"]Sub-Service -dig03[/TD]
[TD="class: xl67"]1_(sum three above)[/TD]

[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl65, align: right"]98.5[/TD]

[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]41.5[/TD]

[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl65, align: right"]78.5[/TD]

[TD="class: xl66"]Sub-Service -dig04[/TD]
[TD="class: xl67"]2_(sum three above)[/TD]

[TD="class: xl65, align: right"]7[/TD]
[TD="class: xl65, align: right"]99.2[/TD]

[TD="class: xl66"]Sub-Service -dig05[/TD]
[TD="class: xl67"]3_(sum one above)[/TD]

[TD="class: xl65, align: right"]8[/TD]
[TD="class: xl65, align: right"]78.8[/TD]

[TD="class: xl66"]Service-dig01[/TD]
[TD="class: xl67"]4_(sum one above)[/TD]

[TD="class: xl66"]Function -Child R&b[/TD]
[TD="class: xl66"]SUM All sub-service and service totals[/TD]

</tbody>

 

[glynn1969]

I worked out a solution "=IFERROR(IF(SEARCH("*sERV*",K3,1)=1,(SUM($L2:L$3)-SUM($M2:M$3))),0)+IFERROR(IF(SEARCH("*fUNC*",K3,1)=1,(SUM($L2:L$3))),0)" but if anyone knows any better would be appreciated