sumproduct

  • Thread starter Thread starter tlee
  • Start date Start date
T

tlee

i have a spreadsheet that look like the following
a b c
name code 2004 sales
alaska c 30000
alaska d 6000
arizona d 50000


Everytime there is alaska and c i want the 2004 sales.

My formula is as follow

sumproduct(((a1:a20)= a1)*(b1:b20)=b1)*(c1:c20)))

I get a #value.

am i doing something wrong.
 
Hi

you can't use a criteria embedded in the range you're searching
try
=SUMPRODUCT((A2:A20="alaska")*(B2:B20="c")*C2:C20)
or with alaska in D1, c in E1
=SUMPRODUCT((A2:A20=D1)*(B2:B20=E1)*C2:C20)

Cheers
JulieD
 
I think you have two troubles:

First your ()'s are mismatched:
=sumproduct(((a1:a20)= a1)*(b1:b20)=b1)*(c1:c20)))

maybe...
=SUMPRODUCT((A1:A20=A1)*(B1:B20=B1)*(C1:C20))

And if A1 is really a header row ("2004 sales" in C1), then it should probably
be skipped in your formula:

=SUMPRODUCT((A2:A20=A1)*(B2:B20=B1)*(C2:C20))

But I'm not sure what you really have in A1, B1, or C1.
 
Get rid of the reference to row 1. You cannot include text headers if you are
looking to sum values from one of the ranges. Also you are referring to cell
references that are inside your ranges and they contain "name" and "code" not
"Alaska" and "c". Try this:-

SUMPRODUCT((A2:A20=E1)*(B2:B20=E2)*(C2:C20)) assuming you have the text
"Alaska" in E1 and "c" in E2
 
Hi

please could you copy & paste your exact formulas ... as the ones i provided
work when i tested them. Additionally, are you sure that there are no
leading or trailing spaces around alaska & c?

Cheers
JulieD
 
Hi,

Have you activated add-inn Analysis Toolpack?

Some funtions are only recognized after activatimg the AnalysisToolpack
add-inn

HTH

Syb
 
Some funtions are only recognized after activatimg the AnalysisToolpack

Not this one though, and had the OP not added the = sign then the formula would
have appeared as text. :-)
 
Back
Top