COUNTIF On Multiple Lists Causing Problems

  • Thread starter Thread starter Jimmy F Burbs
  • Start date Start date
J

Jimmy F Burbs

I've been at this for a couple of days and I can't seem to figure it.
know it should be simple, so here goes...

I have a list of location codes in a spreadsheet (upto 15000 in list
and in another spreadsheet I have a list of those location codes spli
down by regions (upto 150 in list).

I want to count the number of times the second list appears in th
first (counting every instance of the list and not just 1 bein
returned for 5 items of the same type), but I want to do this in
single formula and COUNTIF only works if I count each item seperatel
then sum them at the end. I need to do this for 45 regions of 15
locations. It's annoying to think I have to do each COUNTIF seperately
I know there must be a simple answer to this, but I can't see the woo
for the trees in this one.

Any help would be great..
 
Jimmy

Post back with a text example of how your data is laid out. I suspect the
best option is to use SUMPRODUCT()

Andy.
 
Fairly simply really...

Workbook 1, Sheet 1 contains a column with data labelled :

Location Code Time Call ID User
036200
036100
035200
125600
142000
032600
036200
158900
etc..

There can be numerous entries for each code in this master call list.

The region workbook has sheets with lists of all codes for each regio
as such :

Location code Region
003200 NW - BF1
003300 NW - BF1
003600 NW - BF1
etc..

I want to create a third sheet that automatically searches the raw cal
data and returns a value for everything that falls into NW - BF1
SCOTGLAS - BF2, SCOTEDIN - BF3 etc, so I can automate the manual cal
analysis that goes on
 
Jimmy.

I can't quite follow what you are trying to do. The Location Codes you have
are different in each list. I suggest you have a look at SUMPRODUCT(), which
enables you to do array calculations. Send me you sheet, if you like, and
I'll have look at it.

Andy.
 
I am trying to find out the number of times the items in one Workboo
Column appear in a Different Workbook Column(counting all instances)
using a single formula.

IE :

1
2
3

appears in

1
2
3
4
5
6
1
2
3
4
5
6

6 times
(1 appears 2 times, 2 appears 2 times and 3 appears 2 times)

I want to do this for my lists using a single formula, but
COUNTIF([PATH1]A?:A?,[PATH2]A?:A?) doesn't work
(in the example above it returns 0)

So I'm having to do them separately IE :
COUNTIF([PATH1]A?:A?,[PATH2]A?) then SUM the list of values returned.

I've tried DCOUNT as well and this returns a really weird result. (Th
example above returns 5 with DCOUNT)
 
Jimmy.

I can't see a way to do that - other than to join the COUNTIFs together with
the + sign (not practical). If I was doing it, I would use a hidden column
somewhere to do the COUNTIFs and have a cell which sums the column.
Sorry I can't think of any other way.

Andy.
 
Back
Top