=Sum Problem

[key.digital]

I'm trying to get a spreadsheet document to calculate via =SUM of a total column, to generate this column data it comes from the previous column next to it with the formula "=IF(G2="200",+D2,"")" All this is doing is if the cell G2 is the extension "200" being one of our call centre staff ex.number, it then puts the "call duration" in the cell in and if any other extension in there, it wont include it and they can be a new formula in a different column.

Because the data is imported from our VOIP data source the formulas in the columns work only when the data arrives, so all the blank fields under it are they show as #Value
and =SUM wont work if that's in a field.



What I'm trying to is a total of call time per Ext.number, as I've solved a way to calculate the call amount, i just cant find a way to get the call duration so we can display it on a TV on our wall to motivate staff
Ext 200 = 74 Calls total
Ext 200 = 784 Total Call Duration

Ext 201 = 97 Calls total
Ext 201 = 854 Total Call Duration

Please see attached picture to get a better understanding of my problem, I'm sure there are very clever ways to solve it but this is the only way i can work out a way to get it to work.

Here is the file, its 37mb so it wont upload.
http://wikisend.com/download/440042/Call Report.xlsm

[Jonmo1]

Can you attach the book using The forums attachment feature (many users don't like or are unable to go to 3rd party file sharing sites)

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[key.digital]

The red call is the one that is not working, the green one is the one what isn't working as it includes the #VALUE cell that wont work with =SUM, i want it to be like the green one but add up all the numbers in the cells below even if they have #value in them and not display "#value" but the total call amount instead.

[sourabhg98]

Check attached..

[sourabhg98]

If you want to just remove the error use an iferror statement
in cell H2
=IFERROR(IF(G2="200",+D2,""),"")
and copy down

[key.digital]

omg thank you so much, i was trying that in the SUM and could not get it to work, it also helped as i didn't know you can have 2 cell functions in one cell, very helpful. cheers.

[sourabhg98]

Glad to know it worked!!
Cheers!!
And thanks for the rep!
Also you can can have any number of functions in a cell...not just 2..

[key.digital]

ah you are welcome, i just need to work out the time as i'm sure when you add minuet durations as numbers in excel it doesn't add up to normal times.
like 1.35 min's + 1.30 mins will add up to 2.65 when it should be 3.05mins