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 |
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) |