Counting specific text in two columns.

[Pank]

I have two columns (a and B) which contain the following:-

Column A:-

Not_Chased, Regret, Stockist, Ordered, Chased, Pending, Lost, Opp_Log

Column B:-

List of countries (e.g. China, U.K. Netherlands, Germany, Spain, Italy,
Czech).

I would like to produce a matrix as following showing say Not-Chased for all
the individual countries, Regret for all countries, Stockist for all
countries… (see below)

China Czech France Germany India Grand Totals
LOST
Not_Chased
OPP_LOG
ORDERED
PENDING
REGRET
STOCKIST
Total

Any assistance given will be appreciated.

 

[Pete_UK]

Assume your current data is in Sheet1. Set up another sheet in the way
you have shown, with countries in B1, C1, D1 etc, and LOST in A2,
Not_Chased in A3 etc. Put this formula in B2:

=SUMPRODUCT((Sheet1!$A$1:$A$100=$A2)*(Sheet1!$B$1:$B$100=B$1))

Adjust the range to suit how much data you have on Sheet1, then copy
this formula across and down as required. You might like to apply
conditional formatting to these cells such that if the cell contents
are zero then use a white foreground colour, so that zeros are not
shown.

Hope this helps.

Pete

 

[Dave Peterson]

You may want to look into a pivottable. If your data is laid out nicely
(and it sounds like it is), you could get some very nice summary reports pretty
quickly.

Here are a few 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/assistance/2002/articles/xlconPT101.aspx

 

[Pank]

Pete UK and Dave Peterson,

Firstly many thanks for taking the time to help me.

I have tried Pete UK's solution and unfortunately all the figures displayed
in the matrix are 0. The only things that I have changed in the formula is
the range to be 5000 as opposed to 100 and the start range to start in A2 B2.

Any ideas whay 0's are returned rather than the actual numbers for the
appropriate criteria?

Your assistance is appreciated.

 

[Pank]

Pete Uk,

Sort the displaying 0 problem by looking at the formula and worked out that
because I had transposed differently (i.e. column A was countries and rows
were Status) 0's were returned. Changed the formulae and all is well.

Once again many thanks to both you and Dave.

 

[Pete_UK]

You're welcome - glad you managed to suss it out.

Pete