SUMIF? or not?

[Scott A]

Hello,

I have 3 columns, the first with a rating of 1-10 in each row, the second is
blank and the trird has a description for the rating. When someone picks a
rating they place a "1" in the second row cell next to the 1-10 rating. I
would like the cell at the bottom of the second row to look at all the cells
and see which one has a 1 in it and then look to the cell to the left and
report that number. What formula doe this?

Example:

A B C
1 .5 Day
2 1 day
3 2-3 Days
4 4-6 Days
5 7-8 Days
6 2-3 Weeks
7 3-4 Weeks
8 1 4-6 Weeks
9 5-8 Weeks
10 More than 8 weeks
8 Total Points

 

[JLatham]

=SUMIF(B1:B10,1,A1:A10)
will give you the total from column A for all entries where there is a 1 in
column B next to it. If there's only a single 1, then you get that row's
value from A; and if more than a single 1, you get a total of all with 1 in B.

If there's a chance of more than a single 1 being in column B and you want
to get the value from A associated with the first one, you can use either of
these (first allows you to change values in A and pick up proper value,
second one works because your values are simple 1-10 values).
=INDEX(A1:A10,MATCH(1,B1:B10,-1),1)
or as long as values are also 1, 2, 3, 4, 5, 6, 7, 8, 9 and 10:
=MATCH(1,B1:B10,-1)

 

[teylyn]

Hi Scott,

if I understand your requirement correctly, this should work

=INDEX(A1:A10,MATCH(1,B1:B10,0))

cheers, teylyn