Hello,

I have 2 columns of data, with a formula in the 3rd (part of the solution?),
as such

Type Value
a b c
1 G1 14.3 =cell("row",a1) - simply returns
the row number
2 G1 16.8 =cell("row",a2)
3 G1 17.1 =cell("row",a3)
4 G1 20 =cell("row",a4)
5 G1 23 =cell("row",a2)
6 B2 10 =cell("row",a6)
7 B2 11.5 =cell("row",a7)
8 B2 20 =cell("row",a8)
9 H3 10.9 =cell("row",a9)
10 H3 22.2 =cell("row",a10)
11 H3 14.6 =cell("row",a11)

What I want is to create a an auto-expanding sumif function this is sort of
what I want to do but it doesn't work;

a1:a11 = range
b1:b11 =sumrange


=sumif("A"&vlookup("G1",range,3,false)&":"&"A"&vlo okup("G1",range,3,false)+countif(range,"G1")-1,"G1","B"&vlookup("G1",range,3,false)&":"&"B"&vlo okup("G1",range,3,false)+countif(range,"G1")-1)

What the formula does is uses a vlookup to get the ref for the very first
occurance of G1 A1 then counts the number of G1 occurance minus 1 and
get the range A4. The second vlookup does exactly the same but for the sum
range.


Basically I want to find the sumif to only limit itself to the type I am
looking for.

So in this case is that big formula is the same as sumif(a1:a5,"G1",b1:b5)
of course the big formula G1 would be linked to a cell which could contain
any of the type and hence the sumif range would change dynamically.
How do I do this do I use indirect or something else?

Regards

J

I don't understand why =SUMIF(range,"G1",offset(range,0,1)) is not
sufficient?


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jimbola" wrote in message
...
Hello,

I have 2 columns of data, with a formula in the 3rd (part of the
solution?),
as such

Type Value
a b c
1 G1 14.3 =cell("row",a1) - simply returns
the row number
2 G1 16.8 =cell("row",a2)
3 G1 17.1 =cell("row",a3)
4 G1 20 =cell("row",a4)
5 G1 23 =cell("row",a2)
6 B2 10 =cell("row",a6)
7 B2 11.5 =cell("row",a7)
8 B2 20 =cell("row",a8)
9 H3 10.9 =cell("row",a9)
10 H3 22.2 =cell("row",a10)
11 H3 14.6 =cell("row",a11)

What I want is to create a an auto-expanding sumif function this is sort
of
what I want to do but it doesn't work;

a1:a11 = range
b1:b11 =sumrange



