sum based on less than current date, name & type

[jabbott3]

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.

 

[fairwinds]

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
	A	B	C	D
1
2	2005-01-01	Smith,Mary	Annual	8
3	2005-01-01	Kerr,John	Sick	2
4	2005-01-01	Smith,Mary	Annual	4
5	2005-01-17	Perry,Karen	Annual	1
6
7
8
9
10	Kerr,John	0
11	Smith,Mary	12
12	Perry,Karen	0
Sheet5

 

[jabbott3]

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

 

[fairwinds]

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

Just adjust the ranges to fit your book.

 

[jabbott3]

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

 

[jabbott3]

=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

 

[fairwinds]

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.

 

[jabbott3]

You are wonderful!!! Thank you very much.

I think I looked at it too long.

It works great now!