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