Double filtering
 

Hi everyone

I have two columns with non-numerical data that is repeating over 100 rows
(soon we're going to get over 1,000 new observations):

1. Company Name: Fedex, DHL, UPS
2. Country: US, Germany, Belgium etc....

I would like to know for example how many packages sent with Fedex are going
to US, Germany etc.
Of course this can be done with filtering the first column by Company Name
(ex: FedEx), and then counting all the US (second filter), Germany etc........

.... but can I do it in any way with a formula (any double COUNTIF)?

Thanks!

It sounds like a nice spot to use Data|Pivottables.

Select your range
data|pivottables

Drag the header for company to the row field
drag the header for country to the row field
drag the header for company (again) to the data field
(It should say "Count of..." If it says anything else, double click on it and
choose "count".)

Finish up.

You might even want to try dragging company name to the column Field (instead of
the row field). You'll end up with a nice table.

If you want to read more about the pivottable stuff, you may want to look at
some links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx


Dimmer wrote:

Hi everyone

I have two columns with non-numerical data that is repeating over 100 rows
(soon we're going to get over 1,000 new observations):

1. Company Name: Fedex, DHL, UPS
2. Country: US, Germany, Belgium etc....

I would like to know for example how many packages sent with Fedex are going
to US, Germany etc.
Of course this can be done with filtering the first column by Company Name
(ex: FedEx), and then counting all the US (second filter), Germany etc........

... but can I do it in any way with a formula (any double COUNTIF)?

Thanks!

--

Dave Peterson

With data in A1:B1000, company name in D1, country name in E1

=SUMPRODUCT((A1:A1000=D1)*(B1:B1000=E1)) in C1

Alan Beban

Dimmer wrote:
Hi everyone

I have two columns with non-numerical data that is repeating over 100 rows
(soon we're going to get over 1,000 new observations):

1. Company Name: Fedex, DHL, UPS
2. Country: US, Germany, Belgium etc....

I would like to know for example how many packages sent with Fedex are going
to US, Germany etc.
Of course this can be done with filtering the first column by Company Name
(ex: FedEx), and then counting all the US (second filter), Germany etc........

... but can I do it in any way with a formula (any double COUNTIF)?

Thanks!