Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 5th 04, 07:47 PM
Jim Gentile
 
Posts: n/a
Default Read options in more than one cell

If the A column lists the salespersons name, and the B column lists their
sales. I would like a formula that will total all individuals’ sales, and
then list the top sales in a separate cell, with the name of that salesperson
in the cell next to the totaled sales.

For example: based on the numbers below, I would like Amber to appear in D1
and $297.69 to appear in E1. Thanks for your help.

Amber $158.69
Amber $139.00
Beverly $139.87
Beverly $41.90
Beverly $73.97
Beverly $24.95
Blase $64.99
Blase $208.98


  #2   Report Post  
Old December 5th 04, 09:09 PM
tjtjjtjt
 
Posts: n/a
Default

This one is for the name, and it's a little messy, and it requires the
formula that returns the highest valus (farther down this posting):

=IF(E2=SUMIF(A1:A8,"Amber",B1:B8),"Amber",IF(SUMIF (A1:A8,"Beverly",B1:B8),"Beverly","Blase"))

It will return a match for Amber or Beverly. It will return Blase if it
can't match the other two. You may try to refine it or see if someone can
post a more compact formula.

This one is for the value:
=MAX(SUMIF(A1:A8,{"Amber","Beverly","Blase"},B1:B8 ))

The names in curly brackets basically tell Excel to add all the Anmber
values, all the Beverly values and all the Blase values, and then return the
highest total.

Hope this helps,
tj

"Jim Gentile" wrote:

If the A column lists the salespersons name, and the B column lists their
sales. I would like a formula that will total all individuals’ sales, and
then list the top sales in a separate cell, with the name of that salesperson
in the cell next to the totaled sales.

For example: based on the numbers below, I would like Amber to appear in D1
and $297.69 to appear in E1. Thanks for your help.

Amber $158.69
Amber $139.00
Beverly $139.87
Beverly $41.90
Beverly $73.97
Beverly $24.95
Blase $64.99
Blase $208.98

  #3   Report Post  
Old December 5th 04, 09:49 PM
tjtjjtjt
 
Posts: n/a
Default

The first formula should have been:
=IF(E2=SUMIF(A1:A8,"Amber",B1:B8),"Amber",IF(E2=(S UMIF(A1:A8,"Beverly",B1:B8)),"Beverly","Blase"))

I left out the E2=
at the start of the second IF statement when I retyped it. Next time I'll
copy and paste.


tj


"tjtjjtjt" wrote:

This one is for the name, and it's a little messy, and it requires the
formula that returns the highest valus (farther down this posting):

=IF(E2=SUMIF(A1:A8,"Amber",B1:B8),"Amber",IF(SUMIF (A1:A8,"Beverly",B1:B8),"Beverly","Blase"))

It will return a match for Amber or Beverly. It will return Blase if it
can't match the other two. You may try to refine it or see if someone can
post a more compact formula.

This one is for the value:
=MAX(SUMIF(A1:A8,{"Amber","Beverly","Blase"},B1:B8 ))

The names in curly brackets basically tell Excel to add all the Anmber
values, all the Beverly values and all the Blase values, and then return the
highest total.

Hope this helps,
tj

"Jim Gentile" wrote:

If the A column lists the salespersons name, and the B column lists their
sales. I would like a formula that will total all individuals’ sales, and
then list the top sales in a separate cell, with the name of that salesperson
in the cell next to the totaled sales.

For example: based on the numbers below, I would like Amber to appear in D1
and $297.69 to appear in E1. Thanks for your help.

Amber $158.69
Amber $139.00
Beverly $139.87
Beverly $41.90
Beverly $73.97
Beverly $24.95
Blase $64.99
Blase $208.98

  #4   Report Post  
Old December 6th 04, 06:04 PM
Jack Sheet
 
Posts: n/a
Default

But what if column A contains several hundred names?

I tend to avoid illustrating a problem by way of example when I post.

Examples can be useful to clarify intent, and I used to include examples
habitually, but they have the danger, as here, that responders concentrate
on producing a solution to the specific example rather than the general
case.


I thought the problem was a nice challenge, but lack the time to think about
it.

--
Return email address is not as DEEP as it appears
"tjtjjtjt" wrote in message
...
The first formula should have been:
=IF(E2=SUMIF(A1:A8,"Amber",B1:B8),"Amber",IF(E2=(S UMIF(A1:A8,"Beverly",B1:B8)),"Beverly","Blase"))

I left out the E2=
at the start of the second IF statement when I retyped it. Next time I'll
copy and paste.


tj


"tjtjjtjt" wrote:

