Please help w/Sumproduct!

[Guest]

Hi,

Looking for a Sumproduct formula that will add the quantities in Col. C that
have (A in Col. A & "whatever" in Col. B) + Only those Quantities in Col. C
that have (Blanks in Col. A & Non-Blanks in Col. B)

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

Eg: 20 + 30 + 20 = 70

Thanks in advance,
bkt

 

[Franz Verga]

Hi,

Looking for a Sumproduct formula that will add the quantities in Col.
C that have (A in Col. A & "whatever" in Col. B) + Only those
Quantities in Col. C that have (Blanks in Col. A & Non-Blanks in Col.
B)

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

Eg: 20 + 30 + 20 = 70

Thanks in advance,
bkt

Hi bkt,

try this:

=SUMPRODUCT((A1:A6="A")*C1:C6)+SUMPRODUCT((A1:A6="Blank")*(B1:B6="Non-Blank")*C1:C6)

this work if you have the words "Blank" and "Non-Blank" (without quotes) in
column A and B, but if you mean empty and not empty cells, you have to use
this one:

=SUMPRODUCT((A1:A6="A")*C1:C6)+SUMPRODUCT((A1:A6="")*(B1:B6<>"")*C1:C6)


--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[shail]

Hi again

I guess Franz Verga's 2nd formula will work for you

=SUMPRODUCT((A1:A6="A")*C1:C6)+SUMPRODUCT((A1:A6="")*(B1:B6<>"")*C1:C6)


Thanks,

Shail