Formula for subtracting and adding timecode then collating results using SUMIF

**paddkell** · 07-29-2021, 11:12 AM

This is a supplementary question to a thread which was marked as solved a few years ago. Back then the poster was after help in adding a column of video timecodes hh:mm:ss:ff where ff is frames per second and that value could vary

I'm not allowed to post links because I'm new or I would link to the thread

Back then DonkeyOte came through big time and provided an excel sheet attached which does exactly what was required .

I wonder if anyone can help me take it one stage further? I need to do something similar with timecodes but then also separate out the values based on the source of that video clip. I'll do this by adding a clip source code column next to each timecode value. What I'd like to do is have a seperate sheet with the clip source codes in order. And then have excel scan the column with the timecodes and return a sum of the values associated with that source code

I've done this before with currency values in a budget to separate out cost per department code using a SUMIF across the columns =SUMIF(A1:A1285,B5,J1:J1285) Where A is the column with the department codes, B5 is the department code and J is the column with the costs

But that doesn't work with timecodes because excel doesn't understand the ff

How do I use a similar SUMIF formula and have it understand that the values are hh:mm:ss:ff with a variable value for ff (in my case 23.98)

Many thanks
(I'm using the latest version of excel)

**AliGW** · 07-29-2021, 11:16 AM

Welcome to the forum.

You can get round the link issue by posting the URL with a couple of spaces in it - please do that.

There are instructions at the top of the page explaining how to attach your sample workbook.

Your profile says 16.51 - is this the latest version for Mac? If not, what is it? your profile isn't clear.

**paddkell** · 07-29-2021, 11:28 AM

Hi Ali

Yes I'm on the latest version for Mac. Thanks for the tip on attaching the worksheet, I'm hoping it's now attached. This is the doc DonkeOte provided back in 2011. I've added column D as an example of how the clip sources might fall.

I'd like to be able to total up the duration of clips from each code in a separate sheet if possible

Thanks for your help

**JeteMc** · 07-31-2021, 12:08 PM

It may help us to better understand if you could tell us what the expected values for say clip source codes 1, 2 and 3 should be so that we have something with which to compare the outputs of our proposed formula/code.