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

=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

Cheers Bob...

Array formula was just the ticket...