match, lookup two column data

[KUMPFfrog]

I want to perform a calculation base off criteria from two columns.

ex:
A B
1 apples 222
2 oranges 156
3 apples 789
4 apples 222
5 oranges 789

if all the 'apples' in column 'A' have the same corrisponding value in
column 'B' then multipy the value in 'B' times some number. In this example,
ALL the 'apples' do not have 'B' values that match so it will return "Varies".

Thanks

 

[RagDyeR]

One way ... one LONG way, with an *array* formula,

With the lookup item in C1 and the number to multiply in C2:

=IF(COUNT(IF((A2:A6=C1)*(B2:B6=VLOOKUP(C1,A2:B6,2,0)),B2:B6))=COUNTIF(A2:A6,C1),VLOOKUP(C1,A2:B6,2,0)*C2,"Varies")

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.



--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



I want to perform a calculation base off criteria from two columns.

ex:
A B
1 apples 222
2 oranges 156
3 apples 789
4 apples 222
5 oranges 789

if all the 'apples' in column 'A' have the same corrisponding value in
column 'B' then multipy the value in 'B' times some number. In this
example,
ALL the 'apples' do not have 'B' values that match so it will return
"Varies".

Thanks

 

[Lars-Åke Aspelin]

I want to perform a calculation base off criteria from two columns.

ex:
A B
1 apples 222
2 oranges 156
3 apples 789
4 apples 222
5 oranges 789

if all the 'apples' in column 'A' have the same corrisponding value in
column 'B' then multipy the value in 'B' times some number. In this example,
ALL the 'apples' do not have 'B' values that match so it will return "Varies".

Thanks

Try this formula in cell C1

=IF(COUNTIF($A$1:$A$10,A1)<>SUMPRODUCT(- -($A$1:$A$10=A1),-
-($B$1:$B$10=B1)),"Varies",B1*$Z$1)

Cell Z1 is where "some number" is.

Hope this helps / Lars-Åke

 

[KUMPFfrog]

thanks Lars-Ã…ke Aspelin,

only one problem. if my criteria changes to 'oranges' then i would need to
change the 'B1' reference to 'B2' and so on. So, if all the corrisponding
'B' values match, then i would need it to find the 'B' value to the first
criteria match in column 'A'.

Hope that makes sense.

 

[Lars-Åke Aspelin]

Sorry, I forgot to mention that you should drag down the formula in C1
as far as needed in column C, that is as many rows as there are data
in the columns A and B.

The result will be a column C filled with either "Varies" or the
values (that do not vary) multiplied with the "some number in Z1".

 

[KUMPFfrog]

sorry, i should have provided more details.

continuing with the original example given - i have an area of cells that
will sort of summerize data in the worksheet.
for example: In column E i would have the list of fruits available. In
column F is the number or times that fruit shows up in the range. and in
column G i would use the formula that we/you are trying to create. in my
application this is a date, not a number like i used in the example, but
hopefully you get the idea.

 

[RagDyer]

You should maybe try my suggested formula!

 

[Lars-Åke Aspelin]

No, I am lost.
What happened to columns A and B that you had in your example?

Please give a new example with all relevant input and the expected
output.

 

[KUMPFfrog]

It works perfectly. I actually did try your formula first, but i think in
trying to convert it from my example to my actual application, I mis-keyed
something. Needless to say, it worked when i gave it a second try.

Thanks sooo much to both you and Lars-Ã…ke Aspelin for your help.

 

[KUMPFfrog]

Thanks for all your help.
I was able to make the formula from RagDyer work.

Sorry my example confused you.

 

[Ragdyer]

You're welcome, and we appreciate the feed-back.