Run excel function over multiple rows and columns and get AGGREGATE without the need for additional columns

Question

If I have this data set, for example:

Based on a comment, here's that same example data in CSV format that should properly import into Excel:

Compute (),Serverless (),Compute Containers (),Databases (),Total ()
EC2 --- 9,Lambda --- 1,"App Mesh --- 0
ECS --- 3
EKS --- 2","DocumentDB --- 0
DynamoDB --- 5
ElastiCache --- 0
RDS --- 2
Redshift --- 3",Total --- 
EC2 --- 7,Lambda --- 2,"App Mesh --- 0
ECS --- 0
EKS --- 1","DocumentDB --- 0
DynamoDB --- 5
ElastiCache --- 0
RDS --- 13
Redshift --- 1",Total --- 

I'd like to be able to do two things:

1. I'd like to be able to sum all numbers contained within cells across a range of columns (A through D) and output that total on column E.

2. I'm also looking to then use that same sum all numbers contained within cells but across the rows of each column, rather than the column, and supply a total count for all rows within that column in the header of the column.

I've been able to do #2 with some success, but it requires writing additional columns for each source column in order to do the magic to first get the combined total for cell for each row...store it in that new column...then in the header I simply sum the range of that new column.

For examples, summing up column A, I can use/create column F for storing that total:

=SUM(IFERROR(--MID(TRIM(MID(SUBSTITUTE(A2,CHAR(10),REPT(" ",99)),(ROW($XFD$1:INDEX(XFD:XFD,LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(10),""))+1))-1)*99+1,99)),FIND("---",TRIM(MID(SUBSTITUTE(A2,CHAR(10),REPT(" ",99)),(ROW($XFD$1:INDEX(XFD:XFD,LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(10),""))+1))-1)*99+1,99)))+3,99),0))

And then A1 becomes ="Compute (" & SUBTOTAL(109,F2:F3) & ")" which is nice because it does work dynamically for both removing/hiding rows with filters and when hidden.

That monster allows me to nicely sum the total of all numbers that appear no matter line breaks or empty...but I'd very much prefer to skip the need for that additional column but have not found a way to make this into something that works that way.

Desired result would be:

[EDIT: I wasn't clear that "sum" is not exactly what's needed as it needs to also change when content is hidden/filtered] ...and dynamically change for hidden/filtered content (ie: SUBTOTAL or, ideally, AGGREGATE).

Answer 1

1] In E2, formula copied down :

="TOTAL --- "&SUMPRODUCT(--TEXT(MID(SUBSTITUTE(SUBSTITUTE(CONCAT(A2:D2&" "),CHAR(10)," ")," ",REPT(" ",99)),ROW($1:$50)*99-98,99),"0;;0;\0"))

2] In A1, formula copied across right :

="Compute ("&SUMPRODUCT(--TEXT(MID(SUBSTITUTE(SUBSTITUTE(CONCAT(A2:A3&" "),CHAR(10)," ")," ",REPT(" ",99)),ROW($1:$50)*99-98,99),"0;;0;\0"))&")"

Then ,

In B1:E1, change the header name inside the formula into "Serverless", "Compute Containers", "Databases" and "TOTAL" in accordingly.

Answer 2

Please note, my solution only works if the number at the end of each text string is a positive number between 0 and 9999999999 (10 digits) inclusive. Some rework is required if you require a solution that works for negative values and/or more than 10 digits.

My solution also requires the use of a supporting row to store the desired column headers. Suppose this is in range A1:E1 as shown below:

Suppose the sample data is in the range A3:E5 (I added one row for testing purpose). You want to return the sum of rows in range A2:E2 and return the sum of columns in range E3:E5.

In Cell E3, please enter the following formula and drag it down:

="Total --- "&LET(x,SUBSTITUTE(ARRAYTOTEXT(SUBSTITUTE(A3:D3,CHAR(10),REPT(" ",10))),",",REPT(" ",10)),SUM(IFERROR(--SUBSTITUTE(MID(x,UNIQUE(TRANSPOSE(FIND("---",x,SEQUENCE(1,LEN(x),1,1))))+4,10),",",""),0)))

In Cell A2, please enter the following formula and drag it to the right:

=A1&TEXT(LET(x,SUBSTITUTE(ARRAYTOTEXT(SUBSTITUTE(A3:A5,CHAR(10),REPT(" ",10))),",",REPT(" ",10)),SUM(IFERROR(--SUBSTITUTE(MID(x,UNIQUE(TRANSPOSE(FIND("---",x,SEQUENCE(1,LEN(x),1,1))))+4,10),",",""),0)))," (#)")

Some explanations, I first used the ARRAYTOTEXT+SUBSTITUTE function to convert the target range into a text string and replace all line breaks and comma signs with a 10-character length of blanks, then you should have something like below:

EC2 --- 9 Lambda --- 1 App Mesh --- 0 ECS --- 3 EKS --- 2 DocumentDB --- 0 DynamoDB --- 5 ElastiCache --- 0 RDS --- 2 Reshift --- 3

Then I used a combination of SUBSTITUTE+MID+UNIQUE+TRANSPOSE+SEQUENCE to find the location of each number based on the position of --- in the text string, return the numbers into a vertical array, and lastly use the IFERROR+SUM function to add up the numbers.

The LET function allows me to store the ARRAYTOTEXT+SUBSTITUTE function into a name called x, which can be easily referred to in the subsequent calculations. Without using LET, the formula will be extremely long and hard to read.

I would say FILTERXML is a better function to use in this case as it is not limited to the number of digits of each number in the text string, while mine can only work for up to ten digits.

Answer 3

FILTERXML() with TEXTJOIN() may give you desired result.

=SUM(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,SUBSTITUTE(SUBSTITUTE(A2:D2,CHAR(10),"</s><s>")," --- ", "</s><s>"))&"</s></t>","//s[.*0=0]"))

Edit:

If you don't have TEXTJOIN() then can try below formula. FILTERXML() is available on Excel2013 and later. In this case formula will longer as many column you have in every row.

=SUM(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A2,CHAR(10)," --- ")," --- ","</s><s>")&"</s><s>"&SUBSTITUTE(SUBSTITUTE(B2,CHAR(10)," --- ")," --- ","</s><s>")&"</s><s>"&SUBSTITUTE(SUBSTITUTE(C2,CHAR(10)," --- ")," --- ","</s><s>")&"</s><s>"&SUBSTITUTE(SUBSTITUTE(D2,CHAR(10)," --- ")," --- ","</s><s>")&"</s></t>","//s[.*0=0]"))