Difficulty with Sum+IF Function
 

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

The spreadsheet Im 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.

Heres what Im 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. Ive 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 Ive 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.

Difficulty with Sum+IF Function
 

Try this

=SUMPRODUCT(--(A10:A1000=1),--(A10:A1000<=7),--(A10:A1000=11),--(A10:A1000<=13),--(A10:A1000=17),E10:E1000)

If this helps, please click "Yes"
<<<<<<<<<<<


"Gmania-1980" wrote:

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

The spreadsheet Im 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.

Heres what Im 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. Ive 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 Ive 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.

Difficulty with Sum+IF Function
 

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:

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

The spreadsheet Im 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.

Heres what Im 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. Ive 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 Ive 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.

Difficulty with Sum+IF Function
 

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:

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

The spreadsheet Im 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.

Heres what Im 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. Ive 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 Ive 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.

Difficulty with Sum+IF Function
 

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:

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

The spreadsheet Im 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.

Heres what Im 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. Ive 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 Ive 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.

Difficulty with Sum+IF Function
 

--('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.

Difficulty with Sum+IF Function
 

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

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


"Gmania-1980" wrote:

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:

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

The spreadsheet Im 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.

Heres what Im 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. Ive 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 Ive 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.

Difficulty with Sum+IF Function
 

This worked beautifully:

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

Thanks everyone for your help! Very much appreciated.

"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.


.

Difficulty with Sum+IF Function
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Gmania-1980" wrote in message
...
This worked beautifully:

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

Thanks everyone for your help! Very much appreciated.

"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.


.

Difficulty with Sum+IF Function
 

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.


.

Difficulty with Sum+IF Function
 

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.


.