Counting (with filter) formula

[Piotr C.]

Hi,

I'm wondering if there's any formula / another way to have a formula
that calculates number of rows in another sheet with specified filter
conditions. Eg. in sheet1 / cell A1 I want to have a number of rows in
sheet2 where Z column value is higher than 2, and cell A2 = number of
rows in sheet2 where B column is equal to "ABC"...

Now I have to calculate this one by one using autofilter (pivot is not
sufficient) and it takes a lot of time~~

Is it possible? Thanks in advance for any suggestion.

 

[Roger Govier]

Hi Piotr

=COUNTIF(Sheet2!$Z:$Z,">2")
=COUNTIF(Sheet2!$B$B,"ABC")

 

[Piotr C.]

Roger Govier napisa³(a):

Hi Piotr

=COUNTIF(Sheet2!$Z:$Z,">2")
=COUNTIF(Sheet2!$B$B,"ABC")

Thank You!!

One more question - is it possible to use multiple conditions in one
formula as "AND" (eg. $Z:$Z,">2" AND $Y:$Y,"ABCD")?
Just tell me if it's possible, then I can study this case by myself...

Thanks,
Piotr

 

[Pete_UK]

COUNTIF (and SUMIF ) can only be used for a single condition. However,
you can use SUMPRODUCT or SUM(IF( ... )) as an array formula to build
up multiple conditions.

Hope this helps.

Pete

 

[Roger Govier]

Hi Piotr

Not with Countif.
If you have XL2007, there is COUNTIFS(), which does allow up to 127
conditions.

For earlier versions, you could use SUMPRODUCT()
(but note that in versions earlier than XL2007, you cannot use whole
column ranges)

=SUMPRODUCT((Sheet2!$Z1:$Z1000>2)*(Sheet2!$Y1:$Y1000="ABCD"))

You can keep adding further terms within the outer set of Sumproduct()
parentheses
Change the ranges to suit, (up to 65535) but ensure they are of equal
size
The above formula give a count.

If you wanted a SUM of values for rows matching the conditions similar
to the SUMIF function, then supposing column AA contained numeric
values, you could use
=SUMPRODUCT((Sheet2!$Z1:$Z1000>2)*(Sheet2!$Y1:$Y1000="ABCD")*Sheet2!$AA1:$AA1000)

For more help on Sumproduct take a look at Bob Philips excellent
discussion at
http://xldynamic.com/source/xld.SUMPRODUCT.html