Counting Table

[CST]

Hi All,

I was wondering if I could get help with the following, I want to make a table that counts up totals across 5 sheets.

The sheets will be Monday, Tuesday, Wednesday, Thursday and Friday and a Total Sheet,

On the weekdays in the A column there will be a list of Names and in Column B there will be a number (eg, 1,2,3,4.5,5).

For example "John" does 2 hours on Monday and 3 hours on Wednesday, I want this to add upto 5 on the total sheet, the problems im having is that the names wont be in the same row on each day, they can be different rows on each day.

Cany anyone help with this ?

 

[Domenic]

Assuming that D2 contains the name of interest, such as John, try..

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"Monday","Tuesday","Wednesday","Thursday","Friday"}&"'!A2:A100"),D2,INDIRECT("'"&{"Monday","Tuesday","Wednesday","Thursday","Friday"}&"'!B2:B100")))

Alternatively, try the following formula...

=SUMPRODUCT(SUMIF(INDIRECT("'"&$G$2:$G$6&"'!A2:A100"),D2,INDIRECT("'"&$G$2:$G$6&"'!B2:B100")))

...where G2:G6 contains the sheet names (Monday, Tuesday, etc.).

Hope this helps!