witzph1
asked on
Dynamically count rows of names
The attached is a spreadsheet of accounts sorted by last name. There are 3 named ranges, Split 1, Split 2 and Split 3. Split 1 for instance refers to cell D520 which is at the last row of names beginning with F. Split 2 is at the last row of names starting with N. And Split 3 is at the last row of Z's.
If you jump down to the named range Split 1 you will see a formula that counts the rows between the first row of names beginning with A and the last row containing N's. The reason for the indirect function is so that if I drag Split 1 either up or down it dynamically counts the rows from the first row of names to wherever I drag it. This dynamic formula works well.
What I would like to now is to create a formula that dynamically counts rows between Split 1 and Split 2. Essentially this formula would count names G-N, but if I drug it down to the end of the O's it would adjust to count the rows G-O. And finally I need a similar formula which dynamically counts rows between Split 2 and Split 3.
I hope this makes sense! The concept is simple enough, but It's hard to explain.
If you jump down to the named range Split 1 you will see a formula that counts the rows between the first row of names beginning with A and the last row containing N's. The reason for the indirect function is so that if I drag Split 1 either up or down it dynamically counts the rows from the first row of names to wherever I drag it. This dynamic formula works well.
What I would like to now is to create a formula that dynamically counts rows between Split 1 and Split 2. Essentially this formula would count names G-N, but if I drug it down to the end of the O's it would adjust to count the rows G-O. And finally I need a similar formula which dynamically counts rows between Split 2 and Split 3.
I hope this makes sense! The concept is simple enough, but It's hard to explain.
Shums Faruk
No attachment...
ASKER
I'm a bit new to the new interface of EE. I used to use it a lot, but it's been about a year. Shums, it says you made a comment, but I see no text. Am I missing something?
I just asked, "No attachment"
Strange you cannot see what comment I made :)
Strange you cannot see what comment I made :)
Hi Wizph1: Shums commented - "no attachment" .
You forgot to attach the file.
You forgot to attach the file.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Boy, I remember attaching it before submitting the original. I have not idea why it didn't make the trip. Sorry. I didn't realize the text "No attachment" was actually your comment, Shums.
BOB-Report-Test.xlsx
BOB-Report-Test.xlsx
ASKER
Neil, your idea I think is working perfectly! Just doing =row(split2)-row(split1) in the Split 2 box and then =row(split3)-row(split2) in the Split 3 box gives me the exact right numbers. When I add all splits up they come out exactly right. I can also drag the splits up or down and all counts readjust perfectly.
I've attached the file again with the new formulas in Split 2 and Split 3.
Now, one other question...is there a more elegant and simple formula for my Split 1 cell than what I have? Mine uses the INDIRECT fn and it gets a little ugly, albeit it calculates right. The simplest is always best.
BOB-Report-Test.xlsx
I've attached the file again with the new formulas in Split 2 and Split 3.
Now, one other question...is there a more elegant and simple formula for my Split 1 cell than what I have? Mine uses the INDIRECT fn and it gets a little ugly, albeit it calculates right. The simplest is always best.
BOB-Report-Test.xlsx
Hi witzph1,
Do you want every row to have formula or you just need the count for your condition. If your concern is just count then
Formula For Split1
Formula for Split3
Do you want every row to have formula or you just need the count for your condition. If your concern is just count then
Formula For Split1
=SUMPRODUCT((LEFT($A$3:$A$2225)={"A","B","C","D","E","F"})*1)=SUMPRODUCT((LEFT($A$3:$A$2225)={"G","H","I","J","K","L","M","N"})*1)Formula for Split3
=SUMPRODUCT((LEFT($A$3:$A$2225)={"O","P","Q","R","S","T","U","V","W","X","Y","Z"})*1)
In attached I have changed your NamedRange Formula to above
BOB-Report-Test_v1.xlsx
BOB-Report-Test_v1.xlsx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Shums and Neil, fascinating formulas. There are some pretty cool stuff you devised. I like the simplicity of what Neil suggested but see some value in what Shums suggested.
Thanks all for your great ideas!
Thanks all for your great ideas!