Count text from the results of a filter

[cboz]

In Excel I have autofilterd in column J and I want to count the number of times "M" is in column F. In other words I need something like Countif visible range = M. It's probably easy but I haven't been able to find an example.

 

[SkipVought]

Hi,

What's your filter criteria on column J?
[tt]
=SUMPRODUCT((J2:J8="X")*(F2:F8="M"))
[/tt]
assuming that your criteria in J were X and your range is rows 2 to 8.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[cboz]

I don't understand the formula but it works. Thanks

 

[SkipVought]

[tt]
ColF ColJ
X
M
M X
X
M X

X
[/tt]
here's the results of the elements of the formula
[tt]
F2:F8="M" J2:J8="X"
FALSE TRUE
TRUE FALSE
TRUE TRUE
FALSE TRUE
TRUE TRUE
FALSE FALSE
FALSE TRUE
[/tt]
which equates to
[tt]
F2:F8="M" J2:J8="X"
0 1
1 0
1 1
0 1
1 1
0 0
0 1
[/tt]
so when you take the SUMPRODUCT
[tt]
(F2:F8="M")*(J2:J8="X")
=0 0 1
=0 1 0
=1 1 1
=0 0 1
=1 1 1
=0 0 0
=0 0 1
--
2
[/tt]



Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[cboz]

Eureka! The light bulb is on. Thanks.