Sum if with multiple conditions / Arrays

[Scott]

Hello,

BACKGROUND:
I can really use some help to do a sumif function for
multiple conditions using array formulas. For example, in
the range A1:B2, I have sales data and I want to sum up
all sales for Smith, in January, to the Northwest region.
Needless to say, this is a simplified example.

Col A: Name
Col B: Month
Col C: Region
Col D: Sales

PROBLEM:
Smith Jan NW $200
Smith Feb SW $100

Using the array formula ={sum((A1:A2="Smith)*(B1:B2="Jan")
*(C1:C2="NW")*(A1:A2))} I am getting the response of $400
as opposed to $200. It looks like the Boolean operator is
reading "Smith" twice so that the outcome of this formula
is (2x1x1x$200) as opposed to (1x1x1x$200). I've tried to
modify the formula and then I'll get the error "#Value."
Any ideas?

Thanks in advance.

-Scott

 

[Domenic]

Hi,

Try,

=SUM(IF((A1:A2="Smith")*(B1:B2="Jan")*(C1:C2="NW"),D12))

Hope this helps!

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(A1:A100="Smith"),--(B1:B100="Jan"),--(C1:C100="NW"),D1
100)

 

[Trevor Shuttleworth]

Scott

I don't think what you have is actually array-entered. Try this:

=SUMPRODUCT((A1:A2="Smith")*(B1:B2="Jan")*(C1:C2="NW")*(D12))

Regards

Trevor

 

[Aladin Akyurek]

Tere is a typo in the formula (missing a double quote around Smith) and the
range to sum should be D12...

=SUM((A1:A2="Smith")*(B1:B2="Jan")*(C1:C2="NW")*(D12))

You need to confirm this formula with control+shift+enter instead of just
with enter.