Sums With Ranges

[kc68]

I have a table in which each column represents a range of dollars. An
x in the row means that the person is in that range. I want to see if
there is a compact means of getting a column with the sum of the low
number in each preceding column and a sum of the high end of the range
for those columns.

For instance, there may be an x in a row where the column is
1000-15000, an x in the next column where the range is 15001-50000 and
a blank for the column 50001-100000. Thus the sum column for low would
show 16001 and the sum column for high would show 65000.

I know that I can create columns like =IF(A2="x",1001,0) and then sum
these columns in another column. Then do a similar formula for the
maximum. Is there a simpler way where a single formula would
accomplish the goal? There are 9 columns. Thanks.

Ken

 

[Guest]

If you can split the column header in two rows (1000-15000 in A1 to 1000 in
A1, 15000 in A2) these formulae can do what you want:
min: =SUMIF(A3:F3,"x",A1:F1)
max: =SUMIF(A3:F3,"x",A2:F2)

Hope this helps,
Miguel.

 

[kc68]

Not quite there, but close (the formula doesn't compute, but that's not
the layout).

The rows (say a1 to a8) would each have a person's name. The columns
(b2 to b7) would each show a dollar figure, e.g. 1000, 15000, 15001,
50000, 50001, 100000

If a person has 1000-15000 and 15001-50000, there would be an x in both
b2 and c2 and in both d2 and e2. The sum for minimum would be b1+d1. .
..
and the sum for maximum would be c1+e1. . .

I can't quite articulate your formulas, but I think it's a simple
change. Thanks.

 

[Guest]

Sorry, with that layout I cannot think on a formula simpler than:
=IF(B2="x",$B$1,0)+IF(D2="x",$D$1,0)+IF(F2="x",$F$1,0)
The fact that there are no continuos ranges to check prevents the usage of
the sumif or sumproduct in a simple way.

Miguel.

 

[kc68]

That's an advance. It's essentially my solution but splitting the
range to max and min plus the single formula avoids multiple columns
plus a sum column. And I can drag the single formula (and the one for
maximum). Thanks again.

Ken

 

[kc68]

Actually, one more step: if instead of x I have a number, what formula
do I use for the sum of the products? e.g. b2 is 3 and d2 is 2 I want
$33002 (=$3000+$30002)