Sumif banding on a condition
 

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

Sumif banding on a condition
 

=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)



wrote in message
oups.com...
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

Sumif banding on a condition
 

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" wrote:

Sumif banding on a condition
 

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



" wrote:

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

Sumif banding on a condition
 

Thanks to you both...

Works a treat.

Cheers

On 4 Jul, 22:34, JMB wrote:
There may be a better way, but you could use

Sumif banding on a condition
 

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)

wrote in message
ups.com...
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" wrote: