Sumproduct (Sumif) with Nested Or Criteria

B

bkt

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

Bob Phillips

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)
 
B

bkt

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
 
S

shail

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
 
B

bkt

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
 
S

shail

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
 
B

Bob Phillips

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

--
HTH

Bob Phillips

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

shail

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
 
Top