| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| help with Index and Match Formula | Excel Worksheet Functions | |||
| Index/ Match Formula | Excel Worksheet Functions | |||
| Formula using INDEX and MATCH | Excel Worksheet Functions | |||
| How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
| Match and index functions: corrlating data from 2 worksheets | Excel Worksheet Functions | |||
Linear Mode
