SUMIFs Formula - Converting Hours to Days to use as criteria

[jennarenae]

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.

=SUMIFS(TTFTable[@['# Submittals Submitted]],TTFTable[@[Date raised]]+(TTFTable[@[Time To Fill (Hours)]]/24),">"&Summary!D1,TTFTable[@[Date raised]]+(TTFTable[@[Time To Fill (Hours)]]/24),"<="&Summary!E1)

Thanks everybody!

[icestationzbra]

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:

=SUMIFS(TTFTable['# Submittals Submitted],TTFTable[Date raised],">"&Summary!$D$1,TTFTable[Date raised],"<"&(Summary!$E$1+1))

or

=SUMIFS(TTFTable['# Submittals Submitted],TTFTable[Date raised],">"&EOMONTH(Summary!$E$1,-1),TTFTable[Date raised],"<"&Summary!$E$1+1)

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.

=SUMPRODUCT(TTFTable['# Submittals Submitted]*(INT(TTFTable[Date raised])>Summary!D1)*(INT(TTFTable[Date raised])<(Summary!E1+1)))