Sum, iferror, vlookup dilemma

[nLeo]

Howdy,
on sheet with multiple tabs (one for each day) I have names in ColA and values in ColB
each name could be on different rows and the value on the same row as the name..
some days might have the name missing.
I want to get a Week totals so I am using SUM(IFERROR(lookup("bob",'1st'!,$A$3:$A$35,'1st'!,$B$3:$B$35),0) and so on for every day (tab: 2nd, 3rd..)
so if bob is not on day 4th it will just by pass it... \
I do get a result, but when I sum them manually I get a different one..
what am I doing wrong?

 

[KRice]

LOOKUP requires that your array be sorted. I think you also have an extra comma in your sheet/cell addresses. You may want to consider an approach like this. Make a record of the Names and Values sheet/cell addresses associated with each day...here names are in column A and values in column B. And then use SUMPRODUCT and INDIRECT to reference those other sheets, pull matching data from them and sum the results across the sheets. In this example, I made three other sheets with three names each...some names appear on both sheets, some names appear on only one sheet.

Book1
	A	B	C	D
1	Names	1st!$A$3:$A$35	2nd!$A$3:$A$35	3rd!$A$3:$A$35
2	Values	1st!$B$3:$B$35	2nd!$B$3:$B$35	3rd!$B$3:$B$35
3
4		Day 1	Day 2	Day 3
5	Sue	1	0	0
6	Joe	3	0	9.5
7	Tammy	5	0	0
8	Bob	0	7	2.5
9	Mary	0	11	4.5
10	Sam	0	13	0
Summary

Cell Formulas
Range	Formula
B5:D10	B5	=SUMPRODUCT((INDIRECT(B$1)=$A5)*INDIRECT(B$2))

 

[nLeo]

thank you Kirk,
each day however, is on a different tab

and

one or more will be missing and each name could be on different rows on each TAB

 

[nLeo]

Actually no Kirk.. just needed my coffee and reconnect .. and it works thank you.
now I need to put in practice..

 

[nLeo]

and I as I suspected...
what if I want to get an average out of these? (if is not too much to ask)

 

[KRice]

Just to follow up, yes, the sheet/cell addresses associated with each day make it convenient to see where your information is stored. Then a single formula using SUMPRODUCT & INDIRECT functions pulls matching data from those references. Just be sure to confirm that the worksheet matches with the relevant day. For example, for Day 1, those data are probably on tab "1st", so you'll have "1st!$A$A:$A$35", or something to that effect. Adjust the cell ranges accordingly. There is no harm in opening the ranges up and using the same across all tabs (worksheets).

About the average, what kind of average were you thinking? For example, if Sue has values reported back for Days 1, 2, and 3 of 1, 0, and 14.3...do you want to know the average of 1, 0, 14.3 (a daily average) or the average of 1 and 14.3 (the average per day active, because Sue may not have been assigned that day). I've added some columns that do both.

I may have misunderstood your original post. Did you want to construct a table showing the weekly details broken down by day (which is what this approach does), or did you want a solution that essentially says for Week X, Days 1-5, here are the performance numbers (perhaps sums and averages) without actually showing the daily compilations?

Book1
	A	B	C	D	E	F	G
1	Names	1st!$A$3:$A$35	2nd!$A$3:$A$35	3rd!$A$3:$A$35
2	Values	1st!$B$3:$B$35	2nd!$B$3:$B$35	3rd!$B$3:$B$35
3
4		Day 1	Day 2	Day 3	Sum	Avg/Day	Avg/Day Active
5	Sue	1	0	14.3	15.3	5.1	7.65
6	Joe	3	0	9.5	12.5	4.16667	6.25
7	Tammy	5	0	0	5	1.66667	5
8	Bob	0	7	2.5	9.5	3.16667	4.75
9	Mary	0	11	4.5	15.5	5.16667	7.75
10	Sam	0	13	0	13	4.33333	13
Summary

Cell Formulas
Range	Formula
B5:D10	B5	=SUMPRODUCT((INDIRECT(B$1)=$A5)*INDIRECT(B$2))
E5:E10	E5	=SUM(B5:D5)
F5:F10	F5	=AVERAGE(B5:D5)
G5:G10	G5	=AVERAGEIF(B5:D5,">0")

 

[nLeo]

Thank you, 'your original reply worked perfectly..
basically I have a TAB which represent a week (March 23rd to 29th) and individual TABS for each day.. the weekly TAB is uploaded automatically..
Average will be, for example, the duration of 23,24,25,26,27.28.29 / 7 (days) in this case..
Issue with the Average would be the same as the SUMPRODUCT as Sue will be on A5 on Tab 23rd , might not be on tab 24th and could be on A7 on tab 25th..

 

[KRice]

That sounds great. If you weren't interested in constructing the table for the week, broken down day by day (which is what the above does), then you could opt for an approach like this:

MrExcel_20200331.xlsx
	A	B	C	D	E
14		Sum	Avg/Day		SheetNames
15	Sue	15.3	5.1		1st
16	Joe	12.5	4.166666667		2nd
17	Tammy	5	1.666666667		3rd
18	Bob	9.5	3.166666667
19	Mary	15.5	5.166666667
20	Sam	13	4.333333333
Summary

Cell Formulas
Range	Formula
B15:B20	B15	=SUMPRODUCT(SUMIFS(INDIRECT("'"&$E$15:$E$17&"'!$B$3:$B$100"),INDIRECT("'"&$E$15:$E$17&"'!$A$3:$A$100"),$A15))
C15:C20	C15	=B15/3

To apply this to your application, extend the list of sheet names and then adjust the B15 formula to the appropriate range (e.g., E15:E17 might be E1:E7 assuming that's where you put the list for all 7 days). The average then is easy since you want a weekly average regardless of any other condition, so an individual's sum for the week is divided by 7...so the divisor in the formula for cell C15 would be 7 rather than 3 that I show.

 

[nLeo]

THanks Kirk.. going to have a go after a baileys break

 

[nLeo]

Guess I had 1 too many and ended up bad lol.