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

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!
================================================== ===



"KUMPFfrog" wrote in message
...
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

On Tue, 22 Jul 2008 08:21:01 -0700, KUMPFfrog
wrote:

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

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" wrote:

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

On Tue, 22 Jul 2008 08:21:01 -0700, KUMPFfrog
wrote:

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

You should maybe try my suggested formula!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"KUMPFfrog" wrote in message
...
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" wrote:

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

On Tue, 22 Jul 2008 08:21:01 -0700, KUMPFfrog
wrote:

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

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.

"RagDyer" wrote:

You should maybe try my suggested formula!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"KUMPFfrog" wrote in message
...
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" wrote:

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

On Tue, 22 Jul 2008 08:21:01 -0700, KUMPFfrog
wrote:

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

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"KUMPFfrog" wrote in message
...
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.

"RagDyer" wrote:

You should maybe try my suggested formula!
--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"KUMPFfrog" wrote in message
...
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" wrote:

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

On Tue, 22 Jul 2008 08:21:01 -0700, KUMPFfrog
wrote:

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