auto count-if with subtotal's autofiltered layout

[npuleio]

Hello everyone!

I have a sheet that has two columns: one represents cities and the
other represents devices+code device. Firstly I don't know by prior
how many rows they are; so first, I order cities column, then I put
subtotals grouped by city that counts how many cities they are and I
choose to show subtotals every time city changes then I apply
AutoFilter ON. In this way I can see like a sheet grouped with many
subtotals that tells me how many cities they are.
But really I want to have subtotals that tells me how many printers
there are by each city and how many servers there are always by each
city using something like countif() but still showing the layout in
the same sheet with autofilter on so when I choose a certain city, it
hides other cities and subtotals and keeps showing me the subtotals
for that city.
And I wouldn't like to have pivots table...

How I can do that?

Thanks you to all in advance for this little help.

Ciao,
Luigi

 

[Bernard Liengme]

Have you looked into using a Pivot Table?
Have a look here and come back if you have questions

http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2005/06/23/download-pivottable-parameters/
best wishes

 

[npuleio]

Have you looked into using a Pivot Table?
Have a look here and come back if you have questions

http://www.cpearson.com/excel/pivot...m/archives/2005/06/23/download-pivottable-par...
best wishes

Thanks for your answer,
but I don't want to make pivots...in my question I said I wouldn't use
pivots because I'd like the result directly in the main sheet with
same behavior of subtotals...
Isn't there any solution in this way?...

Thanks again in advance.
Ciao,
Luigi

 

[Bob Phillips]

Just add another layer of subtotals.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[npuleio]

Just add another layer of subtotals.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Hello Bob,

another layer of subtotals?... I think I'm a bit lost.... how I can
add another layer of subtotals?...

Thanks in advance.
Ciao, Luigi

 

[Dave Peterson]

Data|Subtotals
Make sure you uncheck "replace current subtotals"

 

[npuleio]

Dave Peterson ha scritto:

Data|Subtotals
Make sure you uncheck "replace current subtotals"

Ah that's it... but those subtotals are like definite so I can't put
personalized formula like countif... or it is possible in some way?...

thanks again in advance.
Ciao, luigi

 

[Dave Peterson]

=subtotal() supports lots of options. Look at excel's help for all those
options.

Maybe there's something built into excel that counts/sums/averages what you
want.