Summing based on N largest / smallest values

S

stuart

Is it possible to sum a column of values based on the values of
another column being the N largest / smallest?

An example might help undestand the concept...

Position Animal Colour Volume Value
1 Cat Brown 5,000 6,100
2 Dog Brown 4,900 6,200
3 Cat Brown 4,800 6,300
4 Dog Brown 4,700 6,400
5 Cat Brown 4,600 6,500
6 Dog Brown 4,500 6,600
7 Cat Brown 4,400 6,700
8 Dog Brown 4,300 6,800
9 Cat Brown 4,200 6,900
10 Dog Brown 4,100 7,000
11 Cat Black 4,000 7,100
12 Dog Black 3,900 7,200
13 Cat Black 3,800 7,300
14 Dog Black 3,700 7,400
15 Cat Black 3,600 7,500
16 Dog Black 3,500 7,600
17 Cat Black 3,400 7,700
18 Dog Black 3,300 7,800
19 Cat Black 3,200 7,900
20 Dog Black 3,100 8,000

I'm looking to see the total Volume for the first three instances of
Black Cats.

(The answer i'm looking for is 18,000)

Hope someone can help...
 
N

Niek Otten

18,000 is the sum of all 5 instances of Black Cats.

What exactly do you require?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Is it possible to sum a column of values based on the values of
| another column being the N largest / smallest?
|
| An example might help undestand the concept...
|
| Position Animal Colour Volume Value
| 1 Cat Brown 5,000 6,100
| 2 Dog Brown 4,900 6,200
| 3 Cat Brown 4,800 6,300
| 4 Dog Brown 4,700 6,400
| 5 Cat Brown 4,600 6,500
| 6 Dog Brown 4,500 6,600
| 7 Cat Brown 4,400 6,700
| 8 Dog Brown 4,300 6,800
| 9 Cat Brown 4,200 6,900
| 10 Dog Brown 4,100 7,000
| 11 Cat Black 4,000 7,100
| 12 Dog Black 3,900 7,200
| 13 Cat Black 3,800 7,300
| 14 Dog Black 3,700 7,400
| 15 Cat Black 3,600 7,500
| 16 Dog Black 3,500 7,600
| 17 Cat Black 3,400 7,700
| 18 Dog Black 3,300 7,800
| 19 Cat Black 3,200 7,900
| 20 Dog Black 3,100 8,000
|
| I'm looking to see the total Volume for the first three instances of
| Black Cats.
|
| (The answer i'm looking for is 18,000)
|
| Hope someone can help...
 
B

Bob Phillips

=SUM(LARGE(IF((A1:A20="Cat")*(B1:B20="Black"),C1:C20),{1,2,3}))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
S

stuart

Nick,

Apologies.

Answer i'm after is 11,400

The crux of the problem is that the data in the [Volume] & [Value]
columns isn't necessarily in the same order.
Largest volume doesn't necesarily equate to largest value.

So i'm guessing i'll need to look first at the [Animal] column to
determine if it's a Cat, then at the [Colour] column to determine if
it's Black, then at the [Position] column to see if its' one of the
first three instances, and lastly to sum the data in the [Volume] and/
or [Value] column.

Hope this makes sense...
 
N

Niek Otten

Then use Bob Phillips' solution

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Nick,
|
| Apologies.
|
| Answer i'm after is 11,400
|
| The crux of the problem is that the data in the [Volume] & [Value]
| columns isn't necessarily in the same order.
| Largest volume doesn't necesarily equate to largest value.
|
| So i'm guessing i'll need to look first at the [Animal] column to
| determine if it's a Cat, then at the [Colour] column to determine if
| it's Black, then at the [Position] column to see if its' one of the
| first three instances, and lastly to sum the data in the [Volume] and/
| or [Value] column.
|
| Hope this makes sense...
|
|
|
| > 18,000 is the sum of all 5 instances of Black Cats.
| >
| > What exactly do you require?
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| >
| > | Is it possible to sum a column of values based on the values of
| > | another column being the N largest / smallest?
| > |
| > | An example might help undestand the concept...
| > |
| > | Position Animal Colour Volume Value
| > | 1 Cat Brown 5,000 6,100
| > | 2 Dog Brown 4,900 6,200
| > | 3 Cat Brown 4,800 6,300
| > | 4 Dog Brown 4,700 6,400
| > | 5 Cat Brown 4,600 6,500
| > | 6 Dog Brown 4,500 6,600
| > | 7 Cat Brown 4,400 6,700
| > | 8 Dog Brown 4,300 6,800
| > | 9 Cat Brown 4,200 6,900
| > | 10 Dog Brown 4,100 7,000
| > | 11 Cat Black 4,000 7,100
| > | 12 Dog Black 3,900 7,200
| > | 13 Cat Black 3,800 7,300
| > | 14 Dog Black 3,700 7,400
| > | 15 Cat Black 3,600 7,500
| > | 16 Dog Black 3,500 7,600
| > | 17 Cat Black 3,400 7,700
| > | 18 Dog Black 3,300 7,800
| > | 19 Cat Black 3,200 7,900
| > | 20 Dog Black 3,100 8,000
| > |
| > | I'm looking to see the total Volume for the first three instances of
| > | Black Cats.
| > |
| > | (The answer i'm looking for is 18,000)
| > |
| > | Hope someone can help...
|
 
S

stuart

Nick,

Don't think Bob's solultion will work for this example.

The first three instances where [Animal] is Cat & [Colour] is Black
are as follows:

Col A Col B Col C Col D Col E
Position Animal Colour Volume Value
11 Cat Black 4,000 7,100
13 Cat Black 3,800 7,300
15 Cat Black 3,600 7,500

Using Bob's example i can get to the first three [Volume] instances
(1,1,400) using the following:

{=SUM(LARGE(IF((B2:B100="Cat")*(C2:C100="Black"),D2:D100),{1,2,3}))}

However their respective [Value] numbers aren't necessarily in the
same descending order, so the same formula to return the [Value] total
(21,900) doesn't work.


Any ideas?

Cheers
 
P

Peo Sjoblom

I think you need

=SUM(SMALL(IF((A1:A20="Cat")*(B1:B20="Black"),D1:D20),{1,2,3}))

(entered with ctrl + shift & enter)

for the value and the large version for the volume

the above returns 21,900 for the value


--


Regards,


Peo Sjoblom


Nick,

Don't think Bob's solultion will work for this example.

The first three instances where [Animal] is Cat & [Colour] is Black
are as follows:

Col A Col B Col C Col D Col E
Position Animal Colour Volume Value
11 Cat Black 4,000 7,100
13 Cat Black 3,800 7,300
15 Cat Black 3,600 7,500

Using Bob's example i can get to the first three [Volume] instances
(1,1,400) using the following:

{=SUM(LARGE(IF((B2:B100="Cat")*(C2:C100="Black"),D2:D100),{1,2,3}))}

However their respective [Value] numbers aren't necessarily in the
same descending order, so the same formula to return the [Value] total
(21,900) doesn't work.


Any ideas?

Cheers


Then use Bob Phillips' solution

--
Kind regards,

Niek Otten
Microsoft MVP - Excel
Click to expand...
Click to expand...
 
N

Niek Otten

OK.
It took me some time to understand that it is the position column that selects the top 3, not the value of the column itself.
Here is a step by step approach for Volume. Values is similar but with different column.

In F2:

=IF(AND($B2="cat",$C2="black"),RANK(A2,$A$2:$A$21,1),"")
Copy down

In G2:
=IF(F2="","",(RANK(F2,$F$2:$F$21,1)))
Copy down

In H2:
=IF(G2>3,"",D2)
Copy down

Sum column H

I'm sure Bob and/or Peo come up with a compact one-column array formula solution; I'm not good at that at all!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Nick,
|
| Don't think Bob's solultion will work for this example.
|
| The first three instances where [Animal] is Cat & [Colour] is Black
| are as follows:
|
| Col A Col B Col C Col D Col E
| Position Animal Colour Volume Value
| 11 Cat Black 4,000 7,100
| 13 Cat Black 3,800 7,300
| 15 Cat Black 3,600 7,500
|
| Using Bob's example i can get to the first three [Volume] instances
| (1,1,400) using the following:
|
| {=SUM(LARGE(IF((B2:B100="Cat")*(C2:C100="Black"),D2:D100),{1,2,3}))}
|
| However their respective [Value] numbers aren't necessarily in the
| same descending order, so the same formula to return the [Value] total
| (21,900) doesn't work.
|
|
| Any ideas?
|
| Cheers
|
|
| > Then use Bob Phillips' solution
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
 
S

stuart

Nick, Bob, Peo,

Thanks for all your help on this.

If anyone knows a single cell answer to this then please let me
know...

Thanks again
 
Top