software

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=quot;xquot;,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.

KenIf 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,quot;xquot;,A1:F1)
max: =SUMIF(A3:F3,quot;xquot;,A2:F2)

Hope this helps,
Miguel.

quot; wrote:

gt; I have a table in which each column represents a range of dollars. An
gt; x in the row means that the person is in that range. I want to see if
gt; there is a compact means of getting a column with the sum of the low
gt; number in each preceding column and a sum of the high end of the range
gt; for those columns.
gt;
gt; For instance, there may be an x in a row where the column is
gt; 1000-15000, an x in the next column where the range is 15001-50000 and
gt; a blank for the column 50001-100000. Thus the sum column for low would
gt; show 16001 and the sum column for high would show 65000.
gt;
gt; I know that I can create columns like =IF(A2=quot;xquot;,1001,0) and then sum
gt; these columns in another column. Then do a similar formula for the
gt; maximum. Is there a simpler way where a single formula would
gt; accomplish the goal? There are 9 columns. Thanks.
gt;
gt; Ken
gt;
gt;

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.Sorry, with that layout I cannot think on a formula simpler than:
=IF(B2=quot;xquot;,$B$1,0) IF(D2=quot;xquot;,$D$1,0) IF(F2=quot;xquot;,$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.

quot; wrote:

gt; Not quite there, but close (the formula doesn't compute, but that's not
gt; the layout).
gt;
gt; The rows (say a1 to a8) would each have a person's name. The columns
gt; (b2 to b7) would each show a dollar figure, e.g. 1000, 15000, 15001,
gt; 50000, 50001, 100000
gt;
gt; If a person has 1000-15000 and 15001-50000, there would be an x in both
gt; b2 and c2 and in both d2 and e2. The sum for minimum would be b1 d1. .
gt; ..
gt; and the sum for maximum would be c1 e1. . .
gt;
gt; I can't quite articulate your formulas, but I think it's a simple
gt; change. Thanks.
gt;
gt;

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.

KenActually, 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)