Help with SUMIFS/SUMPRODUCT Formula? for date/time
Trying to create a formula that will grab the #total calls (successful+no answer) to 30mins interval. Also need to find out if these calls falls under weekday or weekend.
Not sure if this should be a a SUMIFS or SUMPRODUCT? desperately need help.
Raw data is extracted and pasted over Col A-G (so would prefer if there's no manipulation required to these columns each time data is extracted and pasted over).
Any advice or assistance would really be good. Thank you in advance
The first problem you have is that the times in Column B are text... Before you can do any time calculations you need to convert those to real Excel times. This function isn't elegant but it works :-)
Paste it down through the table starting from row 16
Please Login or Register to view this content.
Then the formula from N3 down is :
Please Login or Register to view this content.
Last edited by AndyLitch; 03-31-2013 at 07:37 AM.
Thanks Andy, I'm a beginner in Excel, so truly don't understand how your formula works. But certainly will give it a go..
Hi auswtz
Try converting the text times in B16:B364 to start with:
Formula:
Please Login or Register to view this content.
In say H16 and copy down.
Then in N3 & copy down to N50:
Formula:Please Login or Register to view this content.
Last edited by Kevin UK; 03-31-2013 at 07:45 AM. Reason: Logged out of forum suddenly!
Regards Kevin
Merged Cells (They are the work of the devil!!!)
Hi auswtz,
You date and time are in text format.. so are they coming in text format only or we can have them in date / time format ?
If we get them in date / time format, then we can use pivot table here...
Regards,
DILIPandey
<click on below * if this helps>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), [email protected]
I've converted the times and summed the totals in the attached... Have a look and see what you think.
Attachment 224721
@ AndyLitch
The OP has Excel 2007, that is why they are using the SUMIFS.
Edit: Just noticed they uploaded a .xls file! So sumproduct will be the way.
@ auswtz. Perhaps you need to update your profile to reflect what version of excel you use!
Last edited by Kevin UK; 03-31-2013 at 07:51 AM.
Andy thank you!... the formula for column N worked. I will now see if I can create formula to allocate the calls to either weekend/weekday depending on the dates (knowing my luck might need help on that as well, but I'll give it a go first)
Thank you KevinI will keep a note of your formula as well.
Thanks Dilipandey...the raw data are extracted and coming in text format only.
For the weekday/weekend part I would do this:
Fully populate column A so that the date is on every row
Add this formula in H16 copied down
=TEXT(A16,"ddd")
Now you have a column with the weekday as text so you can use that in a SUMPRODUCT/SUMIFS function
Audere est facere
Thanks
I just need to work out how to use the sumifs or sumproduct with my criterias???
auswtz
You can not use SUMIFS, you uploaded a .xls file!
Kevin...thanks for the advise but I have created a formula SUMIFS($E:$E,$H:$H,">="&$L3,$H:$H,"<="&$M3,$I:$I,"<="&O$1,$I:$I,">1") and it worked on xls
please let me know though if there is something I have missed or will cause this formula to error later on
SUMIFS can work in an .xls file - if you are using Excel 2007 (or later) and save as an .xls. Of course you can argue there isn't much point as the SUMIFS function won't work if you open the workbook with Excel 2003!Originally Posted by Kevin UK
@ daddylonglegs
I am aware of that, as by post #4 I used the SUMIFS
Just confusing when a OP uploads a .xls!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks