How to Sum Time through SumIF

[devonknows]

Hi,

The problem i am facing at the moment is that a report i am using outputs raw data from a program. now i started to create an excel document to pull certain segments of the raw data. It works find up until i try and SUMIFS a time value. Now i know that in order for SUMIF to work u need to set a custom formatting on both the sum total box and the source data but that isnt an option as there will be several hundred different time fields mixed in with texts, percentage etc. P.S. I have also tried VLookup but becuase i need two criteria it wont work.

Now i did try this.

=INDEX('Raw Data'!$H:$H,MATCH(1,('Raw Data'!$A:$A="Total For Agent")*('Raw Data'!$B:$B=ShortID!$D11),0))

Which pulls the data fine but when i pull several rows of data then try and total it by means of SUM it doesnt seem to work.

Anyone got any ideas?

Regards,
Devon

 

[barry houdini]

Hello Devon,

what results do you get when you total the time values, zero, an error or the wrong total?

 

[devonknows]

Hi Barry,

I get a return result of 00:00:00

Just as a test i attempted to set the data that it pulls if from to "Time" as well as [h]:mm:ss but no luck.

Regards

 

[devonknows]

My Apologies, i have forgot to mention that the formula needs CTRL SHIFT ENTER to close

{=IF($B10="","",IF(ISNA(INDEX('Raw Data'!$H:$H,MATCH(1,('Raw Data'!$A:$A="Total For Agent")*('Raw Data'!$B:$B=ShortID!$D5),0))),"00:00:00",INDEX('Raw Data'!$H:$H,MATCH(1,('Raw Data'!$A:$A="Total For Agent")*('Raw Data'!$B:$B=ShortID!$D5),0))))}

 

[barry houdini]

Sounds like your time values might be text-formatted - if you sum text you get an answer of zero

What do you get if you change the formula like this

=IF($B10="","",IF(ISNA(INDEX('Raw Data'!$H:$H,MATCH(1,('Raw Data'!$A:$A="Total For Agent")*('Raw Data'!$B:$B=ShortID!$D5),0))),0,INDEX('Raw Data'!$H:$H,MATCH(1,('Raw Data'!$A:$A="Total For Agent")*('Raw Data'!$B:$B=ShortID!$D5),0))+0))

 

[devonknows]

Ah i see,

Thank you works a great bunch that i see by adding the +0 it changes it to non-text allowing the SUM to do its job.

Spot on mate, thank you very much.

Regards

 

[devonknows]

Hi,

When back at work today i have tested this formula with real time data, due to the vast amount of data any time any change is made the program lags is not practical due to the formula.

Is there any other way to SUM time, i have attempted to use SumIF and use the +0 technique used but still returns 00:00:00

=IF($B10="","",SUM(SUMIFS('Raw Data'!$H:$H,'Raw Data'!$B:$B,"=" & ShortID!$D5, 'Raw Data'!$A:$A,"=Total For Agent")+0))

The Technique being used was: (Formula from cell below)
=IF($B11="","",IF(ISNA(INDEX('Raw Data'!$H:$H,MATCH(1,('Raw Data'!$A:$A="Total For Agent")*('Raw Data'!$B:$B=ShortID!$D6),0))),0,INDEX('Raw Data'!$H:$H,MATCH(1,('Raw Data'!$A:$A="Total For Agent")*('Raw Data'!$B:$B=ShortID!$D6),0))+0))

Could you add onto this? or any other way of multiple vlookup criterias becuase the indexing works but is just monumnetally slow with amount of data being indexed and there would have to be over 400 index's across the document which isnt practical on computers that are about 10 years old with low processing/ram power.

Regards

 

[barry houdini]

You can't use that approach within SUMIFS. It would be better, probably if you could modify the source data so that they are numeric rather than text, otherwise you could use SUMPRODUCT like this

=IF($B10="","",SUMPRODUCT('Raw Data'!$H$2:$H$100+0,('Raw Data'!$B$2:$B$100=ShortID!$D5)*('Raw Data'!$A$2:$A$100=Total For Agent")))

it's better to use a limited range rather than whole columns, adjust as required

 

[devonknows]

Hi,

Im getting a #Value error on that formula you supplied i think its to do with the +0

Could the raw data be changed via Format? maybe convert it to seconds then convert back? Cause i can select the entire column, i think alot of the drain is coming from the 2 index's within one formula becuase if the isna() formula.

Thanks again,
Devon

 

[devonknows]

Also,

if i take the +0 out it returns 00:00:00

Regards