Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with sum if
I need help with sum if. I have 5 sheets on a workbook, the 1st sheet has 12
names. The other 4 sheets contain the same 12 names in Column A and and weekly amounts for 52 weeks in rows. I am looking to see if the name on sheet 5 matches on the other sheets then sum the weekly amounts just for that name on sheet 5. Can anyone help? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with sum if
Assuming that the names as on Sheet 1 and the other 4 sheets have names &
Amounts so that you want the formula in Sheet 1 not sheet 5 as your post ends up seeming to suggest, (at least to me) for the Name in Cell A8 try: =SUMPRODUCT((Sheet2!A1:A12=A8)*(Sheet2!B1:BA12)+(S heet3!A1:A12=A8)*(Sheet3!B1:BA12)+(Sheet4!A1:A12=A 8)*(Sheet4!B1:BA12)+(Sheet5!A1:A12=A8)*(Sheet5!B1: BA12)) If you do want the formulas in Sheet5 then use: =SUMPRODUCT((Sheet1!A1:A12=A8)*(Sheet1!B1:BA12)+(S heet2!A1:A12=A8)*(Sheet2!B1:BA12)+(Sheet3!A1:A12=A 8)*(Sheet3!B1:BA12)+(Sheet4!A1:A12=A8)*(Sheet4!B1: BA12)) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Trixie" wrote in message ... I need help with sum if. I have 5 sheets on a workbook, the 1st sheet has 12 names. The other 4 sheets contain the same 12 names in Column A and and weekly amounts for 52 weeks in rows. I am looking to see if the name on sheet 5 matches on the other sheets then sum the weekly amounts just for that name on sheet 5. Can anyone help? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with sum if
I'm trying that one, but it's trying to Update Values...and reference another
workbook. Here's my main sheet. I would like to find D71 in the corresponding sheets and sum the rows for just D71. One sheet is 2005, one is 2006, so on. 2005 has 435 rows, each time it sees D71 in column A, I want it to go to column J and add that number. I hope it's a little clearer. Year 2005 Year 2006 Year 2007 Year 2008 D71- D76 - GLX - LQQ - Thank you for your help. Trixie "Sandy Mann" wrote: Assuming that the names as on Sheet 1 and the other 4 sheets have names & Amounts so that you want the formula in Sheet 1 not sheet 5 as your post ends up seeming to suggest, (at least to me) for the Name in Cell A8 try: =SUMPRODUCT((Sheet2!A1:A12=A8)*(Sheet2!B1:BA12)+(S heet3!A1:A12=A8)*(Sheet3!B1:BA12)+(Sheet4!A1:A12=A 8)*(Sheet4!B1:BA12)+(Sheet5!A1:A12=A8)*(Sheet5!B1: BA12)) If you do want the formulas in Sheet5 then use: =SUMPRODUCT((Sheet1!A1:A12=A8)*(Sheet1!B1:BA12)+(S heet2!A1:A12=A8)*(Sheet2!B1:BA12)+(Sheet3!A1:A12=A 8)*(Sheet3!B1:BA12)+(Sheet4!A1:A12=A8)*(Sheet4!B1: BA12)) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Trixie" wrote in message ... I need help with sum if. I have 5 sheets on a workbook, the 1st sheet has 12 names. The other 4 sheets contain the same 12 names in Column A and and weekly amounts for 52 weeks in rows. I am looking to see if the name on sheet 5 matches on the other sheets then sum the weekly amounts just for that name on sheet 5. Can anyone help? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with sum if
No wonder people get confused by my posts!
and drob it down to A5 on the fill handle. was meant to be: and drag down to Row 5 on the fill handle. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... I think that it is trying the reference another Workbook because youd on't have the sheets that I am quoting. With your sheets named: Year 2005 Year 2006 Year 2007 Year 2008 and in the other sheet, (the sheet where you want the summing to occur),the *names* that you want to lookup starting with D71 in A2 being: A2 D71 A3 D76 A4 GLX A5 LQQ Enter the following formula: =SUMPRODUCT(('Year 2005'!$A$1:$A$435=A2)*('Year 2005'!$J$1:$J$435)+('Year 2006'!$A$1:$A$435=A2)*('Year 2006'!$J$1:$J$435)+('Year 2007'!$A$1:$A$435=A2)*('Year 2007'!$J$1:$J$435)+('Year 2008'!$A$1:$A$435=A2)*('Year 2008'!$J$1:$J$435)) and drob it down to A5 on the fill handle. This will add up every cell in Column J that has D71 in the same row in Column A in the sheets: Year 2005, Year 2006, Year 2007 & Year 2008 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Trixie" wrote in message ... I'm trying that one, but it's trying to Update Values...and reference another workbook. Here's my main sheet. I would like to find D71 in the corresponding sheets and sum the rows for just D71. One sheet is 2005, one is 2006, so on. 2005 has 435 rows, each time it sees D71 in column A, I want it to go to column J and add that number. I hope it's a little clearer. Year 2005 Year 2006 Year 2007 Year 2008 D71- D76 - GLX - LQQ - Thank you for your help. Trixie "Sandy Mann" wrote: Assuming that the names as on Sheet 1 and the other 4 sheets have names & Amounts so that you want the formula in Sheet 1 not sheet 5 as your post ends up seeming to suggest, (at least to me) for the Name in Cell A8 try: =SUMPRODUCT((Sheet2!A1:A12=A8)*(Sheet2!B1:BA12)+(S heet3!A1:A12=A8)*(Sheet3!B1:BA12)+(Sheet4!A1:A12=A 8)*(Sheet4!B1:BA12)+(Sheet5!A1:A12=A8)*(Sheet5!B1: BA12)) If you do want the formulas in Sheet5 then use: =SUMPRODUCT((Sheet1!A1:A12=A8)*(Sheet1!B1:BA12)+(S heet2!A1:A12=A8)*(Sheet2!B1:BA12)+(Sheet3!A1:A12=A 8)*(Sheet3!B1:BA12)+(Sheet4!A1:A12=A8)*(Sheet4!B1: BA12)) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Trixie" wrote in message ... I need help with sum if. I have 5 sheets on a workbook, the 1st sheet has 12 names. The other 4 sheets contain the same 12 names in Column A and and weekly amounts for 52 weeks in rows. I am looking to see if the name on sheet 5 matches on the other sheets then sum the weekly amounts just for that name on sheet 5. Can anyone help? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with sum if
worked perfectly.
Thank you Trixie "Sandy Mann" wrote: No wonder people get confused by my posts! and drob it down to A5 on the fill handle. was meant to be: and drag down to Row 5 on the fill handle. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... I think that it is trying the reference another Workbook because youd on't have the sheets that I am quoting. With your sheets named: Year 2005 Year 2006 Year 2007 Year 2008 and in the other sheet, (the sheet where you want the summing to occur),the *names* that you want to lookup starting with D71 in A2 being: A2 D71 A3 D76 A4 GLX A5 LQQ Enter the following formula: =SUMPRODUCT(('Year 2005'!$A$1:$A$435=A2)*('Year 2005'!$J$1:$J$435)+('Year 2006'!$A$1:$A$435=A2)*('Year 2006'!$J$1:$J$435)+('Year 2007'!$A$1:$A$435=A2)*('Year 2007'!$J$1:$J$435)+('Year 2008'!$A$1:$A$435=A2)*('Year 2008'!$J$1:$J$435)) and drob it down to A5 on the fill handle. This will add up every cell in Column J that has D71 in the same row in Column A in the sheets: Year 2005, Year 2006, Year 2007 & Year 2008 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Trixie" wrote in message ... I'm trying that one, but it's trying to Update Values...and reference another workbook. Here's my main sheet. I would like to find D71 in the corresponding sheets and sum the rows for just D71. One sheet is 2005, one is 2006, so on. 2005 has 435 rows, each time it sees D71 in column A, I want it to go to column J and add that number. I hope it's a little clearer. Year 2005 Year 2006 Year 2007 Year 2008 D71- D76 - GLX - LQQ - Thank you for your help. Trixie "Sandy Mann" wrote: Assuming that the names as on Sheet 1 and the other 4 sheets have names & Amounts so that you want the formula in Sheet 1 not sheet 5 as your post ends up seeming to suggest, (at least to me) for the Name in Cell A8 try: =SUMPRODUCT((Sheet2!A1:A12=A8)*(Sheet2!B1:BA12)+(S heet3!A1:A12=A8)*(Sheet3!B1:BA12)+(Sheet4!A1:A12=A 8)*(Sheet4!B1:BA12)+(Sheet5!A1:A12=A8)*(Sheet5!B1: BA12)) If you do want the formulas in Sheet5 then use: =SUMPRODUCT((Sheet1!A1:A12=A8)*(Sheet1!B1:BA12)+(S heet2!A1:A12=A8)*(Sheet2!B1:BA12)+(Sheet3!A1:A12=A 8)*(Sheet3!B1:BA12)+(Sheet4!A1:A12=A8)*(Sheet4!B1: BA12)) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Trixie" wrote in message ... I need help with sum if. I have 5 sheets on a workbook, the 1st sheet has 12 names. The other 4 sheets contain the same 12 names in Column A and and weekly amounts for 52 weeks in rows. I am looking to see if the name on sheet 5 matches on the other sheets then sum the weekly amounts just for that name on sheet 5. Can anyone help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|