Find the cell ID of last row in the column and use it in SUMIF for
 

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.

Find the cell ID of last row in the column and use it in SUMIF for
 

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.

Find the cell ID of last row in the column and use it in SUMIF for
 

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.