I agree with you! Excel help is sorely lacking in "real world" examples.

The best way to learn is to either spend a lot of time in forums like these
or get yourself a "good" book.

--
Biff
Microsoft Excel MVP


"Eddy Stan" wrote in message
...
hi
Excel is highly potential and i wonder is there any stop for imagination &
getting results !
i think MS is cheating excels users too much without putting these in
built
in help file, just kidding, pls dont add in regular, already except a few
no
one trys all functions and example. lol
joke aside. i really thank you for the formula

"T. Valko" wrote:

--('2009 Rollout'!E10:E1000)

What's in that range? If I understood your explanation it contains either
a
number 1 or is blank. If the blanks are formula blanks ("") then that
will
cause the error.

If the cells only contain the number 1 or are *empty* then you don't need
the double unary.

=SUMPRODUCT(--(ISNUMBER(MATCH('2009
Rollout'!A10:A1000,{1,2,3,4,5,6,7,11,12,13,17},0)) ),'2009
Rollout'!E10:E1000)


--
Biff
Microsoft Excel MVP


"Gmania-1980" wrote in message
...
Hi Jacob,

I tried your examples and it returned a #VALUE! error. Can the
SUMPRODUCT
function work across sheets? Here is the function I tried:

=SUMPRODUCT(--('2009 Rollout'!E10:E1000),--ISNUMBER(MATCH('2009
Rollout'!A10:A1000,{1,2,3,4,5,6,7,11,12,13,17},0)) )

I don't see a typo, but I may have made one. I also tried naming the
range
and it returned the same result.

Thanks.

"Jacob Skaria" wrote:

Best way would be to create named ranges for each district for example
enter
the ward numbers in J1:J11 and name this range as WestDis and and use
the
formula as

=SUMPRODUCT(--(E10:E1000),--ISNUMBER(MATCH(A10:A1000,westDis,0)))

PS: To name a range select the cells (say J1:J11) and enter the name
WestDis
in the name box which is just above cell A1

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Try

=SUMPRODUCT(--(E10:E1000),--ISNUMBER(MATCH(A10:A1000,{1,2,3,4,5,6,7,11,12,13,1 7},0)))

If this post helps click Yes
---------------
Jacob Skaria


"Gmania-1980" wrote:

I've been struggling to figure out the proper formula in Excel
2003
and could
use some help.

The spreadsheet I'm working on has a column of city ward numbers
(wards
range from 1 to 30 (A10:A1000), an address column (B10:B1000), and
three
columns for different transit shelter types (small (C10:C1000),
medium
(D10:D1000), and large (E10:E1000)). Under the ward column, a
ward
number
will appear multiple times (in other words, ward 1 may show up 30
times if 30
different addresses are present, ward 10 may show up 15 times,
etc).
The
address column contains different addresses and the transit
shelter
columns
are either blank or contain a 1. A 1 represents that 1 transit
shelter is
being installed at that address in that ward.

Here's what I'm trying to do. The 30 wards in the City make up
three
Districts. Life would be easier if a District was made up of
wards
in an
orderly fashion. Instead, for example, the West District
comprises
wards 1
to 7, 11 to 13 and 17.

I want to obtain a summary of all the large transit shelters in
the
West
District. I've been trying to do an array formula that says if
the
ward
number is between 1 and 7 and 11 and 13 and equal to 17, then
summarize
E10:E1000.

Basically the results I've been getting are either a sum of the
entire
column and not just the wards that make up the West District or a
0.

Help is greatly appreciated. Thank you.


.