Create a text based count with criteria from two differnt workbook

[Matt Kirby]

My master data sheet has multiple instances relateing to when a product was
ordered. I need to create an order count in a second workbook by verifing
against multiple cells. Below is a short example...

** Master Data

Course Type Date
Salad apple 15-Mar
Salad apple 17-Mar
Salad apple 17-Mar
Salad pear 15-Mar
Salad pear 1-Apr
Salad walnut 10-Mar
Dessert apple 12-Mar
Dessert banana 14-Mar
Dessert banana 14-Mar
Dessert banana 2-Apr
Dessert mango 19-Mar
Dessert mango 22-Mar

** Reporting Data

Course Type Order Count
Salad apple
Salad pear
Salad walnut
Dessert apple
Dessert banana
Dessert mango

The master data sheet I am working with has more than 14000 lines and more
than 500 types, so entering in each type/course would be extremely
cumbersome.

 

[Jason]

Matt,

Assuming your 'master data' fields are columns A,B and C and the
'Course' and 'Type' you want to count are in D1 and E1, try:

=sum((A1:A100=D1)*(B1:B100=E1))

And array enter it: CTRL SHIFT ENTER

So if D1 contains 'Salad', and E1 contains 'pear', the formula above
will count how many times 'Salad' and 'pear' appear (in adjacent cells),
in your master data.

This will also work if the master data is in another workbook.

Thew new COUNTIFS function introduced in Excel2007 is for multiple
condition counting, but will return a #VALUE! error if you use it to
count data on a second workbook that is closed.

There are many ways to do this. Let me know how you get on,

Jay
__

 

[Matt Kirby]

Jay,

It works perfectly... almost. As I copy the formula down through the
worksheet, the array is changing. I need to be able to change the reference
(D1 or E1) with out changing the range (A1:A100).

Any ideas?

Matt

 

[Dave Peterson]

=sum(($A$1:$A$100=D1)*($B$1:$B$100=E1))

The $ signs keep the range if the formula is copied somewhere else.

 

[Matt Kirby]

A big thank you to you both.

-Matt

=sum(($A$1:$A$100=D1)*($B$1:$B$100=E1))

The $ signs keep the range if the formula is copied somewhere else.