| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a text based count with criteria from two differnt workbook
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create a text based count with criteria from two differnt workbook
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 wrote: 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create a text based count with criteria from two differnt work
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 "Jason" wrote: 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 wrote: 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create a text based count with criteria from two differnt work
=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 wrote: 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 "Jason" wrote: 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 wrote: 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. -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create a text based count with criteria from two differnt work
A big thank you to you both.
-Matt "Dave Peterson" wrote: =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 wrote: 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 "Jason" wrote: 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 wrote: 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. -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count based on two different criteria | Excel Worksheet Functions | |||
| Count text cells based on two criteria | Excel Worksheet Functions | |||
| I want formula for multiple criteria using ih differnt column | Excel Worksheet Functions | |||
| Insert text from another workbook based on criteria | Excel Worksheet Functions | |||
| count cells,containing text, based on more than one criteria? | Excel Worksheet Functions | |||
Linear Mode
