LinkBack URL
About LinkBacks
Dear Gurus,
How do I create a count of zones at each subtotal? Original file is rather large.
Thank you!
Dear Gurus,
How do I create a count of zones at each subtotal? Original file is rather large.
Thank you!
What results do you want in H16?
Why a simple sum of column H wouldn't be sufficient?
Ron
Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad
Kindly
[1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
[2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
[3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated
So would the count for the "Sanchez" be 35? If so, following Tiger's lead...
in I2
=IF(ISNUMBER(SEARCH("total",A2)),SUM(IF(COUNTIF($A$1:A1,"*total*"),INDEX($G$1:G1,LOOKUP(2,1/(ISNUMBER(SEARCH("total",$A$1:A1))),ROW($1:1))),$G$1):G1),"")
Is that what you are looking for?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Perhaps, in I2:
confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.
Please Login or Register to view this content.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
I need the number of zones counted. So Route 1 (Sanchez) has 14 zones from Row 2 through Row 15. I need a count of the numbers, not the sum of them. Thank you.Originally Posted by ChemistB
One more thing: I need the result in G16. Thanks!
Have you tested my suggestion?
I got a #NAME? error.
If you are looking to count UNIQUE zones in the group & zones always be NUMERIC values then try this in H2 with just ENTER, then copy down.
Please Login or Register to view this content.
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
Using your posted workbook, try this....
• Put this formula in cell H2: =COUNTIF($C$1:$C1,$C1)
• Copy cell H2
• Select the entire Col_G
• F5...(shortcut for Go To)
...Click: Special cells
...Click: Blanks
...Click: OK
• Press ENTER...(to paste that formula into those blank cells)
• Press ESC to exit copy mode.
Is that something you can work with?
Last edited by Ron Coderre; 05-09-2012 at 07:51 PM. Reason: Made the formula much shorter
If you are not using EXCEL2007 or later, you will get the error since I use IFERROR() function.
Sorry; I should have let everybody know that this task is in Excel 2003.
Wow, this was the simplest and works perfectly. Thank you so much,
Eva
Last edited by kisboros; 05-10-2012 at 12:26 PM. Reason: wrong response
You're very welcome...I'm glad I could help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks