Sum If formula between two sheets.

[asyamonique]

[TABLE="width: 1263"]
<tbody>[TR]
[TD="class: xl63, width: 54"]January[/TD]
[TD="class: xl63, width: 70"]Dept.[/TD]
[TD="class: xl63, width: 116"]Name & Surname[/TD]
[TD="class: xl64, width: 33"][/TD]
[TD="class: xl64, width: 33"][/TD]
[TD="class: xl64, width: 33"][/TD]
[TD="class: xl64, width: 33"][/TD]
[TD="class: xl64, width: 33"][/TD]
[TD="class: xl64, width: 33"][/TD]
[TD="class: xl64, width: 33"]OFF[/TD]
[TD="class: xl64, width: 33"]OFF[/TD]
[TD="class: xl64, width: 33"][/TD]
[TD="class: xl64, width: 33"][/TD]
[TD="class: xl64, width: 33"][/TD]
[TD="class: xl64, width: 33"][/TD]
[TD="class: xl64, width: 33"][/TD]
[TD="class: xl64, width: 33"]OFF[/TD]
[TD="class: xl64, width: 33"]OFF[/TD]
[TD="class: xl64, width: 33"][/TD]
[TD="class: xl64, width: 33"][/TD]
[TD="class: xl64, width: 33"][/TD]
[TD="class: xl64, width: 33"][/TD]
[TD="class: xl64, width: 33"][/TD]
[TD="class: xl64, width: 33"]OFF[/TD]
[TD="class: xl64, width: 33"]OFF[/TD]
[TD="class: xl64, width: 33"][/TD]
[TD="class: xl64, width: 33"][/TD]
[TD="class: xl64, width: 33"][/TD]
[TD="class: xl64, width: 33"][/TD]
[TD="class: xl64, width: 33"][/TD]
[TD="class: xl64, width: 33"]OFF[/TD]
[TD="class: xl64, width: 33"]OFF[/TD]
[TD="class: xl64, width: 33"][/TD]
[TD="class: xl64, width: 33"][/TD]
[/TR]
</tbody>[/TABLE]

Good Day,
I have huge list like given above all the way down from the columns A to AH those datas located on the sheet named "list"
I need to put a table to another sheet like below to show all persons OFF dates for each mnth from the A1:M100
A1 text will be written OFF and below cells with name and surnames as given table....

[TABLE="width: 858"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]OFF[/TD]
[TD]JANUARY[/TD]
[TD]FEBRUARY[/TD]
[TD]MARCH[/TD]
[TD]APRIL[/TD]
[TD]MAY[/TD]
[TD]JUNE[/TD]
[TD]JULY[/TD]
[TD]AUGUST[/TD]
[TD]SEPTEMBER[/TD]
[TD]OCTOBER[/TD]
[TD]NOVEMBER[/TD]
[TD]DECEMBER[/TD]
[/TR]
[TR]
[TD]NAME & SURNAME[/TD]
[TD]8[/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]
So, the formula will start from B2


Thanks

 

[Peter_SSs]

1. Does the 'Name & Surname' column in each sheet contain the same names in the same order?

2. If not, does the 'other sheet' have a list of names in column A or are you asking for a formula for that too?

3. Does either sheet contain duplicate names in the 'Name & Surname' column?

4. What version of Excel are you using?

 

[asyamonique]

Hi,
Fixed, thanks.

=SUM(IF(list!$C$1:$C$4997=$A2,IF(list!$A$1:$A$4997=B1,IF(list!$D$1:$AH$4997=$A$1,1))))