Dynamic sumif function

[Guest]

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"&vlookup("G1",range,3,false)+countif(range,"G1")-1,"G1","B"&vlookup("G1",range,3,false)&":"&"B"&vlookup("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

 

[Bob Phillips]

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)

 

[Guest]

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]

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(INDIRECT("'"&A1&"'!A:A"))),"G
1",OFFSET(INDIRECT("'"&A1&"'!B1"),,,COUNTA(INDIRECT("'"&A1&"'!A:A"))))

and repeat for each sheet and copy the formula down.

--

HTH

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

 

[Guest]

Many thanks for this Bob.

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(INDIRECT("'"&A1&"'!A:A"))),"G
1",OFFSET(INDIRECT("'"&A1&"'!B1"),,,COUNTA(INDIRECT("'"&A1&"'!A:A"))))

and repeat for each sheet and copy the formula down.

--

HTH

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

 

[Harlan Grove]

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(INDIRECT("'"&A1&"'!A:A"))),"G
1",OFFSET(INDIRECT("'"&A1&"'!B1"),,,COUNTA(INDIRECT("'"&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"))