Data Filter - count countries selected by filter

[martiano]

I have table for example like this and Im using function Subtotal to count all countries what are selected by filter.

I will for example choose by filter only countries "Asia" and "Europe".

And here is the result, what is ok.

My quesion is:

I want to count not only Total countries selected by filter, but count all countries on every continent selected by filter.
so the resualt should by in this example:
Total countries: 13
Africa: 0
America: 0
Asia: 10
Europe:3

What function or functions do I need to use to get this result? Thank you

 

[martiano]

here is the link for the file example

http://www.upload.ee/files/1352199/Data_filter.xlsx.html

 

[dfida]

You can use "countif".

 

[martiano]

Countif function count hide cells aswell, what I dont want. I want to count just continents what I have selected in filter.

 

[martiano]

Can anyone help me please?

 

[T. Valko]

Can anyone help me please?

Try this...

Entered in D24 and copied down as needed.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(D$2:D$21,ROW(D$2:D$21)-ROW(D$2),0,1)),--(D$2:D$21=C24))

 

[danzon]

^ beat me to it

 

[T. Valko]

how about in D24 and drag down

=SUMPRODUCT(SUBTOTAL(3,OFFSET(D2:D20,ROW(D2:D20)-ROW(D2),0,1)),--(D2:D20=C24))

Ya might want to make the row references absolute!

 

[martiano]

Try this...

Entered in D24 and copied down as needed.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(D$2:D$21,ROW(D$2:D$21)-ROW(D$2),0,1)),--(D$2:D$21=C24))

Doesnt work for me

Look

 

[T. Valko]

Doesnt work for me

Look

I couldn't see what that error message said.

Here's a small sample file that demonstrates this.

zzzMartiano.xls 14kb

http://cjoint.com/?AEucYLWDApa