Simple ? countif, 3 separate conditions across multi tabs

[Steve]

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 are:
between -100 and +100 &
bewteen -399 and + 399 ( not including the -100/+100 above) &
<-400 and >400.

Thanks,

Steve

 

[Spiky]

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 are:
between -100 and +100   &
bewteen -399 and + 399 ( not including the -100/+100 above)   &
<-400 and >400.

Thanks,

Steve

=SUMPRODUCT(--(ABS(P1100)<=100))
=SUMPRODUCT(--(ABS(P1100)<=400))-SUMPRODUCT(--(ABS(P1100)<=100))
=SUMPRODUCT(--(ABS(P1100)>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.

 

[Steve]

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(P1100)<=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,

 

[Pete_UK]

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(P1100)<=100),--(P1100<>""))
Y1: =SUMPRODUCT(--(ABS(P1100)<400))-X1
Z1: =SUMPRODUCT(--(ABS(P1100)>=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

 

[Spiky]

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'!

I had assumed you were going to use 26 different cells to see the
count for each sheet individually, too. Do you want it all in one
formula? Sumproduct can't do 3D ranges, and I don't think other built-
in functions can, either. I think you would have to repeat the formula
26 times in one cell. Or use a UDF that can handle 3D better. See the
very bottom.

Though I can't even get it to work on just one of the tabs, such as:
=SUMPRODUCT     'A'!    (--(ABS(P1100)<=100))

You need to put the sheet next to the range.
=SUMPRODUCT(--(ABS('A'!P1100)<=100))

Counting blank cells would be an issue. What would they get counted as ?

They would be included in the <100 count, because it will assume they
are zeroes. So you can add another calc to get rid of them. There's a
couple ways to do it, here's one:
=SUMPRODUCT(--(ABS('A'!P1100)<=100))-SUMPRODUCT(--('A'!P1100=""))

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

At some point I think you need the =. Otherwise it will not count 100
and 400.


So, after all that, here is a single formula that can give you the
first item, counting items from -100 and 100, inclusive, on all
sheets. This includes a UDF from morefunc. You can find morefunc via
Google or download.com.
=SUMPRODUCT(--(ABS(--THREED('A:Z'!A1:A29))<=100))-SUMPRODUCT(--(THREED
('A:Z'!A1:A29)=""))

 

[Steve]

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

 

[Shane Devenshire]

Hi,

In 2003 if you want to exclude blank cells from the counts, enter these
three formulas on each sheet:

=SUMPRODUCT(--(A1:A12>=-100),--(A1:A12<=100),--(A1:A12<>""))
=SUMPRODUCT(--(A1:A12>=-300),--(A1:A12<=300),--(A1:A12<>""))-C11
=COUNT(A1:A12)-SUM(C11:C12)

In each case the range to look at is column A. In this example the first
formula is in C11, the second in C12.

In 2007
=COUNTIFS(A1:A12,">=-100",A1:A12,"<=100",A1:A12,"<>")
=COUNTIFS(A1:A12,">=-399",A1:A12,"<=399",A1:A12,"<>")-C11
and the third formula is unchanged from the 2003 version.

Formulas in the same locations as in the 2003 examples.

To sum these results to a summary page
=SUM(A:Z!C11)
=SUM(A:Z!C12)
=SUM(A:Z!C13)

 

[Pete_UK]

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

X1: =SUMPRODUCT(--(ABS(P1100)<=100),--(P1100<>""))
Y1: =SUMPRODUCT(--(ABS(P1100)<400),--(P1100<>""))-X1
Z1: =SUMPRODUCT(--(ABS(P1100)>=400),--(P1100<>""))

Other instructions as before.

Hope this helps.

Pete

 

[Steve]

Thanks guys,

They all worked great. Much appreciated.
Have a great year.

Steve