Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
summing across worksheets conditionally and being able to copy for
I have about 50+ worksheets in a file that I need to sum. However, I only
want to sum some of the sheets if it meets a certain criteria. This criteria is located in a separate worksheet in the file that lists each worksheet name in one column and in the next column it list the type of worksheet. If a worksheet is of a certain type I need the sum of those types. In addition. there are more than one cell that I am summing. The solution that someone provided me below worked great. So I tried copying the cell to grab the other data that I needed but the cell B2 which in his example is the cell I am grabbing in each worksheet is not dynamic so when you copy the cell with the formula it stays B2. I tried changing it to pull the B2 out of the parenthesis but it didn't work I got a circular reference. Any suggestions. I really need to be able to copy the formula down 30 rows and across 30 columns. This is what I tried: =SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!"&B2),"<0")) http://boisgontierjacques.free.fr/fi.../Sum3DCond.xls Named range cond $H$2:$H$8 nf $G$2:$G$8 nfCond =OffSet($J$2,,,CountIf($J$2:$J$12,"<"&"")) =SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!B2"),"<0")) JB http://boisgontierjacques.free.fr/ On 18 mar, 03:17, Celia wrote: I have about 50+ worksheets in a file that I need to sum. However, I only want to sum some of the sheets if it meets a certain criteria. This criteria is located in a separate worksheet in the file that lists each worksheet name in one column and in the next column it list the type of worksheet. If a worksheet is of a certain type I need the sum of those types. Celia -- Celia |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
summing across worksheets conditionally and being able to copy for
I really need to be able to copy the formula
down 30 rows and across 30 columns. =SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!"&B2),"<0")) Does that mean when you drag copy the formula down then B2 should become: B2 B3 B4 B5 etc And when you drag copy the formula across a row then B2 should become: B2, C2, D2, E2, etc -- Biff Microsoft Excel MVP "Celia" wrote in message ... I have about 50+ worksheets in a file that I need to sum. However, I only want to sum some of the sheets if it meets a certain criteria. This criteria is located in a separate worksheet in the file that lists each worksheet name in one column and in the next column it list the type of worksheet. If a worksheet is of a certain type I need the sum of those types. In addition. there are more than one cell that I am summing. The solution that someone provided me below worked great. So I tried copying the cell to grab the other data that I needed but the cell B2 which in his example is the cell I am grabbing in each worksheet is not dynamic so when you copy the cell with the formula it stays B2. I tried changing it to pull the B2 out of the parenthesis but it didn't work I got a circular reference. Any suggestions. I really need to be able to copy the formula down 30 rows and across 30 columns. This is what I tried: =SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!"&B2),"<0")) http://boisgontierjacques.free.fr/fi.../Sum3DCond.xls Named range cond $H$2:$H$8 nf $G$2:$G$8 nfCond =OffSet($J$2,,,CountIf($J$2:$J$12,"<"&"")) =SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!B2"),"<0")) JB http://boisgontierjacques.free.fr/ On 18 mar, 03:17, Celia wrote: I have about 50+ worksheets in a file that I need to sum. However, I only want to sum some of the sheets if it meets a certain criteria. This criteria is located in a separate worksheet in the file that lists each worksheet name in one column and in the next column it list the type of worksheet. If a worksheet is of a certain type I need the sum of those types. Celia -- Celia |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
summing across worksheets conditionally and being able to copy
-- Celia "T. Valko" wrote: I really need to be able to copy the formula down 30 rows and across 30 columns. =SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!"&B2),"<0")) Does that mean when you drag copy the formula down then B2 should become: B2 B3 B4 B5 etc And when you drag copy the formula across a row then B2 should become: B2, C2, D2, E2, etc -- Biff Microsoft Excel MVP "Celia" wrote in message ... I have about 50+ worksheets in a file that I need to sum. However, I only want to sum some of the sheets if it meets a certain criteria. This criteria is located in a separate worksheet in the file that lists each worksheet name in one column and in the next column it list the type of worksheet. If a worksheet is of a certain type I need the sum of those types. In addition. there are more than one cell that I am summing. The solution that someone provided me below worked great. So I tried copying the cell to grab the other data that I needed but the cell B2 which in his example is the cell I am grabbing in each worksheet is not dynamic so when you copy the cell with the formula it stays B2. I tried changing it to pull the B2 out of the parenthesis but it didn't work I got a circular reference. Any suggestions. I really need to be able to copy the formula down 30 rows and across 30 columns. This is what I tried: =SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!"&B2),"<0")) http://boisgontierjacques.free.fr/fi.../Sum3DCond.xls Named range cond $H$2:$H$8 nf $G$2:$G$8 nfCond =OffSet($J$2,,,CountIf($J$2:$J$12,"<"&"")) =SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!B2"),"<0")) JB http://boisgontierjacques.free.fr/ On 18 mar, 03:17, Celia wrote: I have about 50+ worksheets in a file that I need to sum. However, I only want to sum some of the sheets if it meets a certain criteria. This criteria is located in a separate worksheet in the file that lists each worksheet name in one column and in the next column it list the type of worksheet. If a worksheet is of a certain type I need the sum of those types. Celia -- Celia That is exactly what I mean. Why doesn't it work taking the B2 out of the parenthesis? Does it not make it dynamic that way? Since I was getting a circular reference it appeared to me that it was trying to make it the B2 in the new worksheet that I was doing the calculation in not the worksheets I was trying to sum B2 in. Celia |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
summing across worksheets conditionally and being able to copy
=SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!"&B2),"<0"))
The way that that formula is written it's not referencing cell B2 on each of the sheets. It's referencing cell B2 on the sheet that the formula is entered in. If you want to reference cell B2 on each of the sheets then: =SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!B2"),"<0")) However, as written, that won't allow the reference to B2 increment as you drag copy. What is the very first cell where you want to enter that formula? -- Biff Microsoft Excel MVP "Celia" wrote in message ... -- Celia "T. Valko" wrote: I really need to be able to copy the formula down 30 rows and across 30 columns. =SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!"&B2),"<0")) Does that mean when you drag copy the formula down then B2 should become: B2 B3 B4 B5 etc And when you drag copy the formula across a row then B2 should become: B2, C2, D2, E2, etc -- Biff Microsoft Excel MVP "Celia" wrote in message ... I have about 50+ worksheets in a file that I need to sum. However, I only want to sum some of the sheets if it meets a certain criteria. This criteria is located in a separate worksheet in the file that lists each worksheet name in one column and in the next column it list the type of worksheet. If a worksheet is of a certain type I need the sum of those types. In addition. there are more than one cell that I am summing. The solution that someone provided me below worked great. So I tried copying the cell to grab the other data that I needed but the cell B2 which in his example is the cell I am grabbing in each worksheet is not dynamic so when you copy the cell with the formula it stays B2. I tried changing it to pull the B2 out of the parenthesis but it didn't work I got a circular reference. Any suggestions. I really need to be able to copy the formula down 30 rows and across 30 columns. This is what I tried: =SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!"&B2),"<0")) http://boisgontierjacques.free.fr/fi.../Sum3DCond.xls Named range cond $H$2:$H$8 nf $G$2:$G$8 nfCond =OffSet($J$2,,,CountIf($J$2:$J$12,"<"&"")) =SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!B2"),"<0")) JB http://boisgontierjacques.free.fr/ On 18 mar, 03:17, Celia wrote: I have about 50+ worksheets in a file that I need to sum. However, I only want to sum some of the sheets if it meets a certain criteria. This criteria is located in a separate worksheet in the file that lists each worksheet name in one column and in the next column it list the type of worksheet. If a worksheet is of a certain type I need the sum of those types. Celia -- Celia That is exactly what I mean. Why doesn't it work taking the B2 out of the parenthesis? Does it not make it dynamic that way? Since I was getting a circular reference it appeared to me that it was trying to make it the B2 in the new worksheet that I was doing the calculation in not the worksheets I was trying to sum B2 in. Celia |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
summing across worksheets conditionally and being able to copyfor
Formula in B2:
=SumProduct(N(Offset(INDIRECT(nfCond&"!B2"),Row()-2,Column()-2))) http://boisgontierjacques.free.fr/fi...Sum3DCond2.xls JB On 18 mar, 23:15, Celia wrote: I have about 50+ worksheets in a file that *I need to sum. *However, I only want to sum some of the sheets if it meets a certain criteria. *This criteria is located in a separate worksheet in the file that lists each worksheet name in one column and in the next column it list the type of worksheet. *If a worksheet is of a certain type I need the sum of those types. * In addition. there are more than one cell that I am summing. *The solution that someone provided me below worked great. *So I tried copying the cell to grab the other data that I needed but the cell B2 which in his example is the cell I am grabbing in each worksheet is not dynamic so when you copy the cell with the formula it stays B2. *I tried changing it to pull the B2 out of the parenthesis but it didn't work I got a circular reference. *Any suggestions. * *I really need to be able to copy the formula down 30 rows and across 30 columns. This is what I tried: *=SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!"&B2),"<0")) http://boisgontierjacques.free.fr/fi.../Sum3DCond.xls Named range cond $H$2:$H$8 nf $G$2:$G$8 nfCond =OffSet($J$2,,,CountIf($J$2:$J$12,"<"&"")) =SumProduct(SumIfI(INDIRECT("'"&nfCond &"'!B2"),"<0")) JBhttp://boisgontierjacques.free.fr/ On 18 mar, 03:17, Celia wrote: I have about 50+ worksheets in a file that *I need to sum. *However, I only want to sum some of the sheets if it meets a certain criteria. *This criteria is located in a separate worksheet in the file that lists each worksheet name in one column and in the next column it list the type of worksheet. *If a worksheet is of a certain type I need the sum of those types. Celia -- Celia |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum across worksheets conditionally | Excel Discussion (Misc queries) | |||
How do I conditionally format a row and copy the formatting to tab | Excel Worksheet Functions | |||
Conditionally summing data from two columns depending on their value. | Excel Worksheet Functions | |||
Conditionally summing cells based on conditions in other rows | Excel Worksheet Functions | |||
How can I conditionally link worksheets? | Excel Discussion (Misc queries) |