# Sumproduct (Sumif) with Nested Or Criteria

=?Utf-8?B?Ymt0?=
Guest
Posts: n/a

 1st Sep 2006
Can anyone let me know the syntax for including an "OR" criteria inside
SUMPRODUCT (Sumif) function.

Andy Wiggins
Guest
Posts: n/a

 1st Sep 2006
This link might be of help:

http://www.bygsoftware.com/Excel/fun...sumproduct.htm

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"bkt" <(E-Mail Removed)> wrote in message
news:8FD618A2-0F18-472B-A016-(E-Mail Removed)...
> Can anyone let me know the syntax for including an "OR" criteria inside
> SUMPRODUCT (Sumif) function.

Bob Phillips
Guest
Posts: n/a

 1st Sep 2006
Depends on what type of or.

For a simple count of = A or = A

=SUMPRODUCT(--(rng1={"A","B"})

To sum another range with that simple OR

=SUMPRODUCT((rng1={"A","B"})*rng3)

However, if you want, if rng1 = A OR rng2 >5 say, then for a SUM of rng3
meeting these criteria,

=SUMPRODUCT(((rng1="A")+(rng2>5)>0)*rng3)

you have to be careful not to double count here, when rng1 = A and rng2 >5,
hence the > 0 test

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"bkt" <(E-Mail Removed)> wrote in message
news:8FD618A2-0F18-472B-A016-(E-Mail Removed)...
> Can anyone let me know the syntax for including an "OR" criteria inside
> SUMPRODUCT (Sumif) function.

=?Utf-8?B?Ymt0?=
Guest
Posts: n/a

 1st Sep 2006
Thanks, Gents.

Actually what I am looking for is as below

Col. A Col. B Col. C
F Blank 10
A Non-Blank 20
Blank Non-Blank 30
F Blank 40
A Non-Blank 30
Blank Blank 20

looking for a Sumproduct formula that will add the quantities in Col. C that
has A in Col. A + Only those Quantities in Col. C that have Blanks in Col. A
and have Non-Blanks in Col. B

Eg: 20 + 30 + 30 = 80

bkt

"Bob Phillips" wrote:

> Depends on what type of or.
>
> For a simple count of = A or = A
>
> =SUMPRODUCT(--(rng1={"A","B"})
>
> To sum another range with that simple OR
>
> =SUMPRODUCT((rng1={"A","B"})*rng3)
>
> However, if you want, if rng1 = A OR rng2 >5 say, then for a SUM of rng3
> meeting these criteria,
>
> =SUMPRODUCT(((rng1="A")+(rng2>5)>0)*rng3)
>
> you have to be careful not to double count here, when rng1 = A and rng2 >5,
> hence the > 0 test
>
> See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
> explanation.
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "bkt" <(E-Mail Removed)> wrote in message
> news:8FD618A2-0F18-472B-A016-(E-Mail Removed)...
> > Can anyone let me know the syntax for including an "OR" criteria inside
> > SUMPRODUCT (Sumif) function.

>
>
>

shail
Guest
Posts: n/a

 1st Sep 2006
Hi,

Assuming you have the data from A1 till C7

=sumproduct((A2:A7="A")*(A2:A7="Blank")*(B2:B7="Non-Blanks")*(C2:C7))

Hope this will work for you.

Thanks

Shail

bkt wrote:
> Thanks, Gents.
>
> Actually what I am looking for is as below
>
> Col. A Col. B Col. C
> F Blank 10
> A Non-Blank 20
> Blank Non-Blank 30
> F Blank 40
> A Non-Blank 30
> Blank Blank 20
>
> looking for a Sumproduct formula that will add the quantities in Col. C that
> has A in Col. A + Only those Quantities in Col. C that have Blanks in Col. A
> and have Non-Blanks in Col. B
>
> Eg: 20 + 30 + 30 = 80
>
> bkt
>
> "Bob Phillips" wrote:
>
> > Depends on what type of or.
> >
> > For a simple count of = A or = A
> >
> > =SUMPRODUCT(--(rng1={"A","B"})
> >
> > To sum another range with that simple OR
> >
> > =SUMPRODUCT((rng1={"A","B"})*rng3)
> >
> > However, if you want, if rng1 = A OR rng2 >5 say, then for a SUM of rng3
> > meeting these criteria,
> >
> > =SUMPRODUCT(((rng1="A")+(rng2>5)>0)*rng3)
> >
> > you have to be careful not to double count here, when rng1 = A and rng2 >5,
> > hence the > 0 test
> >
> > See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
> > explanation.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "bkt" <(E-Mail Removed)> wrote in message
> > news:8FD618A2-0F18-472B-A016-(E-Mail Removed)...
> > > Can anyone let me know the syntax for including an "OR" criteria inside
> > > SUMPRODUCT (Sumif) function.

> >
> >
> >

=?Utf-8?B?Ymt0?=
Guest
Posts: n/a

 1st Sep 2006
Maybe I wasn't clear with my question, but Col. B doesn't have to be
Non-Blanks all the time.

The quantity I am looking for is Col A = A, Col. B = Whatever + Col. A =
Blank, Col. B = Non-Blanks

Thanks,
bkt

"shail" wrote:

> Hi,
>
> Assuming you have the data from A1 till C7
>
> =sumproduct((A2:A7="A")*(A2:A7="Blank")*(B2:B7="Non-Blanks")*(C2:C7))
>
> Hope this will work for you.
>
> Thanks
>
> Shail
>
>
> bkt wrote:
> > Thanks, Gents.
> >
> > Actually what I am looking for is as below
> >
> > Col. A Col. B Col. C
> > F Blank 10
> > A Non-Blank 20
> > Blank Non-Blank 30
> > F Blank 40
> > A Non-Blank 30
> > Blank Blank 20
> >
> > looking for a Sumproduct formula that will add the quantities in Col. C that
> > has A in Col. A + Only those Quantities in Col. C that have Blanks in Col. A
> > and have Non-Blanks in Col. B
> >
> > Eg: 20 + 30 + 30 = 80
> >
> > bkt
> >
> > "Bob Phillips" wrote:
> >
> > > Depends on what type of or.
> > >
> > > For a simple count of = A or = A
> > >
> > > =SUMPRODUCT(--(rng1={"A","B"})
> > >
> > > To sum another range with that simple OR
> > >
> > > =SUMPRODUCT((rng1={"A","B"})*rng3)
> > >
> > > However, if you want, if rng1 = A OR rng2 >5 say, then for a SUM of rng3
> > > meeting these criteria,
> > >
> > > =SUMPRODUCT(((rng1="A")+(rng2>5)>0)*rng3)
> > >
> > > you have to be careful not to double count here, when rng1 = A and rng2 >5,
> > > hence the > 0 test
> > >
> > > See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
> > > explanation.
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace somewhere in email address with gmail if mailing direct)
> > >
> > > "bkt" <(E-Mail Removed)> wrote in message
> > > news:8FD618A2-0F18-472B-A016-(E-Mail Removed)...
> > > > Can anyone let me know the syntax for including an "OR" criteria inside
> > > > SUMPRODUCT (Sumif) function.
> > >
> > >
> > >

>
>

shail
Guest
Posts: n/a

 1st Sep 2006
Hi again,

Better you make a drop down list below each column for the criteria you
want to pass for SUMPRODUCT.
This will make your SUMPRODUCT to work Dynamic. For this re-write your
formula for the SUMPRODUCT as below. Assuming your dropdown lists are
at A9, B9 and C9.

=sumproduct((A2:A7=A9)*(A2:A7=B9)*(B2:B7=C9)*(C2:C7))

Hope this will work for you.

Thanks,

Shail

bkt wrote:
> Maybe I wasn't clear with my question, but Col. B doesn't have to be
> Non-Blanks all the time.
>
> The quantity I am looking for is Col A = A, Col. B = Whatever + Col. A =
> Blank, Col. B = Non-Blanks
>
> Thanks,
> bkt
>
> "shail" wrote:
>
> > Hi,
> >
> > Assuming you have the data from A1 till C7
> >
> > =sumproduct((A2:A7="A")*(A2:A7="Blank")*(B2:B7="Non-Blanks")*(C2:C7))
> >
> > Hope this will work for you.
> >
> > Thanks
> >
> > Shail
> >
> >
> > bkt wrote:
> > > Thanks, Gents.
> > >
> > > Actually what I am looking for is as below
> > >
> > > Col. A Col. B Col. C
> > > F Blank 10
> > > A Non-Blank 20
> > > Blank Non-Blank 30
> > > F Blank 40
> > > A Non-Blank 30
> > > Blank Blank 20
> > >
> > > looking for a Sumproduct formula that will add the quantities in Col. C that
> > > has A in Col. A + Only those Quantities in Col. C that have Blanks in Col. A
> > > and have Non-Blanks in Col. B
> > >
> > > Eg: 20 + 30 + 30 = 80
> > >
> > > Thanks in advance,
> > > bkt
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Depends on what type of or.
> > > >
> > > > For a simple count of = A or = A
> > > >
> > > > =SUMPRODUCT(--(rng1={"A","B"})
> > > >
> > > > To sum another range with that simple OR
> > > >
> > > > =SUMPRODUCT((rng1={"A","B"})*rng3)
> > > >
> > > > However, if you want, if rng1 = A OR rng2 >5 say, then for a SUM of rng3
> > > > meeting these criteria,
> > > >
> > > > =SUMPRODUCT(((rng1="A")+(rng2>5)>0)*rng3)
> > > >
> > > > you have to be careful not to double count here, when rng1 = A and rng2 >5,
> > > > hence the > 0 test
> > > >
> > > > See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
> > > > explanation.
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (replace somewhere in email address with gmail if mailing direct)
> > > >
> > > > "bkt" <(E-Mail Removed)> wrote in message
> > > > news:8FD618A2-0F18-472B-A016-(E-Mail Removed)...
> > > > > Can anyone let me know the syntax for including an "OR" criteria inside
> > > > > SUMPRODUCT (Sumif) function.
> > > >
> > > >
> > > >

> >
> >

Bob Phillips
Guest
Posts: n/a

 1st Sep 2006
=SUMPRODUCT(((A2:A10="A")+((A2:A10="")*(B2:B10<>"")))*C2:C10)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"bkt" <(E-Mail Removed)> wrote in message
news:172E6850-17FD-44FC-A248-(E-Mail Removed)...
> Maybe I wasn't clear with my question, but Col. B doesn't have to be
> Non-Blanks all the time.
>
> The quantity I am looking for is Col A = A, Col. B = Whatever + Col. A =
> Blank, Col. B = Non-Blanks
>
> Thanks,
> bkt
>
> "shail" wrote:
>
> > Hi,
> >
> > Assuming you have the data from A1 till C7
> >
> > =sumproduct((A2:A7="A")*(A2:A7="Blank")*(B2:B7="Non-Blanks")*(C2:C7))
> >
> > Hope this will work for you.
> >
> > Thanks
> >
> > Shail
> >
> >
> > bkt wrote:
> > > Thanks, Gents.
> > >
> > > Actually what I am looking for is as below
> > >
> > > Col. A Col. B Col. C
> > > F Blank 10
> > > A Non-Blank 20
> > > Blank Non-Blank 30
> > > F Blank 40
> > > A Non-Blank 30
> > > Blank Blank 20
> > >
> > > looking for a Sumproduct formula that will add the quantities in Col.

C that
> > > has A in Col. A + Only those Quantities in Col. C that have Blanks in

Col. A
> > > and have Non-Blanks in Col. B
> > >
> > > Eg: 20 + 30 + 30 = 80
> > >
> > > Thanks in advance,
> > > bkt
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Depends on what type of or.
> > > >
> > > > For a simple count of = A or = A
> > > >
> > > > =SUMPRODUCT(--(rng1={"A","B"})
> > > >
> > > > To sum another range with that simple OR
> > > >
> > > > =SUMPRODUCT((rng1={"A","B"})*rng3)
> > > >
> > > > However, if you want, if rng1 = A OR rng2 >5 say, then for a SUM of

rng3
> > > > meeting these criteria,
> > > >
> > > > =SUMPRODUCT(((rng1="A")+(rng2>5)>0)*rng3)
> > > >
> > > > you have to be careful not to double count here, when rng1 = A and

rng2 >5,
> > > > hence the > 0 test
> > > >
> > > > See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a

detailed
> > > > explanation.
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (replace somewhere in email address with gmail if mailing direct)
> > > >
> > > > "bkt" <(E-Mail Removed)> wrote in message
> > > > news:8FD618A2-0F18-472B-A016-(E-Mail Removed)...
> > > > > Can anyone let me know the syntax for including an "OR" criteria

inside
> > > > > SUMPRODUCT (Sumif) function.
> > > >
> > > >
> > > >

> >
> >

=?Utf-8?B?Ymt0?=
Guest
Posts: n/a

 5th Sep 2006
Hi Shail,

I talk of Sumif and you talk of Countif.

Cheers,
bkt

"shail" wrote:

> Hi again,
>
> Better you make a drop down list below each column for the criteria you
> want to pass for SUMPRODUCT.
> This will make your SUMPRODUCT to work Dynamic. For this re-write your
> formula for the SUMPRODUCT as below. Assuming your dropdown lists are
> at A9, B9 and C9.
>
> =sumproduct((A2:A7=A9)*(A2:A7=B9)*(B2:B7=C9)*(C2:C7))
>
> Hope this will work for you.
>
> Thanks,
>
> Shail
>
>
> bkt wrote:
> > Maybe I wasn't clear with my question, but Col. B doesn't have to be
> > Non-Blanks all the time.
> >
> > The quantity I am looking for is Col A = A, Col. B = Whatever + Col. A =
> > Blank, Col. B = Non-Blanks
> >
> > Thanks,
> > bkt
> >
> > "shail" wrote:
> >
> > > Hi,
> > >
> > > Assuming you have the data from A1 till C7
> > >
> > > =sumproduct((A2:A7="A")*(A2:A7="Blank")*(B2:B7="Non-Blanks")*(C2:C7))
> > >
> > > Hope this will work for you.
> > >
> > > Thanks
> > >
> > > Shail
> > >
> > >
> > > bkt wrote:
> > > > Thanks, Gents.
> > > >
> > > > Actually what I am looking for is as below
> > > >
> > > > Col. A Col. B Col. C
> > > > F Blank 10
> > > > A Non-Blank 20
> > > > Blank Non-Blank 30
> > > > F Blank 40
> > > > A Non-Blank 30
> > > > Blank Blank 20
> > > >
> > > > looking for a Sumproduct formula that will add the quantities in Col. C that
> > > > has A in Col. A + Only those Quantities in Col. C that have Blanks in Col. A
> > > > and have Non-Blanks in Col. B
> > > >
> > > > Eg: 20 + 30 + 30 = 80
> > > >
> > > > Thanks in advance,
> > > > bkt
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > Depends on what type of or.
> > > > >
> > > > > For a simple count of = A or = A
> > > > >
> > > > > =SUMPRODUCT(--(rng1={"A","B"})
> > > > >
> > > > > To sum another range with that simple OR
> > > > >
> > > > > =SUMPRODUCT((rng1={"A","B"})*rng3)
> > > > >
> > > > > However, if you want, if rng1 = A OR rng2 >5 say, then for a SUM of rng3
> > > > > meeting these criteria,
> > > > >
> > > > > =SUMPRODUCT(((rng1="A")+(rng2>5)>0)*rng3)
> > > > >
> > > > > you have to be careful not to double count here, when rng1 = A and rng2 >5,
> > > > > hence the > 0 test
> > > > >
> > > > > See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
> > > > > explanation.
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > (replace somewhere in email address with gmail if mailing direct)
> > > > >
> > > > > "bkt" <(E-Mail Removed)> wrote in message
> > > > > news:8FD618A2-0F18-472B-A016-(E-Mail Removed)...
> > > > > > Can anyone let me know the syntax for including an "OR" criteria inside
> > > > > > SUMPRODUCT (Sumif) function.
> > > > >
> > > > >
> > > > >
> > >
> > >

>
>

shail
Guest
Posts: n/a

 5th Sep 2006
Hi friend,

No it is doing sumif. I did it here over my PC with the example you
gave me and the formula I have posted.

Do let me know if it is not working with you.

Thanks,

Shail

bkt wrote:
> Hi Shail,
>
> I talk of Sumif and you talk of Countif.
>
> Cheers,
> bkt
>
> "shail" wrote:
>
> > Hi again,
> >
> > Better you make a drop down list below each column for the criteria you
> > want to pass for SUMPRODUCT.
> > This will make your SUMPRODUCT to work Dynamic. For this re-write your
> > formula for the SUMPRODUCT as below. Assuming your dropdown lists are
> > at A9, B9 and C9.
> >
> > =sumproduct((A2:A7=A9)*(A2:A7=B9)*(B2:B7=C9)*(C2:C7))
> >
> > Hope this will work for you.
> >
> > Thanks,
> >
> > Shail
> >
> >
> > bkt wrote:
> > > Maybe I wasn't clear with my question, but Col. B doesn't have to be
> > > Non-Blanks all the time.
> > >
> > > The quantity I am looking for is Col A = A, Col. B = Whatever + Col. A =
> > > Blank, Col. B = Non-Blanks
> > >
> > > Thanks,
> > > bkt
> > >
> > > "shail" wrote:
> > >
> > > > Hi,
> > > >
> > > > Assuming you have the data from A1 till C7
> > > >
> > > > =sumproduct((A2:A7="A")*(A2:A7="Blank")*(B2:B7="Non-Blanks")*(C2:C7))
> > > >
> > > > Hope this will work for you.
> > > >
> > > > Thanks
> > > >
> > > > Shail
> > > >
> > > >
> > > > bkt wrote:
> > > > > Thanks, Gents.
> > > > >
> > > > > Actually what I am looking for is as below
> > > > >
> > > > > Col. A Col. B Col. C
> > > > > F Blank 10
> > > > > A Non-Blank 20
> > > > > Blank Non-Blank 30
> > > > > F Blank 40
> > > > > A Non-Blank 30
> > > > > Blank Blank 20
> > > > >
> > > > > looking for a Sumproduct formula that will add the quantities in Col. C that
> > > > > has A in Col. A + Only those Quantities in Col. C that have Blanks in Col. A
> > > > > and have Non-Blanks in Col. B
> > > > >
> > > > > Eg: 20 + 30 + 30 = 80
> > > > >
> > > > > Thanks in advance,
> > > > > bkt
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > Depends on what type of or.
> > > > > >
> > > > > > For a simple count of = A or = A
> > > > > >
> > > > > > =SUMPRODUCT(--(rng1={"A","B"})
> > > > > >
> > > > > > To sum another range with that simple OR
> > > > > >
> > > > > > =SUMPRODUCT((rng1={"A","B"})*rng3)
> > > > > >
> > > > > > However, if you want, if rng1 = A OR rng2 >5 say, then for a SUM of rng3
> > > > > > meeting these criteria,
> > > > > >
> > > > > > =SUMPRODUCT(((rng1="A")+(rng2>5)>0)*rng3)
> > > > > >
> > > > > > you have to be careful not to double count here, when rng1 = A and rng2 >5,
> > > > > > hence the > 0 test
> > > > > >
> > > > > > See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
> > > > > > explanation.
> > > > > >
> > > > > > --
> > > > > > HTH
> > > > > >
> > > > > > Bob Phillips
> > > > > >
> > > > > > (replace somewhere in email address with gmail if mailing direct)
> > > > > >
> > > > > > "bkt" <(E-Mail Removed)> wrote in message
> > > > > > news:8FD618A2-0F18-472B-A016-(E-Mail Removed)...
> > > > > > > Can anyone let me know the syntax for including an "OR" criteria inside
> > > > > > > SUMPRODUCT (Sumif) function.
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >

> >
> >

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post diablo Microsoft Excel Discussion 2 11th Dec 2007 09:58 PM =?Utf-8?B?c2NvdXNlcmFiYml0?= Microsoft Excel Worksheet Functions 5 2nd Mar 2007 05:03 PM Benny Raymond Microsoft C# .NET 1 28th Oct 2005 07:06 AM Jim Hunt Microsoft ADO .NET 0 5th Oct 2004 05:13 PM andycharger Microsoft Excel Programming 2 25th Feb 2004 04:58 PM

Features