| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching data
How do i work out which formular to use for example.
I have 4000 plublication entries Column B and I need to fine Globe and Mail and Match it to Column D with the regions. I.e hpw many entries of Globe and Mail appear and match up to the National Region? -- WitkosRobinson |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Matching data
Hi,
First, not enought info - how do you determine they are in the National Region? Basically the formula would be =SUMPRODUCT(--(B1:B4000="Globe")+(B1:B4000="Mail"),--(D1:D4000="National Region")) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "kristy_6278" wrote: How do i work out which formular to use for example. I have 4000 plublication entries Column B and I need to fine Globe and Mail and Match it to Column D with the regions. I.e hpw many entries of Globe and Mail appear and match up to the National Region? -- WitkosRobinson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Matching data
Thanks so much Shane! Sorry for the lack of detail.
I have 3 months (different worksheets) July, August and September. Then the 4th workbook I am putting the results. All layouts are the same for each month workbook. I have one column called Service Lines, (Column J) and there are 4 service lines distributed through out the 400 lines. Then I have another column with regions (Column K) Again there are 3 criteria, National/Central, Easter or Western. I want to see which service lines match up to which region. I.e Toys, how many times does toys match up to National/Central. Does that make more sense? -- WitkosRobinson "Shane Devenshire" wrote: Hi, First, not enought info - how do you determine they are in the National Region? Basically the formula would be =SUMPRODUCT(--(B1:B4000="Globe")+(B1:B4000="Mail"),--(D1:D4000="National Region")) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "kristy_6278" wrote: How do i work out which formular to use for example. I have 4000 plublication entries Column B and I need to fine Globe and Mail and Match it to Column D with the regions. I.e hpw many entries of Globe and Mail appear and match up to the National Region? -- WitkosRobinson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Matching data
=SUMPRODUCT(--(July!J:J="AABS")+(July!K:K="National/Central"))
I tried this and got #NUM?? -- WitkosRobinson "Shane Devenshire" wrote: Hi, First, not enought info - how do you determine they are in the National Region? Basically the formula would be =SUMPRODUCT(--(B1:B4000="Globe")+(B1:B4000="Mail"),--(D1:D4000="National Region")) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "kristy_6278" wrote: How do i work out which formular to use for example. I have 4000 plublication entries Column B and I need to fine Globe and Mail and Match it to Column D with the regions. I.e hpw many entries of Globe and Mail appear and match up to the National Region? -- WitkosRobinson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Matching data
If you are using a version of Excel prior to 2007, trying to use a full
column (such as J:J) in SUMPRODUCT will give #NUM! You need to specify start and end points in the column, e.g. =SUMPRODUCT(--(July!J1:J100="AABS")+(July!K1:K100="National/Central")) But what are you trying to do with the formula? What is the double unary minus trying to do on the J term, as you're already doing an arithmetic operation on it by adding the K term? If you want the J and K terms to be combined with an AND, you may want =SUMPRODUCT(--(July!J1:J100="AABS"),--(July!K1:K100="National/Central")) or =SUMPRODUCT((July!J1:J100="AABS")*(July!K1:K100="N ational/Central")) Your current formula will count 1 for an exclusive OR of the J and K terms, but would count 2 if J and K terms were true on the same row. Are you sure that's what you want? If you want to combine the J and K terms with an OR, then perhaps =SUMPRODUCT(SIGN((July!J1:J100="AABS")+(July!K1:K1 00="National/Central"))) ? -- David Biddulph "kristy_6278" wrote in message ... =SUMPRODUCT(--(July!J:J="AABS")+(July!K:K="National/Central")) I tried this and got #NUM?? -- WitkosRobinson "Shane Devenshire" wrote: Hi, First, not enought info - how do you determine they are in the National Region? Basically the formula would be =SUMPRODUCT(--(B1:B4000="Globe")+(B1:B4000="Mail"),--(D1:D4000="National Region")) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "kristy_6278" wrote: How do i work out which formular to use for example. I have 4000 plublication entries Column B and I need to fine Globe and and Match it to Column D with the regions. I.e hpw many entries of Globe and Mail appear and match up to the National Region? -- WitkosRobinson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Matching data
=SUMPRODUCT(--(July!J2:J470 = "AABS"),--(July!K2:K470 = "National/Central"))
THIS WORKED LIKE A DREAM!! THANK YOU SO MUCH!! I can now start to calculate the results! Whoooooooooooo you have made my day! -- WitkosRobinson "David Biddulph" wrote: If you are using a version of Excel prior to 2007, trying to use a full column (such as J:J) in SUMPRODUCT will give #NUM! You need to specify start and end points in the column, e.g. =SUMPRODUCT(--(July!J1:J100="AABS")+(July!K1:K100="National/Central")) But what are you trying to do with the formula? What is the double unary minus trying to do on the J term, as you're already doing an arithmetic operation on it by adding the K term? If you want the J and K terms to be combined with an AND, you may want =SUMPRODUCT(--(July!J1:J100="AABS"),--(July!K1:K100="National/Central")) or =SUMPRODUCT((July!J1:J100="AABS")*(July!K1:K100="N ational/Central")) Your current formula will count 1 for an exclusive OR of the J and K terms, but would count 2 if J and K terms were true on the same row. Are you sure that's what you want? If you want to combine the J and K terms with an OR, then perhaps =SUMPRODUCT(SIGN((July!J1:J100="AABS")+(July!K1:K1 00="National/Central"))) ? -- David Biddulph "kristy_6278" wrote in message ... =SUMPRODUCT(--(July!J:J="AABS")+(July!K:K="National/Central")) I tried this and got #NUM?? -- WitkosRobinson "Shane Devenshire" wrote: Hi, First, not enought info - how do you determine they are in the National Region? Basically the formula would be =SUMPRODUCT(--(B1:B4000="Globe")+(B1:B4000="Mail"),--(D1:D4000="National Region")) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "kristy_6278" wrote: How do i work out which formular to use for example. I have 4000 plublication entries Column B and I need to fine Globe and and Match it to Column D with the regions. I.e hpw many entries of Globe and Mail appear and match up to the National Region? -- WitkosRobinson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Matching a column of new data to existing larger data set. | Excel Worksheet Functions | |||
| Matching identical data using data only once in the matching proce | Excel Discussion (Misc queries) | |||
| Data matching | Excel Discussion (Misc queries) | |||
| Can I merge data in 2 sheets matching rows of data by last name? | Excel Discussion (Misc queries) | |||
| Matching data and linking it to the matching cell | Links and Linking in Excel | |||
Hybrid Mode
