CODEWITHSUNDEEP
excelstringnumbersstring-matching
pippo
pippo

Reputation: 824

Matching, splitting, converting and summing string in Excel / Numbers

I'm trying to do a match-and-calculate formula in Excel (or in Numbers for Mac, is the same for me: I try them both as they seem equal, also function names are equal!).

This is what I have:

|     1     |     2     |     3     |
|-----------+-----------+-----------|
|  Category |other stuff| duration  |
|-----------+-----------+-----------|
|    A      + ....  ... + 00:01:23  |
|-----------+-----------+-----------|
|    A      + ....  ... + 00:30:19  |
|-----------+-----------+-----------|
|    B      + ......... + ......... |
|-----------+-----------+-----------|
|    A      + .... ...  + 00:22:12  |
...          ...           ....

So, in column 3 I have a duration in time in this format "hh:mm:ss" and in column 1 are stored all of my categories.

I want to search for all rows in my table that are matching with the category "A" in column 1 and take the relative column 3, splitting the string and converting chars to numbers (in particular I'm interested in converting them to secs, so hh*3600+mm*60+ss) and finally sum up all these values. Is it possible?

I'm new with Excel and Numbers, but I'm pretty familiar with coding in programming languages generally: this is what I'd do in programming:

global_secs=0;
for(row r=top to end){
   if(r.get_column(1).content_equals("A")){
      cell c=r.get_column(3);
      string=split(c.get_content(),":")
      global_secs+=int(string[1])*3600+int(string[2])*60+int(string[3])
   }
}

Is there a way to achieve this in Excel sheet (or Numbers)? I'd like to do all of this in one, or more, formula only in Excel or Numbers.

One more thing: I do not want to change cells format because this should be an automatic process without human interaction, so unless there is a function to change a range of cells format dynamically I prefer not to do that (I know I can make "duration" as format and sum up without converting to integer, but originally my data is in hh:mm:ss format)

Thanks so much!

Upvotes: 0

Views: 74

Answers (2)

Karsten W.
Karsten W.

Reputation: 18490

Looks like a matrix formula

=SUM(N($A$2:$A$8="A")*$B$2:$B$8)

where column A contains the category and column C the duration. Note you need to press ctrl shift enter to make it work.

To convert the result to seconds, an alternative approach to @Mrig' solution would be to format the result and convert it back to a number, i.e.

=VALUE(TEXT(SUM(N($A$2:$A$8="A")*$B$2:$B$8),"[ss]"))

Upvotes: 0

Mrig
Mrig

Reputation: 11702

The formula you are looking for is

=SUMIF(A2:A5,"A",C2:C5)

The easiest way to get the result in seconds would have been to format the cell as [ss] in Custom category. But as you don't want to do formatting , the other way could be

=HOUR(result) * 3600 + MINUTE(result) * 60 + SECOND(result)

So formula becomes

=HOUR(SUMIF(A2:A5,"A",C2:C5)) * 3600 + MINUTE(SUMIF(A2:A5,"A",C2:C5)) * 60 + SECOND(SUMIF(A2:A5,"A",C2:C5))

See image for referecne

enter image description here

Upvotes: 1

Related Questions