S
Stuie
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
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