Help Counting 10 Columns * X Number of Rows

[Rob]

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

 

[JBeaucaire]

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.

 

[Rob]

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]

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.

 

[Domenic]

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

 

[Rob]

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