G
Gmania-1980
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
(D101000), 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.
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
(D101000), 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.