Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing based on N largest / smallest values
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... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing based on N largest / smallest values
18,000 is the sum of all 5 instances of Black Cats.
What exactly do you require? -- Kind regards, Niek Otten Microsoft MVP - Excel wrote in message ... | 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... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing based on N largest / smallest values
=SUM(LARGE(IF((A1:A20="Cat")*(B1:B20="Black"),C1:C 20),{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) wrote in message ... 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... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing based on N largest / smallest values
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... On 23 Nov, 15:54, "Niek Otten" wrote: 18,000 is the sum of all 5 instances of Black Cats. What exactly do you require? -- Kind regards, Niek Otten Microsoft MVP - Excel wrote in ... | 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... |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing based on N largest / smallest values
Then use Bob Phillips' solution
-- Kind regards, Niek Otten Microsoft MVP - Excel wrote in message ... | 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... | | | | On 23 Nov, 15:54, "Niek Otten" wrote: | 18,000 is the sum of all 5 instances of Black Cats. | | What exactly do you require? | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | wrote in ... | | | 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... | |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing based on N largest / smallest values
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"),D 2: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 On 23 Nov, 16:39, "Niek Otten" wrote: Then use Bob Phillips' solution -- Kind regards, Niek Otten Microsoft MVP - Excel |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing based on N largest / smallest values
I think you need
=SUM(SMALL(IF((A1:A20="Cat")*(B1:B20="Black"),D1:D 20),{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 wrote in message ... 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"),D 2: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 On 23 Nov, 16:39, "Niek Otten" wrote: Then use Bob Phillips' solution -- Kind regards, Niek Otten Microsoft MVP - Excel |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing based on N largest / smallest values
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(G23,"",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 wrote in message ... | 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"),D 2: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 | | | On 23 Nov, 16:39, "Niek Otten" wrote: | Then use Bob Phillips' solution | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing based on N largest / smallest values
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 -------------------------- Niek Otten wrote: 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. 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel summing N largest values by condition | Excel Discussion (Misc queries) | |||
Find Smallest and Largest. | Excel Worksheet Functions | |||
functions largest to smallest | Excel Worksheet Functions | |||
Getting the 2nd largest or smallest valuesin a range | Excel Discussion (Misc queries) | |||
Ranking cells largest to smallest | Excel Discussion (Misc queries) |