Thread: Embedding a Sumif in a sumif

Attempted to embed a sumif into a sumif. Formula
=sumif(Gift,1,sumif(PerPay,"<>0",Children)) returns message "The formula you
types contains an error."

Need to sum the range "Children" based on criteria in both the "Gift" and
"PerPay" colums. Suggentions? Alternatives?

.. . . Cheers & Thank You
C.Pflugrath

maybe..

=SUMPRODUCT(--(gift=1),--(perpay<>0),children)


"C.Pflugrath" wrote:

> Attempted to embed a sumif into a sumif. Formula
> =sumif(Gift,1,sumif(PerPay,"<>0",Children)) returns message "The formula you
> types contains an error."
>
> Need to sum the range "Children" based on criteria in both the "Gift" and
> "PerPay" colums. Suggentions? Alternatives?
>
> . . . Cheers & Thank You
> C.Pflugrath

C,

=SUMPRODUCT((Gift=1)*(PerPay<>0)*Children)

HTH,
Bernie
MS Excel MVP


"C.Pflugrath" <CPflugrath@discussions.microsoft.com> wrote in message
news:D2C26FB4-CB55-450F-B23C-C4ED28A5765A@microsoft.com...
> Attempted to embed a sumif into a sumif. Formula
> =sumif(Gift,1,sumif(PerPay,"<>0",Children)) returns message "The formula you
> types contains an error."
>
> Need to sum the range "Children" based on criteria in both the "Gift" and
> "PerPay" colums. Suggentions? Alternatives?
>
> . . . Cheers & Thank You
> C.Pflugrath

=SUMPRODUCT(--(Gift=1),--(PerPay<>0),Children)

--
Regards,

Peo Sjoblom

(No private emails please)


"C.Pflugrath" <CPflugrath@discussions.microsoft.com> wrote in message
news:D2C26FB4-CB55-450F-B23C-C4ED28A5765A@microsoft.com...
> Attempted to embed a sumif into a sumif. Formula
> =sumif(Gift,1,sumif(PerPay,"<>0",Children)) returns message "The formula
> you
> types contains an error."
>
> Need to sum the range "Children" based on criteria in both the "Gift" and
> "PerPay" colums. Suggentions? Alternatives?
>
> . . . Cheers & Thank You
> C.Pflugrath

One way
=SUMPRODUCT(--(Gift=1),--(PerPay<>0),Children)
This assumes the ranges are of equal size.

--
Regards

Roger Govier


"C.Pflugrath" <CPflugrath@discussions.microsoft.com> wrote in message
news:D2C26FB4-CB55-450F-B23C-C4ED28A5765A@microsoft.com...
> Attempted to embed a sumif into a sumif. Formula
> =sumif(Gift,1,sumif(PerPay,"<>0",Children)) returns message "The formula
> you
> types contains an error."
>
> Need to sum the range "Children" based on criteria in both the "Gift" and
> "PerPay" colums. Suggentions? Alternatives?
>
> . . . Cheers & Thank You
> C.Pflugrath

Fabolous! Thank You!
--
.. . . Cheers
C.Pflugrath


"Duke Carey" wrote:

> maybe..
>
> =SUMPRODUCT(--(gift=1),--(perpay<>0),children)
>
>
> "C.Pflugrath" wrote:
>
> > Attempted to embed a sumif into a sumif. Formula
> > =sumif(Gift,1,sumif(PerPay,"<>0",Children)) returns message "The formula you
> > types contains an error."
> >
> > Need to sum the range "Children" based on criteria in both the "Gift" and
> > "PerPay" colums. Suggentions? Alternatives?
> >
> > . . . Cheers & Thank You
> > C.Pflugrath