Hi,
I have this formula to find the cell ID of the last row in a column:
=ADDRESS(MATCH(9.99999999999999E+307,'IT hours'!A:A),1)

eg.. if the result is $A$933

I want to use this Cell ID in a SUMIF formula.
SUMIFS('IT Ratio'!$A$4:$A$933,'IT Ratio'!$A$4:$A$118,'For SLR'!E$17,.....)
ie. to replace the $A$933 in the formula, so that the sumifs will work
dynamically.

May I know what should be the syntax for this?
Appreciate any help.
Thanks,
YY.

I am not sure how this is going to help; but to use that reference in your
formula use INDIRECT()

'you can use this formula instead which will return the last row number in
ColA with a numeric
=MATCH(9^9,A:A)

'To use that in a formula..refer the below example...The below formula will
add items in ColB referring to the last numeric in ColA...

=SUM(INDIRECT("B1:B" &MATCH(9^9,A:A))))

--
Jacob


"YY san." wrote:

Hi,
I have this formula to find the cell ID of the last row in a column:
=ADDRESS(MATCH(9.99999999999999E+307,'IT hours'!A:A),1)

eg.. if the result is $A$933

I want to use this Cell ID in a SUMIF formula.
SUMIFS('IT Ratio'!$A$4:$A$933,'IT Ratio'!$A$4:$A$118,'For SLR'!E$17,.....)
ie. to replace the $A$933 in the formula, so that the sumifs will work
dynamically.

May I know what should be the syntax for this?
Appreciate any help.
Thanks,
YY.

Try using

=SUMIFS(OFFSET('IT Ratio'!$A$4,,,COUNTA('IT Ratio'!$A:$A),1),'IT
Ratio'!$A$4:$A$118,'For SLR'!E$17,.....)

but as you are using 2007, you could even use whole columns

=SUMIFS('IT Ratio'!$A:$A,'IT Ratio'!$A$4:$A$118,'For SLR'!E$17,.....)

HTH

Bob

"YY san." wrote in message
...
Hi,
I have this formula to find the cell ID of the last row in a column:
=ADDRESS(MATCH(9.99999999999999E+307,'IT hours'!A:A),1)

eg.. if the result is $A$933

I want to use this Cell ID in a SUMIF formula.
SUMIFS('IT Ratio'!$A$4:$A$933,'IT Ratio'!$A$4:$A$118,'For SLR'!E$17,.....)
ie. to replace the $A$933 in the formula, so that the sumifs will work
dynamically.

May I know what should be the syntax for this?
Appreciate any help.
Thanks,
YY.