# Complex get multiple values or sums from table for date range based on dropdown selection.

1. ## Complex get multiple values or sums from table for date range based on dropdown selection.

Hello. I have a problem with a spreadsheet here that I cannot figure out.

On the PayrollEntry sheet I've got a table with the following headers:

Date
Employee
Time In
Time Out
Advance
Total Shift Time hh:mm
Total Shift Time

On the left side of the table I've got a formula using =OFFSET(Sheet2!A:A,0,0,COUNTIF(Sheet2!\$A:\$A,">*"),1) to display a unique list of names of employees.

On the PayrollEntry sheet in cell A3 I have a date range dropdown box.

What I need is Column D2:D27 to populate with the sum of hours (decimal) worked for the date range in the dropdown box and in column E from E2:E27 to show the sum of any Payouts the employee took in that date range chosen in the dropdown box.

I have it working with VBA but it's super slow and really awful code. I'm wondering if its possible to do the same thing faster with excel formulas.

I've left data in that shows how I would like it to populate.

If possible I would like it to leave out total hours worked and total payouts if there are no employee names present to calculate those values from. Currently it produces zeros even though there are no employee names in column C.

Also if possible the dropdown list would display Feb_16 - Feb_29 if it is a leap year otherwise display Feb_16 - Feb_28.

I'm doing my best to help my mom with this so help would be absolutely amazing.

2. ## Re: Complex get multiple values or sums from table for date range based on dropdown select

I think Sheet1 is missing.

3. ## Re: Complex get multiple values or sums from table for date range based on dropdown select

Sorry. Sheet one was renamed to PayrollEntry. I updated the main post. Sorry about that ><.

4. ## Re: Complex get multiple values or sums from table for date range based on dropdown select

Your Date Drop needs proper dates with Year: i suggest To/From as separate dates (Excel dates)

5. ## Re: Complex get multiple values or sums from table for date range based on dropdown select

in D2

=SUMIFS(\$I\$29:\$I\$1000,\$D\$29:\$D\$1000,C2,\$C\$29:\$C\$1000,">=" &\$A\$5,\$C\$29:\$C\$1000,"<=" &\$A\$6)

Dates in A5/A6

6. ## Re: Complex get multiple values or sums from table for date range based on dropdown select

Excellent thank you so much. This works great :D I think I can figure out how to adapt it to include the payouts.

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1