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	Column B	Column C
1	547.25
2	848
3	84.5
Sub-Service -dig03	1_(sum three above)
4	98.5
5	41.5
6	78.5
Sub-Service -dig04	2_(sum three above)
7	99.2
Sub-Service -dig05	3_(sum one above)
8	78.8
Service-dig01	4_(sum one above)
Function -Child R&b	SUM All sub-service and service totals

<tbody>
</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