Summing occurrences within a list
I have a worksheet containing a host of data, similar to that shown
below. I want to be able to sum between the Nth & Nth occurrence of a given criteria, eg. sum the Qty value of the fourth, fifth & sixth occurrence of Apples. Rank Fruit Qty 1 Apple 1500 2 Pear 1400 3 Apple 1300 4 Pear 1200 5 Apple 1100 6 Apple 1000 7 Pear 900 8 Pear 800 9 Apple 700 10 Apple 600 11 Apple 500 12 Apple 400 13 Pear 300 14 Pear 200 15 Apple 100 Eg. Apple occurrence 1-3 Rank Fruit Qty 1 Apple 1500 3 Apple 1300 5 Apple 1100 =3900 Eg. Apple occurrence 4-6 Rank Fruit Qty 6 Apple 1000 9 Apple 700 10 Apple 600 =2300 Eg. Apple occurrence 7-9 Rank Fruit Qty 11 Apple 500 12 Apple 400 15 Apple 100 =1000 There are certain restrictions i'm facing. No1. It needs to be in the form of a worksheet formula rather than VBA. No2. It's not possible to re-sort the list. I hope someone can help. Rgds |
Summing occurrences within a list
=SUM(N(OFFSET(C2,SMALL(IF(B2:B16="Apple",ROW(C2:C1 6)),{1,2,3})-MIN(ROW(C2:C16)),0)))
=SUM(N(OFFSET(C2,SMALL(IF(B2:B16="Apple",ROW(C2:C1 6)),{4,5,6})-MIN(ROW(C2:C16)),0))) etc. 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) "Stuie" wrote in message ups.com... I have a worksheet containing a host of data, similar to that shown below. I want to be able to sum between the Nth & Nth occurrence of a given criteria, eg. sum the Qty value of the fourth, fifth & sixth occurrence of Apples. Rank Fruit Qty 1 Apple 1500 2 Pear 1400 3 Apple 1300 4 Pear 1200 5 Apple 1100 6 Apple 1000 7 Pear 900 8 Pear 800 9 Apple 700 10 Apple 600 11 Apple 500 12 Apple 400 13 Pear 300 14 Pear 200 15 Apple 100 Eg. Apple occurrence 1-3 Rank Fruit Qty 1 Apple 1500 3 Apple 1300 5 Apple 1100 =3900 Eg. Apple occurrence 4-6 Rank Fruit Qty 6 Apple 1000 9 Apple 700 10 Apple 600 =2300 Eg. Apple occurrence 7-9 Rank Fruit Qty 11 Apple 500 12 Apple 400 15 Apple 100 =1000 There are certain restrictions i'm facing. No1. It needs to be in the form of a worksheet formula rather than VBA. No2. It's not possible to re-sort the list. I hope someone can help. Rgds |
Summing occurrences within a list
Cheers Bob...
Array formula was just the ticket... |
All times are GMT +1. The time now is 03:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com