This one is for the name, and it's a little messy, and it requires the
formula that returns the highest valus (farther down this posting):

=IF(E2=SUMIF(A1:A8,"Amber",B1:B8),"Amber",IF(SUMIF (A1:A8,"Beverly",B1:B8),"Beverly","Blase"))

It will return a match for Amber or Beverly. It will return Blase if it
can't match the other two. You may try to refine it or see if someone can
post a more compact formula.

This one is for the value:
=MAX(SUMIF(A1:A8,{"Amber","Beverly","Blase"},B1:B8 ))

The names in curly brackets basically tell Excel to add all the Anmber
values, all the Beverly values and all the Blase values, and then return
the
highest total.

Hope this helps,
tj

"Jim Gentile" wrote:

If the A column lists the salespersons name, and the B column lists
their
sales. I would like a formula that will total all individuals' sales,
and
then list the top sales in a separate cell, with the name of that
salesperson
in the cell next to the totaled sales.

For example: based on the numbers below, I would like Amber to appear
in D1
and $297.69 to appear in E1. Thanks for your help.

Amber $158.69
Amber $139.00
Beverly $139.87
Beverly $41.90
Beverly $73.97
Beverly $24.95
Blase $64.99
Blase $208.98



  #5   Report Post  
Old December 7th 04, 12:01 AM
Jack Sheet
 
Posts: n/a
Default

If you name the range of salespersons in colunm A to be called SalesPersons
and name the range of sales in column B to be called Sales
then in cell E1, array-enter the formula
=MAX(SUMIF(SalesPersons,SalesPersons,Sales))
(hit control+Shift+enter when entering the formula, which should then be
displayed in curly brackets in the formula bar)

Still thinking about the formula for cell D1. That is the challenge.


--
Return email address is not as DEEP as it appears
"Jim Gentile" wrote in message
...
If the A column lists the salespersons name, and the B column lists their
sales. I would like a formula that will total all individuals' sales, and
then list the top sales in a separate cell, with the name of that
salesperson
in the cell next to the totaled sales.

For example: based on the numbers below, I would like Amber to appear in
D1
and $297.69 to appear in E1. Thanks for your help.

Amber $158.69
Amber $139.00
Beverly $139.87
Beverly $41.90
Beverly $73.97
Beverly $24.95
Blase $64.99
Blase $208.98





  #6   Report Post  
Old December 7th 04, 12:35 AM
Jack Sheet
 
Posts: n/a
Default

A slightly inelegant solution, this requires you to reserve a column in
which to reside some temporary workings. You can hide the column. Suppose
we use column Z for this purpose, and your SalesPersons occupy cells
$A$1:$A$100, with Sales in $B$1:$B$100

Then in cell $Z$1 enter the formula
=SUMIF($A$1:$A$100,A1,$B$1:$B$100)
Copy that formula down to $Z$2:$Z$100

Then in cell $E$1 enter the formula
=MAX($Z$1:$Z$100)
and in cell $D$1 enter the formula
=INDEX($A$1:$A$100,MATCH(E1,$Z$1:$Z$100,FALSE))

This formula will not distinguish a case where two+ salespersons equally
rank for maximum sales - it will simply return one of those salespersons.
Still, it may suffice for your purposes.

There is probably a way of getting to a formula for D1 that uses an array
formula without using the cells in column Z in the above example, but it is
just beyond my reach at the moment. As per my previous post, an alternative
formula for E1 is fairly trivial without resorting to column Z.

--
Return email address is not as DEEP as it appears
"Jim Gentile" wrote in message
...
If the A column lists the salespersons name, and the B column lists their
sales. I would like a formula that will total all individuals' sales, and
then list the top sales in a separate cell, with the name of that
salesperson
in the cell next to the totaled sales.

For example: based on the numbers below, I would like Amber to appear in
D1
and $297.69 to appear in E1. Thanks for your help.

Amber $158.69
Amber $139.00
Beverly $139.87
Beverly $41.90
Beverly $73.97
Beverly $24.95
Blase $64.99
Blase $208.98





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to read a blank cell as zero for formulas? mrssm5 Excel Discussion (Misc queries) 2 December 23rd 04 01:48 PM
Using Jet to read excel file returns blank for last cell - sometim Ron Excel Discussion (Misc queries) 1 December 9th 04 08:21 AM
Addition to Turn cell red if today is greater or equal to date in cell Rich New Users to Excel 2 December 9th 04 02:06 AM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 07:16 PM
VLookup resulting in a blank cell... KempensBoerke Excel Worksheet Functions 1 October 28th 04 09:57 PM


All times are GMT +1. The time now is 12:24 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017