chrono2483
Board Regular
- Joined
- Aug 23, 2014
- Messages
- 164
- Office Version
- 2016
Hello Everyone,
I have two sheets: Sheet1 and Sheet2.
Sheet1, contains the raw data.
Sheet2, contains tables with formulas to pull from Sheet1
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Day of Week
[/TD]
[TD]Sunday
[/TD]
[TD]Monday
[/TD]
[TD]Tuesday
[/TD]
[TD]Wednesday
[/TD]
[TD]Thursday
[/TD]
[TD]Friday
[/TD]
[TD]Saturday
[/TD]
[TD]Sunday
[/TD]
[TD]Monday
[/TD]
[TD]Tuesday
[/TD]
[TD]Wednesday
[/TD]
[TD]Thursday
[/TD]
[TD]Friday
[/TD]
[TD]Saturday
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]WeekNum
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Name
[/TD]
[TD]1-Jan-17
[/TD]
[TD]2-Jan-17
[/TD]
[TD]3-Jan-17
[/TD]
[TD]4-Jan-17
[/TD]
[TD]5-Jan-17
[/TD]
[TD]6-Jan-17
[/TD]
[TD]7-Jan-17
[/TD]
[TD]8-Jan-17
[/TD]
[TD]9-Jan-17
[/TD]
[TD]10-Jan-17
[/TD]
[TD]11-Jan-17
[/TD]
[TD]12-Jan-17
[/TD]
[TD]13-Jan-17
[/TD]
[TD]14-Jan-17
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Paula Smith
[/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]23
[/TD]
[TD]26
[/TD]
[TD]30
[/TD]
[TD]-
[/TD]
[TD]45
[/TD]
[TD]65
[/TD]
[TD]99
[/TD]
[TD]99
[/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]John Doe
[/TD]
[TD]56
[/TD]
[TD]57
[/TD]
[TD]88
[/TD]
[TD]98
[/TD]
[TD]76
[/TD]
[TD]44
[/TD]
[TD]56
[/TD]
[TD]44
[/TD]
[TD]43
[/TD]
[TD]41
[/TD]
[TD]38
[/TD]
[TD]36
[/TD]
[TD]39
[/TD]
[TD]40
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Jane Doe
[/TD]
[TD]99
[/TD]
[TD]99
[/TD]
[TD]99
[/TD]
[TD]100
[/TD]
[TD]101
[/TD]
[TD]111
[/TD]
[TD]112
[/TD]
[TD]79
[/TD]
[TD]77
[/TD]
[TD]74
[/TD]
[TD]100
[/TD]
[TD]101
[/TD]
[TD]112
[/TD]
[TD]99
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Craig Lowes
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Peter Oz
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
;
What I would like to do is have a formula that will do similar to a VLOOKUP to match the name of the client. Then, based on the weeknum, do an AVERAGEIF of all the values that fall within that range, for that name. What it will return is the average for that week, for that client.
Example: John Doe would return 67.8, for Week 1 // Jane Doe would return 103 for Week 1 // Paula Smith would return 77, for Week 2.
I've been playing around with this formula that works in a similar scenario, with no success:
=IFERROR(VLOOKUP($A$5,Sheet1!$A:$A,AVERAGEIF(SUMPRODUCT(--(Sheet1!$2:$2=Sheet2!$C$1),Sheet1!7:7)),FALSE),0)
Where $A$5 = Client's name, $C$1 = Week Number
Am I missing something with my formula? Any help would be great. TY!
I have two sheets: Sheet1 and Sheet2.
Sheet1, contains the raw data.
Sheet2, contains tables with formulas to pull from Sheet1
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Day of Week
[/TD]
[TD]Sunday
[/TD]
[TD]Monday
[/TD]
[TD]Tuesday
[/TD]
[TD]Wednesday
[/TD]
[TD]Thursday
[/TD]
[TD]Friday
[/TD]
[TD]Saturday
[/TD]
[TD]Sunday
[/TD]
[TD]Monday
[/TD]
[TD]Tuesday
[/TD]
[TD]Wednesday
[/TD]
[TD]Thursday
[/TD]
[TD]Friday
[/TD]
[TD]Saturday
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]WeekNum
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Name
[/TD]
[TD]1-Jan-17
[/TD]
[TD]2-Jan-17
[/TD]
[TD]3-Jan-17
[/TD]
[TD]4-Jan-17
[/TD]
[TD]5-Jan-17
[/TD]
[TD]6-Jan-17
[/TD]
[TD]7-Jan-17
[/TD]
[TD]8-Jan-17
[/TD]
[TD]9-Jan-17
[/TD]
[TD]10-Jan-17
[/TD]
[TD]11-Jan-17
[/TD]
[TD]12-Jan-17
[/TD]
[TD]13-Jan-17
[/TD]
[TD]14-Jan-17
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Paula Smith
[/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]23
[/TD]
[TD]26
[/TD]
[TD]30
[/TD]
[TD]-
[/TD]
[TD]45
[/TD]
[TD]65
[/TD]
[TD]99
[/TD]
[TD]99
[/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]John Doe
[/TD]
[TD]56
[/TD]
[TD]57
[/TD]
[TD]88
[/TD]
[TD]98
[/TD]
[TD]76
[/TD]
[TD]44
[/TD]
[TD]56
[/TD]
[TD]44
[/TD]
[TD]43
[/TD]
[TD]41
[/TD]
[TD]38
[/TD]
[TD]36
[/TD]
[TD]39
[/TD]
[TD]40
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Jane Doe
[/TD]
[TD]99
[/TD]
[TD]99
[/TD]
[TD]99
[/TD]
[TD]100
[/TD]
[TD]101
[/TD]
[TD]111
[/TD]
[TD]112
[/TD]
[TD]79
[/TD]
[TD]77
[/TD]
[TD]74
[/TD]
[TD]100
[/TD]
[TD]101
[/TD]
[TD]112
[/TD]
[TD]99
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Craig Lowes
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Peter Oz
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
;
What I would like to do is have a formula that will do similar to a VLOOKUP to match the name of the client. Then, based on the weeknum, do an AVERAGEIF of all the values that fall within that range, for that name. What it will return is the average for that week, for that client.
Example: John Doe would return 67.8, for Week 1 // Jane Doe would return 103 for Week 1 // Paula Smith would return 77, for Week 2.
I've been playing around with this formula that works in a similar scenario, with no success:
=IFERROR(VLOOKUP($A$5,Sheet1!$A:$A,AVERAGEIF(SUMPRODUCT(--(Sheet1!$2:$2=Sheet2!$C$1),Sheet1!7:7)),FALSE),0)
Where $A$5 = Client's name, $C$1 = Week Number
Am I missing something with my formula? Any help would be great. TY!