SUMIFs Formula - Converting Hours to Days to use as criteria

**jennarenae** · 08-21-2013, 04:18 PM

Hi everyone! This is my first post here but I'm a longtime lurker. I struggle with explaining excel issues, so I will try my best!

I have 2 Sheets - my end goal is to find the number of submittals (recorded in column C - TTF tab) and sum those values if the Date Raised (column D) + Time to Fill (column B) is within the date range on my summary tab. The issue is that Time to Fill is in hours and I need it to be converted to days within the formula. I do not want to add any columns to my TTF tab because I'm trying to make my project extremely automatic without adding additional columns or using macros. That being said - I dont know if what I want can be done.

Just for reference TTF is a table named TTFTable. I'm trying to do this through table references. Here is the code that does not work - I've attached a screen shot of the error I get. (I am trying to sum positions filled in june, so anything >5/31 and <=6/30) Whenever I open the functions tab, I do not see a #Value error. I've also attached the workbook for reference.

Please Login or Register  to view this content.

Thanks everybody!

**icestationzbra** · 08-21-2013, 10:09 PM

there are two reasons why your formula isn't working, and won't work even if fixed.

1. you are manipulating the "criteria_range" portion of the SUMIFS argument (shown below), which it does not allow. it allows manipulation of the "criteria" portion, however.

TTFTable[@[Date raised]]+(TTFTable[@[Time To Fill (Hours)]]/24)

2. the values that you have on the Summary tab between B1 and G1 (except E1) are anything but Dates. they "look" like Dates, but they are actually Text. so, even if your formula were fixed, it would not successfully give you an output because of this fact (i.e., formula will output an error).

so, first you have to fix the values between B1 and G1 to make them real Dates.

then, your SUMIFS formula can be fixed in one of the following two ways:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

however, for some godforsaken reason, either of these are not giving the "right" output and i am not able to figure out why.

so, below is another formula that you can use, which i have tested to be correct for your sample data.

Please Login or Register  to view this content.

SUMIFs Formula - Converting Hours to Days to use as criteria

LinkBack

Thread Tools

Rate This Thread

Display

SUMIFs Formula - Converting Hours to Days to use as criteria

Re: SUMIFs Formula - Converting Hours to Days to use as criteria

Thread Information

Users Browsing this Thread

Similar Threads

Converting days hours minutes to hours

Converting hours to 8 hour days, 31 day limit? Formula blows up at 256 hours..

Converting hours into days, hours & minutes

Problem converting Hours to Days, Hours, Minutes

[SOLVED] converting hours to days,hours,minutes

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions