SUM of cells in corresponding rows IF 3 CONDITIONS are met - Vacation Days Tracker

Hello all,

I'm attempting to set up a tracker that totals the # of days employees have taken and requested off based on submissions from a Google Form (this will be in Google Sheets, but if you can show me how to do it in Excel that will be just fine).

More explicitly, for Column H of sheet HPES Counts, I would like to SUM the # of days requested off if (sheet names/columns correspond to images below):

1) The email address on the tracker sheet EQUALS the email address recorded from the Google Form submission (on HPES Column B = HPES Counts Column D).
2) The # of days between the when the request is made and the first day of absence is GREATER THAN 14 (days). Currently I just have a column that subtracts the 2 dates (on HPES Column E-Column A) and returns a value- so if that value is less than 14 is the 2nd condition (on HPES Column O)*
3) That a 3rd column has a date entered. This is a column of supervisor approval. So the dates will only count if the supervisor has manually approved them (on HPES Column U)

IF all 3 of these conditions are met, I would like the to sum the cells in the corresponding rows of Column N on HPES, which calculates the NETWORKDAYS between the Start Date of Absence Requested (Column E) and End Date of Absence Requested (Column F).

*As a note to condition 2, this is in place because if the request is made greater than 14 days before the absence, the request will count as a vacation day, but if not it counts as a "DREAM Day" (there's a difference for us). I will have another column that does the same, but sums if this # is less than 14.

Google Form Response Sheet TITLED: HPES
Screen Shot 2014-02-10 at 12.37.49 AM.png

Employee Vacation Tracker Sheet TITLED: HPES Counts
Screen Shot 2014-02-10 at 12.37.59 AM.png

I had previously been using this SUMPRODUCT formula:
=SUMPRODUCT((HPES!B:B=D2)*(HPES!O:O>=14)*(HPES!U:U>1))
but this only counts the # of entries that meet the 3 criteria above and does not take into account a single submission for multiple days.

Thus in the end, cell H3 should equal 8 (total # of days requested off, with greater than 14 days notice) instead of 2 (# of request instances with greater than 14 days notice).


Thank you so much for your help. It is GREATLY APPRECIATED and I look forward to being part of this online community