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

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.

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.

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

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

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

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

=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.
> > > >
> > > >
> > > >
> >
> >

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

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