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?
you are looking at multiple options here from standard sumif to array formulae with sumproduct in the middle.
What is your problem ?
2+2=9 ... (My Arithmetic Is Mental)
The problem is I'm not sure how to do it!Originally Posted by unmarkedhelicopter
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?
=sumproduct((Sheet1!C1:C1000=Sheet3!C9)*Sheet1!J1:J1000)
2+2=9 ... (My Arithmetic Is Mental)
that comes up with a #VALUE error too! Any ideas why?
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)))
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
I'm still getting a #VALUE error.
Then you must have problems in the data.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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?
Post the book
2+2=9 ... (My Arithmetic Is Mental)
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?
Works fine for me even with formulae.
What is in C1:C3 and J1:J3?
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
C1:C3 just has the text 212, 212, 213
And J1:J3 have the formula
=IF(NOT(ISBLANK(H4)),I4*G4, " ")
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.
Change that formula to
=IF(NOT(ISBLANK(H4)),I4*G4,0)
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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 ? - ).
Actually I've sorted it! I've used conditional formatting to make the text invisible when there's nothing in H4.
Change the cell format to
_-?* #,##0.00_-;-?* #,##0.00_-;""
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Thanks a lot! Works perfectly!
Posting Permissions
Reply With Quote
