| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Worksheets
I have 13 worksheets, one for every month plus a total worksheet. I am
looking for a simple way to sum different cells depending on the result in another cell. For example on the 12 monthly worksheets they all have the same columns. Number of sales, cost of sales, budget and so on. What I want to do is look at each worksheet and check the Number of sales and only accumulate the amounts in the budget cell only if Number of sales is greater than zero and place that number in a cell on the total worksheet. I appreciate any help. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple Worksheets
One way
=SUMPRODUCT(SUMIF(INDIRECT("'"&G1:G11&"'!A2"),"0" ,INDIRECT("'"&G1:G11&"'!B2"))) where G1:G11 holds each name of the sheets, or hard coded maybe =SUMPRODUCT(SUMIF(INDIRECT("'"&{"Jan";"Feb";"Mar"; "Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov"}&" '!A2"),"0",INDIRECT("'"&{"Jan";"Feb";"Mar";"Apr"; "May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov"}&"'!B2") )) A2 is the cell you want to check and B2 the one you want to sum, both can be changed into ranges -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Daniell" wrote in message ... I have 13 worksheets, one for every month plus a total worksheet. I am looking for a simple way to sum different cells depending on the result in another cell. For example on the 12 monthly worksheets they all have the same columns. Number of sales, cost of sales, budget and so on. What I want to do is look at each worksheet and check the Number of sales and only accumulate the amounts in the budget cell only if Number of sales is greater than zero and place that number in a cell on the total worksheet. I appreciate any help. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple Worksheets
Thanks, I will give that a try.
"Peo Sjoblom" wrote: One way =SUMPRODUCT(SUMIF(INDIRECT("'"&G1:G11&"'!A2"),"0" ,INDIRECT("'"&G1:G11&"'!B2"))) where G1:G11 holds each name of the sheets, or hard coded maybe =SUMPRODUCT(SUMIF(INDIRECT("'"&{"Jan";"Feb";"Mar"; "Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov"}&" '!A2"),"0",INDIRECT("'"&{"Jan";"Feb";"Mar";"Apr"; "May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov"}&"'!B2") )) A2 is the cell you want to check and B2 the one you want to sum, both can be changed into ranges -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Daniell" wrote in message ... I have 13 worksheets, one for every month plus a total worksheet. I am looking for a simple way to sum different cells depending on the result in another cell. For example on the 12 monthly worksheets they all have the same columns. Number of sales, cost of sales, budget and so on. What I want to do is look at each worksheet and check the Number of sales and only accumulate the amounts in the budget cell only if Number of sales is greater than zero and place that number in a cell on the total worksheet. I appreciate any help. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple Worksheets
I am having a little trouble getting this to work. I created each worksheet
and placed the formula in the cell and it gives me a #REF error. I hard coded the worksheets into the formula with the same results "Daniell" wrote: Thanks, I will give that a try. "Peo Sjoblom" wrote: One way =SUMPRODUCT(SUMIF(INDIRECT("'"&G1:G11&"'!A2"),"0" ,INDIRECT("'"&G1:G11&"'!B2"))) where G1:G11 holds each name of the sheets, or hard coded maybe =SUMPRODUCT(SUMIF(INDIRECT("'"&{"Jan";"Feb";"Mar"; "Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov"}&" '!A2"),"0",INDIRECT("'"&{"Jan";"Feb";"Mar";"Apr"; "May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov"}&"'!B2") )) A2 is the cell you want to check and B2 the one you want to sum, both can be changed into ranges -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Daniell" wrote in message ... I have 13 worksheets, one for every month plus a total worksheet. I am looking for a simple way to sum different cells depending on the result in another cell. For example on the 12 monthly worksheets they all have the same columns. Number of sales, cost of sales, budget and so on. What I want to do is look at each worksheet and check the Number of sales and only accumulate the amounts in the budget cell only if Number of sales is greater than zero and place that number in a cell on the total worksheet. I appreciate any help. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple Worksheets
You can download a working example here
http://nwexcelsolutions.com/Download...e%20sheets.xls -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Daniell" wrote in message ... I am having a little trouble getting this to work. I created each worksheet and placed the formula in the cell and it gives me a #REF error. I hard coded the worksheets into the formula with the same results "Daniell" wrote: Thanks, I will give that a try. "Peo Sjoblom" wrote: One way =SUMPRODUCT(SUMIF(INDIRECT("'"&G1:G11&"'!A2"),"0" ,INDIRECT("'"&G1:G11&"'!B2"))) where G1:G11 holds each name of the sheets, or hard coded maybe =SUMPRODUCT(SUMIF(INDIRECT("'"&{"Jan";"Feb";"Mar"; "Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov"}&" '!A2"),"0",INDIRECT("'"&{"Jan";"Feb";"Mar";"Apr"; "May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov"}&"'!B2") )) A2 is the cell you want to check and B2 the one you want to sum, both can be changed into ranges -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Daniell" wrote in message ... I have 13 worksheets, one for every month plus a total worksheet. I am looking for a simple way to sum different cells depending on the result in another cell. For example on the 12 monthly worksheets they all have the same columns. Number of sales, cost of sales, budget and so on. What I want to do is look at each worksheet and check the Number of sales and only accumulate the amounts in the budget cell only if Number of sales is greater than zero and place that number in a cell on the total worksheet. I appreciate any help. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple Worksheets
Thanks Peo It is amazing when you forget a ; Thanks
"Peo Sjoblom" wrote: You can download a working example here http://nwexcelsolutions.com/Download...e%20sheets.xls -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Daniell" wrote in message ... I am having a little trouble getting this to work. I created each worksheet and placed the formula in the cell and it gives me a #REF error. I hard coded the worksheets into the formula with the same results "Daniell" wrote: Thanks, I will give that a try. "Peo Sjoblom" wrote: One way =SUMPRODUCT(SUMIF(INDIRECT("'"&G1:G11&"'!A2"),"0" ,INDIRECT("'"&G1:G11&"'!B2"))) where G1:G11 holds each name of the sheets, or hard coded maybe =SUMPRODUCT(SUMIF(INDIRECT("'"&{"Jan";"Feb";"Mar"; "Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov"}&" '!A2"),"0",INDIRECT("'"&{"Jan";"Feb";"Mar";"Apr"; "May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov"}&"'!B2") )) A2 is the cell you want to check and B2 the one you want to sum, both can be changed into ranges -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Daniell" wrote in message ... I have 13 worksheets, one for every month plus a total worksheet. I am looking for a simple way to sum different cells depending on the result in another cell. For example on the 12 monthly worksheets they all have the same columns. Number of sales, cost of sales, budget and so on. What I want to do is look at each worksheet and check the Number of sales and only accumulate the amounts in the budget cell only if Number of sales is greater than zero and place that number in a cell on the total worksheet. I appreciate any help. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMif or SUMproduct across multiple worksheets? | Excel Worksheet Functions | |||
| Line chart from multiple worksheets | Charts and Charting in Excel | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Update multiple worksheets | Excel Discussion (Misc queries) | |||
| Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions | |||
Hybrid Mode
