Sumproduct Help Needed

T

Timmy Mac1

I've got a spreadsheet set up with

X = a defined type

y = a defined location

Z = a number pertinent to the location and type.


I have it set up in such a way that I can select my location in a cell
A1 with data validation and then for each type I will get a number that
relates to the selected location.

so my formula is along the lines of...

A2. A10 are lists of types

and B2 will have -=sumproduct((x=A2)*(y=$A$1),z)-

What I want to know is if there is any way I can have the formula
return the amount for all locations as an option, without creating a
separate formula omitting the -y=$A$2- part.

In other words is there some sort of text string which excel will see
as a match for all the locations I have listed?

hope this explains what I'm trying to achieve and thanks for any
assistance out there.

tm
 
B

Bob Phillips

=SUMPRODUCT(--(ISNUMBER(MATCH(x,A2:A10,0))),--(y=$A$1),z)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
T

Timmy Mac1

Bob

Thanks very much for your response, but I must confess I'm flounderin
in trying to understand your formula. In the meantime I've worked
long way around! I've added a location for total and included it on m
data validation list
 
T

Timmy Mac1

Thanks again Bob

I will give it a go when I've got time.

I was trying to understand the sense of what it was trying to do by
looking at it, but couldn't get it right in my head. Like most things
though, that generally comes when I've ploughed through the example :)
 
Top