WHAT IS THE RIGHT FUNCTION ?

[Terrance Wyatt]

Here is what I am trying to do:

I have two sets of similiar data on worksheet. On the first set,
Column A has cities, Column B has dollar amounts. This first set of
data is in the same order as the second set (same columns, different
rows)

The second set of data also has Column A with cities in the same order
as the first set of data. However Column, B is are values of 1-6. The
1-6 values correspond with the dollar amounts in the first set of
data.

Now, What I am trying to do is create a IF statement that says if any
city in Column A that has the value "1" in Column B in the second set
of data find the corresponding dollar amount in Column B in the first
set of data and SUM any of the matches.

Here is what I have so far, but is totally wrong:

=IF(ISNUMBER(MATCH(1,$C$83:$C$142)),SUM(C19:C52),0)

Help ?

 

[Don Guillett Excel MVP]

Here is what I am trying to do:

I have two sets of similiar data on worksheet. On the first set,
Column A has cities, Column B has dollar amounts. This first set of
data is in the same order as the second set (same columns, different
rows)

The second set of data also has Column A with cities in the same order
as the first set of data. However Column, B is are values of 1-6. The
1-6 values correspond with the dollar amounts in the first set of
data.

Now, What I am trying to do is create a IF statement that says if any
city in Column A that has the value "1" in Column B in the second set
of data find the corresponding dollar amount in Column B in the first
set of data and SUM any of the matches.

Here is what I have so far, but is totally wrong:

=IF(ISNUMBER(MATCH(1,$C$83:$C$142)),SUM(C19:C52),0)

Help ?

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[Max]

Assume your 2 sheets are named as: x, y
with data within rows 2 - 100 in cols A and B
you could try this in x,
in say, C2: =SUMPRODUCT((y!$B$2:$B$100=1)*(y!$A$2:$A$100=A2),$B$2:$B
$100)
Copy down

p/s: Ranges need to be identical size

 

[Max]

You should feedback to responders who cared enough to share something
with you.
Don't just post your Q and then keep quiet.