Sumif banding on a condition

[stuart]

Dear all...

Is there a way of suming a column of data based firstly on a criteria
(E.g. Name = "Bob") and then on the relative order in which the values
appear in the list (E.g. Sum the 1st, 2nd, 3rd & 4th instances of
"Bob").

Essentially what i'm looking for is a formula that will sum the values
for the fist 10 instances (1 to 10) of Bob.
But also the ability to change the criteria. E.g.
instances 11 to 20, 21 to 30, 31 to 40, etc.

To illustrate...

Name Value
Bob 5
Ted 2
Fred 6
Bob 4
Fred 2
Bob 5
Bob 1
Ted 4
Bob 2

E.g.
Bob instances 1 to 3 would equal 14
Bob instances 4 to 5 would equal 3

Etc...

I'd need to do this without re-ordering the data.

Hope you can help

 

[Bob Phillips]

=SUM(N(OFFSET($B$1,SMALL(IF($A$1:$A$20="Bob",ROW($A$1:$A$20)),{1,2,5})-1,0)))

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.

The 1,2,5 refers to the instances you want to capture.

--
---
HTH

Bob

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

 

[stuart]

Thanks Bob.

Does this mean if i wanted to band together instances 50 to 100 i'd
have to list out all 50 numbers in the brackets {50, 51, 52, etc.?



On 4 Jul, 22:09, "Bob Phillips" <[email protected]> wrote:

 

[JMB]

There may be a better way, but you could use

=SUMPRODUCT(--(A1:A12=F1),--(ROW(B1:B12)>=SMALL(IF(A1:A12=F1,ROW(A1:A12)),G1)),--(ROW(B1:B12)<=SMALL(IF(A1:A12=F1,ROW(A1:A12)),H1)),B1:B12)

confirmed w/Ctrl+Shift+Enter

where A1:A12 contains your names, B1:B12 contains the numbers, F1 is the
name you are looking for, G1 is the first instance, and H1 is the last
instance you want included

 

[stuart]

Thanks to you both...

Works a treat.

Cheers

 

[Bob Phillips]

No, if contiguous, you could use

=SUM(N(OFFSET($B$1,SMALL(IF($A$1:$A$20="Bob",ROW($A$1:$A$20)),ROW(INDIRECT("50:100")))-1,0)))

--
HTH

Bob

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