Sumproduct Help Needed
 

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


--
Timmy Mac1
------------------------------------------------------------------------
Timmy Mac1's Profile: http://www.excelforum.com/member.php...o&userid=15188
View this thread: http://www.excelforum.com/showthread...hreadid=504885

Sumproduct Help Needed
 

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Timmy Mac1" wrote
in message ...

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


--
Timmy Mac1
------------------------------------------------------------------------
Timmy Mac1's Profile:
http://www.excelforum.com/member.php...o&userid=15188
View this thread: http://www.excelforum.com/showthread...hreadid=504885

Sumproduct Help Needed
 

Bob

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


--
Timmy Mac1
------------------------------------------------------------------------
Timmy Mac1's Profile: http://www.excelforum.com/member.php...o&userid=15188
View this thread: http://www.excelforum.com/showthread...hreadid=504885

Sumproduct Help Needed
 

In essence all it does is tot compare all values in A2:A10 rather than just
1.

Did you try it and see if it worked?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Timmy Mac1" wrote
in message ...

Bob

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


--
Timmy Mac1
------------------------------------------------------------------------
Timmy Mac1's Profile:
http://www.excelforum.com/member.php...o&userid=15188
View this thread: http://www.excelforum.com/showthread...hreadid=504885

Sumproduct Help Needed
 

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


--
Timmy Mac1
------------------------------------------------------------------------
Timmy Mac1's Profile: http://www.excelforum.com/member.php...o&userid=15188
View this thread: http://www.excelforum.com/showthread...hreadid=504885