Get last row number from the cell value

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,354
Office Version
  1. 2010
Hello,</SPAN></SPAN>

I am using formula in cell G3 to down that is working fine; I want a formula, which can take a last row number from the cell values of H1 & H2
</SPAN></SPAN>

I tried to modified formula as shown below but getting #REF error please need help
</SPAN></SPAN>


Book1
ABCDEFGH
171
272
3
4n1Count "0" AfterCount ResultCount Result
5013#REF!
6122#REF!
7031#REF!
8141#REF!
9253#REF!
10360#REF!
11470#REF!
12580#REF!
13690#REF!
148100#REF!
159110#REF!
1611121#REF!
1712130#REF!
180140#REF!
190150#REF!
202160#REF!
210171#REF!
220180#REF!
231190#REF!
242200#REF!
254
265
270
280
290
301
312
324
330
342
353
364
375
380
390
401
410
421
432
443
454
465
476
487
499
5010
5111
5212
5314
5416
5517
560
571
582
593
600
610
622
630
641
650
661
672
683
694
705
710
721
Sheet2
Cell Formulas
RangeFormula
G5=SUMPRODUCT(--(D$4:D$71=F5)*(D$5:D$72=0))
H5=SUMPRODUCT(--(INDIRECT("D$4:D$"&H$1=F5))*INDIRECT("D$5:D$"&H$2=F5))


Thanks In Advance
</SPAN></SPAN>
Using version 2000
</SPAN></SPAN>

Regards,
</SPAN></SPAN>
Moti
</SPAN></SPAN>
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
How about
=SUMPRODUCT((INDIRECT("D$4:D$"&H$1)=F5)*(INDIRECT("D$5:D$"&H$2)=0))
 
Upvote 0
Maybe...

H5 copied down
=SUMPRODUCT(--(D$4:INDEX(D:D,H$1)=F5),--(D$5:INDEX(D:D,H$2)=0))

M.
 
Last edited:
Upvote 0
How about
=SUMPRODUCT((INDIRECT("D$4:D$"&H$1)=F5)*(INDIRECT("D$5:D$"&H$2)=0))
Fluff, thank you for correcting the formula it worked Perfect! </SPAN></SPAN>

Thank you for your help
</SPAN></SPAN>

Kind Regards,
</SPAN></SPAN>
Moti :)
</SPAN></SPAN>
 
Upvote 0
Maybe...

H5 copied down
=SUMPRODUCT(--(D$4:INDEX(D:D,H$1)=F5),--(D$5:INDEX(D:D,H$2)=0))

M.
Marcelo Branco, I liked your non-volatile INDEX formula it is much better I will use this one! </SPAN></SPAN>

Thank you for your help
</SPAN></SPAN>

Kind Regards,
</SPAN></SPAN>
Moti :biggrin:
</SPAN></SPAN>
 
Upvote 0
Marcelo Branco, I liked your non-volatile INDEX formula it is much better I will use this one!
Here is Marcelo's formula modified so that you do not need to put any values in cells H1 and H2 (the formula figures out these values on its own)...

=SUMPRODUCT(--(D$4:INDEX(D:D,COUNT(D:D)+3)=F5),--(D$5:INDEX(D:D,COUNT(D:D)+4)=0))
 
Last edited:
Upvote 0
Here is Marcelo's formula modified so that you do not need to put any values in cells H1 and H2 (the formula figures out these values on its own)...

=SUMPRODUCT(--(D$4:INDEX(D:D,COUNT(D:D)+3)=F5),--(D$5:INDEX(D:D,COUNT(D:D)+4)=0))
Rick Rothstein, really it works without the values in cells H1 and H2, I am surprised how many ways are to get result, I liked the Update! It is more easier now</SPAN></SPAN>

Thank you for your help </SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>
:biggrin:</SPAN></SPAN>
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top