Index/Match Formula across 2 worksheets
 

New to all this so any help would be really appreciated. (Using Office 2003)

I have 2 worksheets and need a formula (possibly index/match) to add up
total course hours in Worksheet 2 where the Course Number in Column A matches
the course number in column B.

Worksheet 1
Column A Column B
Course Number Course Hours
1 20
1 25
2 30
2 35

Worksheet 2
Column A Column B
Course Number Total Course Hours
1 ?
2 ?


As I say any help would be greatly appreciated.

Many thanks

Adrian

Index/Match Formula across 2 worksheets
 

On Sheet2, try...

B2. copied down:

=SUMIF('Sheet1'!$A$2:$A$5,'Sheet2'!A2,'Sheet1'!$B$ 2:$B$5)

Hope this helps!

In article ,
Adrian wrote:

New to all this so any help would be really appreciated. (Using Office 2003)

I have 2 worksheets and need a formula (possibly index/match) to add up
total course hours in Worksheet 2 where the Course Number in Column A matches
the course number in column B.

Worksheet 1
Column A Column B
Course Number Course Hours
1 20
1 25
2 30
2 35

Worksheet 2
Column A Column B
Course Number Total Course Hours
1 ?
2 ?


As I say any help would be greatly appreciated.

Many thanks

Adrian

Index/Match Formula across 2 worksheets
 

Hi,
Try this in B1, sheet 2, and copy down:

=SUMPRODUCT(--(Sheet1!$A$1:$A$5=A1)*(Sheet1!$B$1:$B$5))

Regards - Dave.

Index/Match Formula across 2 worksheets
 

Domenic - you're a star - worked a treat :-)))))
--
Many thanks

Adrian


"Domenic" wrote:

On Sheet2, try...

B2. copied down:

=SUMIF('Sheet1'!$A$2:$A$5,'Sheet2'!A2,'Sheet1'!$B$ 2:$B$5)

Hope this helps!

In article ,
Adrian wrote:

New to all this so any help would be really appreciated. (Using Office 2003)

I have 2 worksheets and need a formula (possibly index/match) to add up
total course hours in Worksheet 2 where the Course Number in Column A matches
the course number in column B.

Worksheet 1
Column A Column B
Course Number Course Hours
1 20
1 25
2 30
2 35

Worksheet 2
Column A Column B
Course Number Total Course Hours
1 ?
2 ?


As I say any help would be greatly appreciated.

Many thanks

Adrian

Index/Match Formula across 2 worksheets
 

Thanks for replying Dave - I had success with Domenics solution but
appreciate your time and knowledge!!!
--
Many thanks

Adrian


"Dave" wrote:

Hi,
Try this in B1, sheet 2, and copy down:

=SUMPRODUCT(--(Sheet1!$A$1:$A$5=A1)*(Sheet1!$B$1:$B$5))

Regards - Dave.