From: Celia on
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

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
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
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
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
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: SUMIF WITH OR
Next: How to change color within string?