=sumif("A"&vlookup("G1",range,3,false)&":"&"A"&vlo okup("G1",range,3,false)+c
ountif(range,"G1")-1,"G1","B"&vlookup("G1",range,3,false)&":"&"B"&vlo okup("G
1",range,3,false)+countif(range,"G1")-1)

What the formula does is uses a vlookup to get the ref for the very first
occurance of G1 A1 then counts the number of G1 occurance minus 1 and
get the range A4. The second vlookup does exactly the same but for the sum
range.


Basically I want to find the sumif to only limit itself to the type I am
looking for.

So in this case is that big formula is the same as sumif(a1:a5,"G1",b1:b5)
of course the big formula G1 would be linked to a cell which could contain
any of the type and hence the sumif range would change dynamically.
How do I do this do I use indirect or something else?

Regards

J

Hi Bob,
Thanks for your response.

Normally this would be fine, unfortunately I have been give a massive
workbook, with 8 worksheets where the rows are populated upto the 50,000 row.
I need to summarize this on a summary sheet given the quantity of the data a
normal sumif takes forever to re-calc.
I've tried using a pivot table but that simply doesn't fulfill my needs.
After doing some probing it appears that by sorting the data into type and
then doing specific sumif the sheet is faster, as its only referencing exact
ranges.
But this data changes and so i need a dynamic sumif.

Hope that makes it clearer.


J


"Bob Phillips" wrote:

I don't understand why =SUMIF(range,"G1",offset(range,0,1)) is not
sufficient?


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jimbola" wrote in message
...
Hello,

I have 2 columns of data, with a formula in the 3rd (part of the
solution?),
as such

Type Value
a b c
1 G1 14.3 =cell("row",a1) - simply returns
the row number
2 G1 16.8 =cell("row",a2)
3 G1 17.1 =cell("row",a3)
4 G1 20 =cell("row",a4)
5 G1 23 =cell("row",a2)
6 B2 10 =cell("row",a6)
7 B2 11.5 =cell("row",a7)
8 B2 20 =cell("row",a8)
9 H3 10.9 =cell("row",a9)
10 H3 22.2 =cell("row",a10)
11 H3 14.6 =cell("row",a11)

What I want is to create a an auto-expanding sumif function this is sort
of
what I want to do but it doesn't work;

a1:a11 = range
b1:b11 =sumrange



=sumif("A"&vlookup("G1",range,3,false)&":"&"A"&vlo okup("G1",range,3,false)+c
ountif(range,"G1")-1,"G1","B"&vlookup("G1",range,3,false)&":"&"B"&vlo okup("G
1",range,3,false)+countif(range,"G1")-1)

What the formula does is uses a vlookup to get the ref for the very first
occurance of G1 A1 then counts the number of G1 occurance minus 1 and
get the range A4. The second vlookup does exactly the same but for the sum
range.


Basically I want to find the sumif to only limit itself to the type I am
looking for.

So in this case is that big formula is the same as sumif(a1:a5,"G1",b1:b5)
of course the big formula G1 would be linked to a cell which could contain
any of the type and hence the sumif range would change dynamically.
How do I do this do I use indirect or something else?

Regards

J

Try this, as dynamic as it gets (I hope -;))

On the summary sheet,

cell A1: enter the first data sheet name
Cell B1:
=SUMIF(OFFSET(INDIRECT("'"&A1&"'!A1"),,,COUNTA(IND IRECT("'"&A1&"'!A:A"))),"G
1",OFFSET(INDIRECT("'"&A1&"'!B1"),,,COUNTA(INDIREC T("'"&A1&"'!A:A"))))

and repeat for each sheet and copy the formula down.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jimbola" wrote in message
...
Hi Bob,
Thanks for your response.

Normally this would be fine, unfortunately I have been give a massive
workbook, with 8 worksheets where the rows are populated upto the 50,000
row.
I need to summarize this on a summary sheet given the quantity of the data
a
normal sumif takes forever to re-calc.
I've tried using a pivot table but that simply doesn't fulfill my needs.
After doing some probing it appears that by sorting the data into type and
then doing specific sumif the sheet is faster, as its only referencing
exact
ranges.
But this data changes and so i need a dynamic sumif.

Hope that makes it clearer.


J


"Bob Phillips" wrote:

I don't understand why =SUMIF(range,"G1",offset(range,0,1)) is not
sufficient?


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jimbola" wrote in message
...
Hello,

I have 2 columns of data, with a formula in the 3rd (part of the
solution?),
as such

Type Value
a b c
1 G1 14.3 =cell("row",a1) - simply
returns
the row number
2 G1 16.8 =cell("row",a2)
3 G1 17.1 =cell("row",a3)
4 G1 20 =cell("row",a4)
5 G1 23 =cell("row",a2)
6 B2 10 =cell("row",a6)
7 B2 11.5 =cell("row",a7)
8 B2 20 =cell("row",a8)
9 H3 10.9 =cell("row",a9)
10 H3 22.2 =cell("row",a10)
11 H3 14.6 =cell("row",a11)

What I want is to create a an auto-expanding sumif function this is
sort
of
what I want to do but it doesn't work;

a1:a11 = range
b1:b11 =sumrange




=sumif("A"&vlookup("G1",range,3,false)&":"&"A"&vlo okup("G1",range,3,false)+c

ountif(range,"G1")-1,"G1","B"&vlookup("G1",range,3,false)&":"&"B"&vlo okup("G
1",range,3,false)+countif(range,"G1")-1)

What the formula does is uses a vlookup to get the ref for the very
first
occurance of G1 A1 then counts the number of G1 occurance minus 1
and
get the range A4. The second vlookup does exactly the same but for the
sum
range.


Basically I want to find the sumif to only limit itself to the type I
am
looking for.

So in this case is that big formula is the same as
sumif(a1:a5,"G1",b1:b5)
of course the big formula G1 would be linked to a cell which could
contain
any of the type and hence the sumif range would change dynamically.
How do I do this do I use indirect or something else?

Regards

J

Many thanks for this Bob.

"Bob Phillips" wrote:

Try this, as dynamic as it gets (I hope -;))

On the summary sheet,

cell A1: enter the first data sheet name
Cell B1:
=SUMIF(OFFSET(INDIRECT("'"&A1&"'!A1"),,,COUNTA(IND IRECT("'"&A1&"'!A:A"))),"G
1",OFFSET(INDIRECT("'"&A1&"'!B1"),,,COUNTA(INDIREC T("'"&A1&"'!A:A"))))

and repeat for each sheet and copy the formula down.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jimbola" wrote in message
...
Hi Bob,
Thanks for your response.

Normally this would be fine, unfortunately I have been give a massive
workbook, with 8 worksheets where the rows are populated upto the 50,000
row.
I need to summarize this on a summary sheet given the quantity of the data
a
normal sumif takes forever to re-calc.
I've tried using a pivot table but that simply doesn't fulfill my needs.
After doing some probing it appears that by sorting the data into type and
then doing specific sumif the sheet is faster, as its only referencing
exact
ranges.
But this data changes and so i need a dynamic sumif.

Hope that makes it clearer.


J


"Bob Phillips" wrote:

I don't understand why =SUMIF(range,"G1",offset(range,0,1)) is not
sufficient?


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jimbola" wrote in message
...
Hello,

I have 2 columns of data, with a formula in the 3rd (part of the
solution?),
as such

Type Value
a b c
1 G1 14.3 =cell("row",a1) - simply
returns
the row number
2 G1 16.8 =cell("row",a2)
3 G1 17.1 =cell("row",a3)
4 G1 20 =cell("row",a4)
5 G1 23 =cell("row",a2)
6 B2 10 =cell("row",a6)
7 B2 11.5 =cell("row",a7)
8 B2 20 =cell("row",a8)
9 H3 10.9 =cell("row",a9)
10 H3 22.2 =cell("row",a10)
11 H3 14.6 =cell("row",a11)

What I want is to create a an auto-expanding sumif function this is
sort
of
what I want to do but it doesn't work;

a1:a11 = range
b1:b11 =sumrange




=sumif("A"&vlookup("G1",range,3,false)&":"&"A"&vlo okup("G1",range,3,false)+c

ountif(range,"G1")-1,"G1","B"&vlookup("G1",range,3,false)&":"&"B"&vlo okup("G
1",range,3,false)+countif(range,"G1")-1)

What the formula does is uses a vlookup to get the ref for the very
first
occurance of G1 A1 then counts the number of G1 occurance minus 1
and
get the range A4. The second vlookup does exactly the same but for the
sum
range.


Basically I want to find the sumif to only limit itself to the type I
am
looking for.

So in this case is that big formula is the same as
sumif(a1:a5,"G1",b1:b5)
of course the big formula G1 would be linked to a cell which could
contain
any of the type and hence the sumif range would change dynamically.
How do I do this do I use indirect or something else?

Regards

J

Bob Phillips wrote...
Try this, as dynamic as it gets (I hope -;))

On the summary sheet,

cell A1: enter the first data sheet name
Cell B1:
=SUMIF(OFFSET(INDIRECT("'"&A1&"'!A1"),,,COUNTA(IN DIRECT("'"&A1&"'!A:A"))),"G
1",OFFSET(INDIRECT("'"&A1&"'!B1"),,,COUNTA(INDIRE CT("'"&A1&"'!A:A"))))
....

Why OFFSET(INDIRECT(.))? An alternative,

=SUMIF(INDIRECT("'"&A1&"'!A1:A"&COUNTA(INDIRECT("' "&A1&"'!A:A"))),
"G1",INDIRECT("'"&A1&"'!B1:B"&COUNTA(INDIRECT("'"& A1&"'!A:A"))))

That said, I doubt this would recalc any quicker than

=SUMIF(INDIRECT("'"&A1&"'!A:A"),"G1",INDIRECT("'"& A1&"'!B:B"))