Solved: VLOOKUP as a sum

[icthus123]

Hi,

Is there an excel formula which will let me find the sum of rows in a column of a table which meet a certain criteria. I suspect that some variation on VLOOKUP could do this but as far as I know VLOOKUP can only be made to return one value from a table not a sum of values.

Any ideas?

[unmarkedhelicopter]

you are looking at multiple options here from standard sumif to array formulae with sumproduct in the middle.
What is your problem ?

[icthus123]

you are looking at multiple options here from standard sumif to array formulae with sumproduct in the middle.
What is your problem ?

The problem is I'm not sure how to do it! :dunno

[icthus123]

I've tried to use this

=SUMPRODUCT((OFFSET(Sheet1!$C$4,0,0,COUNTA(Sheet1!$C:$C),1)=Sheet3!C9)*(OFF SET(Sheet1!$J$4,0,0,COUNTA(Sheet1!$J:$J),1)))


But it doesn't work! I want to find the sum in column J where column C = the value in sheet3!C9. Any ideas?

[unmarkedhelicopter]

=sumproduct((Sheet1!C1:C1000=Sheet3!C9)*Sheet1!J1:J1000)

[icthus123]

that comes up with a #VALUE error too! Any ideas why?

[Bob Phillips]

It is probably because you have a different number of rows in column C and J, so when you build the dynamic arrays, they are different sizes, and SUMPRODUCT chokes on that.

Try using the same sizing range

=SUMPRODUCT((OFFSET(Sheet1!$C$4,0,0,COUNTA(Sheet1!$C:$C),1)=Sheet3!C9)*(OFF SET(Sheet1!$J$4,0,0,COUNTA(Sheet1!$C:$C),1)))

[icthus123]

It is probably because you have a different number of rows in column C and J, so when you build the dynamic arrays, they are different sizes, and SUMPRODUCT chokes on that.

Try using the same sizing range

=SUMPRODUCT((OFFSET(Sheet1!$C$4,0,0,COUNTA(Sheet1!$C:$C),1)=Sheet3!C9)*(OFF SET(Sheet1!$J$4,0,0,COUNTA(Sheet1!$C:$C),1)))

I'm still getting a #VALUE error. :banghead:

[Bob Phillips]

Then you must have problems in the data.

[icthus123]

Well, at the moment column C has this data
212
212
213



And column J has this data got from a formula

?54.00
?54.00
?87.60

Any ideas what might be wrong?

[unmarkedhelicopter]

Post the book

[icthus123]

I can't cause it's too big, even when I compress it! And when I just copy the relevant bits into another file it works! Do you think it could be the formulae in column J that it doesn't like?

[Bob Phillips]

Works fine for me even with formulae.

What is in C1:C3 and J1:J3?

[icthus123]

Works fine for me even with formulae.

What is in C1:C3 and J1:J3?

C1:C3 just has the text 212, 212, 213

And J1:J3 have the formula

=IF(NOT(ISBLANK(H4)),I4*G4, " ")

[icthus123]

Sorry misunderstood you waht I have you was C4:C6, etc.

There's nothing in the first two rows. And just column heads in row three.

[Bob Phillips]

Change that formula to

=IF(NOT(ISBLANK(H4)),I4*G4,0)

[icthus123]

Thanks a lot, that works fine now! Except it's defeated the reason I put " " instead of 0, that's that I want those rows to be hidden until there's a value in H4. Is there a way I can still have it hidden? Now it has a zero in it (or in my case ? - ).

[icthus123]

Actually I've sorted it! I've used conditional formatting to make the text invisible when there's nothing in H4.

[Bob Phillips]

Change the cell format to

_-?* #,##0.00_-;-?* #,##0.00_-;""

[icthus123]

Thanks a lot! Works perfectly!