sum across worksheets conditionally
in [General]
|
Prev: SUMIF WITH OR
Next: How to change color within string?
From: Celia on 17 Mar 2010 22:17 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
From: JB on 18 Mar 2010 02:18 http://boisgontierjacques.free.fr/fichiers/Matriciel/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 <Ce...(a)discussions.microsoft.com> 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
From: JB on 18 Mar 2010 03:22 or =SumProduct(N(INDIRECT(nfCond&"!B2"))) JB On 18 mar, 07:18, JB <boisgont...(a)hotmail.com> wrote: > http://boisgontierjacques.free.fr/fichiers/Matriciel/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 <Ce...(a)discussions.microsoft.com> 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- Masquer le texte des messages précédents - > > - Afficher le texte des messages précédents -
From: Celia on 18 Mar 2010 09:10 Thank you so much. I am not sure if I completely understands how or why it works but I really appreciate your help. -- Celia "JB" wrote: > > http://boisgontierjacques.free.fr/fichiers/Matriciel/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 <Ce...(a)discussions.microsoft.com> 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 > > . >
From: Celia on 18 Mar 2010 15:10
I used the formula and it worked for one cell in the worksheet. THere is a whole bunch of data that I want to sum in each of the worksheets. How do I get the Cell B2 in your example to auto change as I copy the formula ex. so that I can sum b3, b4 ,b5, .... -- Celia "JB" wrote: > > http://boisgontierjacques.free.fr/fichiers/Matriciel/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 <Ce...(a)discussions.microsoft.com> 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 > > . > |