SumIF ignoring ignoring errors across multiple worksheets

[Paul Naylor]

Hi All, Hoping someone can help.

I.ve got a s/sheet with names and stats on for multiple people across each weekday Sun-Sat, trying to add the values up but when using sumif & Vlookup it only finds 1st entry and tried sumproduct and get a name error , see below formulas:

=SUM(IFERROR(VLOOKUP($B4,Sunday!$A$2:$BO$50,{5},FALSE),IFERROR(VLOOKUP($B4,Monday!$A$2:$BO$50,{5},FALSE),IFERROR(VLOOKUP($B4,Tuesday!$A$2:$BO$50,{5},FALSE),IFERROR(VLOOKUP($B4,Wednesday!$A$2:$BO$50,{5},FALSE),IFERROR(VLOOKUP($B4,Thursday!$A$2:$BO$50,{5},FALSE),IFERROR(VLOOKUP($B4,Friday!$A$2:$BO$50,{5},FALSE),IFERROR(VLOOKUP($B4,Saturday!$A$2:$BO$50,{5},FALSE),""))))))))

column B is names and wanting to sum all column 5 for given name and ignore any non occurances ( all people are not in every day

=SUMPRODUCT(SUMIF(INDIRECT("'"&Days&"'!A3:A99"),$B3,INDIRECT("'"&Days&"'!E3:E99")))

Helper column A with days of the week which coincide with the worksheets , column B is list of the names which I want to look up against which appear in column A on the daily sheets
Not sure where im going wrong ?

 

[Paul Naylor]

re second attempt , apologies forgot to name to range now i'm getting zero ??


=SUMPRODUCT(SUMIF(INDIRECT("'"&Days&"'!"&"A2:A99"),$B2,INDIRECT("'"&Days&"'!"&"E2:E99")))

 

[Paul Naylor]

I've managed to resolve , think it was Something to do with incorrect reference on name
=SUMPRODUCT(SUMIF(INDIRECT("'"&Days&"'!"&"A2:A99"),$A2,INDIRECT("'"&Days&"'!"&"E2:E99")))

Although one columns I need to do an AVERAGE , but there's an error (Sun-No Data, Mon-No Data, Tues-552,Wed-513,Thur-519,Fri-405,Sat-256 Giving results as 391 instead of 449?

=IFERROR(AVERAGE(INDIRECT("'"&Days&"'!"&"A2:A99"),$A2,INDIRECT("'"&Days&"'!"&"D2:D99")),"-")

 

[Paul Naylor]

Hi All,

Found it :

=SUMPRODUCT(SUMIF(INDIRECT("'"&Days&"'!"&"A2:A99"),$A3,INDIRECT("'"&Days&"'!"&"D2:D99")))/SUMPRODUCT(COUNTIF(INDIRECT("'"&Days&"'!"&"A2:A99"),$A3))