# sum based on less than current date, name & type

#### jabbott3

##### New Member
I am having a problem trying to get what I need.

Sheet 1

column a column b column c column d
01/01/05 Smith, Mary Annual 8
01/02/05 Kerr, John Sick 2
01/15/05 Smith, Mary Annual 4
01/17/05 Perry, Karen Annual 1

Sheet 2

Column a column b column c
Kerr, John
Perry, Karen
Smith, Mary

I need for sheet 2 to show in column b how much annual time each person has used, but it needs to only show how much is used as of todays date, so that each day when I go in, Sheet 2 will be updated as of current date.

I have tried several ways to do this and no luck. Any help is appreciated.

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Welcome to the board!

Try:

=SUMPRODUCT(--(\$B\$2:\$B\$5=A10),--(\$A\$2:\$A\$5<=TODAY()),--(\$C\$2:\$C\$5="Annual"),\$D\$2:\$D\$5)
Book1
ABCD
1
22005-01-01Smith,MaryAnnual8
32005-01-01Kerr,JohnSick2
42005-01-01Smith,MaryAnnual4
52005-01-17Perry,KarenAnnual1
6
7
8
9
10Kerr,John0
11Smith,Mary12
12Perry,Karen0
Sheet5

But since the answers should be on the 2nd sheet, wouldn't you have to reference the first sheet?

Yes ofcourse. I just had to put it in one sheet to get it to show in the exhibit.

Thank you, you have been very helpful, I have left work for the day so I will try it out in the morning and let you know!! Thanks again

=SUMPRODUCT(--('2005_LeaveSlips'!\$C\$2:\$C\$400="Allen, Cheryl"),--('2005_LeaveSlips'!\$B\$3:\$B\$400<=TODAY()),--('2005_LeaveSlips'!\$G\$3:\$G\$400="Annual"),'2005_LeaveSlips'!\$F\$2:\$F\$400)

This just gives a #value in the cell. I'm not sure what I'm doing wrong

All ranges must be equal size:

=SUMPRODUCT(--('2005_LeaveSlips'!\$C\$2:\$C\$400="Allen, Cheryl"),--('2005_LeaveSlips'!\$B\$2:\$B\$400<=TODAY()),--('2005_LeaveSlips'!\$G\$2:\$G\$400="Annual"),'2005_LeaveSlips'!\$F\$2:\$F\$400)

You had some starting in row 2 and some in row 3.

You are wonderful!!! Thank you very much.

I think I looked at it too long.

It works great now!

Replies
1
Views
618
Replies
3
Views
242
Replies
8
Views
293
Replies
5
Views
265
Replies
6
Views
337

1,203,125
Messages
6,053,656
Members
444,676
Latest member
locapoca

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back