| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. . |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. . |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. . |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Function Difficulty | Excel Discussion (Misc queries) | |||
| Difficulty with IMPOWER() Worksheet Function | Excel Worksheet Functions | |||
| difficulty with Search function that incorporates multiple workshe | Excel Discussion (Misc queries) | |||
| Price Function - Difficulty in understanding the formula | Excel Worksheet Functions | |||
| IF Statement difficulty | Excel Worksheet Functions | |||
Linear Mode
