Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Counting 10 Columns * X Number of Rows
I am trying to figure out how I can count the number of rows where Columns
AC:AM=10 when added together no matter what numbers/integers are in any of those columns per row. I'm certain that it is starring me in the face and laughing at me but seeing as it's been an extremely long day I just cannot figure it out. Can anyone please help me? Thanks in Advance! Rob |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Counting 10 Columns * X Number of Rows
I would add another column to sum the range...for instance in AQ1 I would
enter: =SUM(AC1:AM1) ....and copy that cell down. Now you have column you can do a simple COUNTIF on: =COUNTIF(AQ:AQ,10) I'm sure there are some hefty arrays, but I like to keep the plumbing simple. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Rob" wrote: I am trying to figure out how I can count the number of rows where Columns AC:AM=10 when added together no matter what numbers/integers are in any of those columns per row. I'm certain that it is starring me in the face and laughing at me but seeing as it's been an extremely long day I just cannot figure it out. Can anyone please help me? Thanks in Advance! Rob |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Counting 10 Columns * X Number of Rows
Thanks for the reply/suggestion. I was afraid that someone was going to
suggest that. I was and am hoping to avoid adding yet another column of data/calculations. If I were to utilize that method then I would be adding another 600+ cells of formulae to have evaluated on top of the one that will count them all. Hopefully there's another method. If not then I am destined to do just that I suppose. Thanks Again. "JBeaucaire" wrote: I would add another column to sum the range...for instance in AQ1 I would enter: =SUM(AC1:AM1) ...and copy that cell down. Now you have column you can do a simple COUNTIF on: =COUNTIF(AQ:AQ,10) I'm sure there are some hefty arrays, but I like to keep the plumbing simple. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Rob" wrote: I am trying to figure out how I can count the number of rows where Columns AC:AM=10 when added together no matter what numbers/integers are in any of those columns per row. I'm certain that it is starring me in the face and laughing at me but seeing as it's been an extremely long day I just cannot figure it out. Can anyone please help me? Thanks in Advance! Rob |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Counting 10 Columns * X Number of Rows
I think you misunderstand. Putting the formula in 600 cells and copying down
once actually reduces overhead on your sheet. Simple fast functions like SUM that can run once and aren't volatile keep your sheet peppy and happy. SUM is basic and fast. Countif is, too. To put ALL that plumbing into a single cell requires some heavy lifting. Most really clever formulas of this kind do not reduce calculations, they just cram them all into one cell. Worse, many of them do it in a way that results in MORE calculations, not less. Just have to stop thinking that fewer cells = more efficient. My experience is that elegant and efficient don't go hand in hand. You could hide the ugly helper column if it bother you... hehe. Hope that helps. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Rob" wrote: Thanks for the reply/suggestion. I was afraid that someone was going to suggest that. I was and am hoping to avoid adding yet another column of data/calculations. If I were to utilize that method then I would be adding another 600+ cells of formulae to have evaluated on top of the one that will count them all. Hopefully there's another method. If not then I am destined to do just that I suppose. Thanks Again. "JBeaucaire" wrote: I would add another column to sum the range...for instance in AQ1 I would enter: =SUM(AC1:AM1) ...and copy that cell down. Now you have column you can do a simple COUNTIF on: =COUNTIF(AQ:AQ,10) I'm sure there are some hefty arrays, but I like to keep the plumbing simple. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Rob" wrote: I am trying to figure out how I can count the number of rows where Columns AC:AM=10 when added together no matter what numbers/integers are in any of those columns per row. I'm certain that it is starring me in the face and laughing at me but seeing as it's been an extremely long day I just cannot figure it out. Can anyone please help me? Thanks in Advance! Rob |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Counting 10 Columns * X Number of Rows
Try...
=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(AC2:AM100,ROW(AC2:AM100)-ROW(AC2),0,1))= 10)) Adjust the range accordingly. Hope this helps! http://www.xl-central.com In article , Rob wrote: I am trying to figure out how I can count the number of rows where Columns AC:AM=10 when added together no matter what numbers/integers are in any of those columns per row. I'm certain that it is starring me in the face and laughing at me but seeing as it's been an extremely long day I just cannot figure it out. Can anyone please help me? Thanks in Advance! Rob |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Counting 10 Columns * X Number of Rows
Awesome! Perfect! that works just like I was hoping for! Thank You Soo
Very much! Now If I only understood how you did it. :/ Thanks Very Much! Rob "Domenic" wrote: Try... =SUMPRODUCT(--(SUBTOTAL(9,OFFSET(AC2:AM100,ROW(AC2:AM100)-ROW(AC2),0,1))= 10)) Adjust the range accordingly. Hope this helps! http://www.xl-central.com In article , Rob wrote: I am trying to figure out how I can count the number of rows where Columns AC:AM=10 when added together no matter what numbers/integers are in any of those columns per row. I'm certain that it is starring me in the face and laughing at me but seeing as it's been an extremely long day I just cannot figure it out. Can anyone please help me? Thanks in Advance! Rob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting number of rows | New Users to Excel | |||
Autofill counting up Columns and Rows! | Excel Worksheet Functions | |||
Counting the number of rows | Excel Worksheet Functions | |||
Counting rows of blanks across certain columns | New Users to Excel | |||
Counting rows with 3 columns | Excel Worksheet Functions |