Difficulty with Sum+IF Function

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

 

[BSc Chem Eng Rick]

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

 

[Jacob Skaria]

Try

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

If this post helps click Yes

 

[Jacob Skaria]

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

 

[Gmania-1980]

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.

 

[T. Valko]

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

 

[Jacob Skaria]

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

 

[Gmania-1980]

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]

You're welcome. Thanks for the feedback!

 

[Eddy Stan]

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]

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.