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:C 7))

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="No n-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")+(rng25)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" wrote in message
...
Can anyone let me know the syntax for including an "OR" criteria inside
SUMPRODUCT (Sumif) function.