J
Jimbola
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
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