--(B2:B100="A")

This expression will return an array of either TRUE or FALSE:

(B2:B100="A")

B2: X
B3: A
B4: A
B5: C

B2="A" = FALSE
B3="A" = TRUE
B4="A" = TRUE
B5="A" = FALSE

SUMPRODUCT calculates numbers so we have to convert those logical TRUE and
FALSE to numbers. The TWO adjacent minus signs, known as double unary, is
one way to do that.

--TRUE = 1
--FALSE = 0

--(B2="A") = 0
--(B3="A") = 1
--(B4="A") = 1
--(B5="A") = 0

So we end up with an array of 1s and 0s:

{0;1;1;0}

The result of the SUBTOTAL function is also an array of 1s and 0s. For
example: {0;1;1;1}.

These 2 arrays are then multiplied together to arrive at the final result of
the formula:

Subtotal......B2:B5="A"
{0;1;1;1}*{0;1;1;0}

0*0 = 0
1*1 = 1
1*1 = 1
1*0 = 0

SUMPRODUCT({0;1;1;0}) = 2

See this for more info:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
Hi.
I was wondering if you could explain the occurence of TWO adjacent minus
signs part way through this formula.
Thanks,
David

"T. Valko" wrote:

This will "COUNTIF" B2:B100 = "A" in a filtered list:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))

--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
do anybody know, how to use "countif" with filtered lists?

the function subtotal does not allow conditions (e.g. "name")
the function countif does it, but it count hidden cells, too

thank you for your ideas

"N Harkawat" wrote:

=subtotal(2,a1:a1000)

"Counting filtered data." <Counting filtered
wrote in message
...
I have an excel spread sheet table that I filter. I would like to be
able
to
count the number of cells in the filtered data. Anyone know how to
do
this?
CountA returns the number of cells in the unfiltered data.
Tom