Okay, Steve, try these amendments to ignore blank cells in all cases:

X1: =SUMPRODUCT(--(ABS(P1:P100)<=100),--(P1:P100<""))
Y1: =SUMPRODUCT(--(ABS(P1:P100)<400),--(P1:P100<""))-X1
Z1: =SUMPRODUCT(--(ABS(P1:P100)=400),--(P1:P100<""))

Other instructions as before.

Hope this helps.

Pete

On Jan 2, 5:36*pm, Steve wrote:
Almost there. X1 is working great. But Y1 & Z1 not quite there yet.
Y1 should be counting between -399 and + 399, not including the X1 (-X1)
Z1 should be counting anything <400 and anything 400.

Gottit on the shifting and the combining on the summary.

Steve



"Pete_UK" wrote:
Steve,

it won't work well across multiple tabs (and your attempted amendment
has the wrong syntax), so put these formulae in the same cells in each
tab, eg:

X1: * * =SUMPRODUCT(--(ABS(P1:P100)<=100),--(P1:P100<""))
Y1: * *=SUMPRODUCT(--(ABS(P1:P100)<400))-X1
Z1: * * =SUMPRODUCT(--(ABS(P1:P100)=400))

If you group the sheets A to Z together first (by selecting sheet A,
holding the SHIFT key down and clicking on the Z sheet tab), then you
will only need to type the formula once into X1, Y1 and Z1. Remember
to ungroup the sheets afterwards, by right-clicking on a sheet tab and
selecting Ungroup sheets).

Then you can combine them on your summary sheet like this:

=SUM(A:Z!X1)
=SUM(A:Z!Y1)
=SUM(A:Z!Z1)

Hope this helps.

Pete

On Jan 2, 4:47 pm, Steve wrote:
I can't seem to get it to work. *The formula cell will be on a different tab
than what's to be counted, but I want it to count the P column on 26 other
tabs, such as 'A:Z'!
Though I can't even get it to work on just one of the tabs, such as:
=SUMPRODUCT * * 'A'! * *(--(ABS(P1:P100)<=100))

Do I have the tab identifier 'A'! wrong, or will sumproduct even work across
multi tabs ? Though if that is the case, I guess I could use these formulas
on each tab, then count each of the appropraite cells on the rollup tab.
Counting blank cells would be an issue. What would they get counted as ?

The = to can probably be removed, as the < account for it as I needed.

Thanks,

"Spiky" wrote:
On Jan 2, 9:46 am, Steve wrote:
If I have 26 tabs A:Z,
and in the P columns of each tab, there are various numbers such as:

0,-100, -400,-70, 100, 400, 70, etc.

I'd like to count how many a
between -100 and +100 * &
bewteen -399 and + 399 ( not including the -100/+100 above) * &
<-400 and 400.

Thanks,

Steve

=SUMPRODUCT(--(ABS(P1:P100)<=100))
=SUMPRODUCT(--(ABS(P1:P100)<=400))-SUMPRODUCT(--(ABS(P1:P100)<=100))
=SUMPRODUCT(--(ABS(P1:P100)400))

Although this will count blank cells, too. Is that an issue? And I'm
not sure where you wanted the "equal to" counted, you weren't clear on
that.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -