| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match data, count data and report to diffrent sheet.
I have a work sheet that I want to report to another page. I am putting
togehter a production tracking tool, and need to pull information from two diffren colums in the work sheet and count them. Basically what I want is to be able to if v2:V690 = 8386 and r2:r690 = 1/20/09 then count 1 for every time it happes in the same row. I've tried this several diffrent was if then, count, sum, what am I doing wrong and how do I do it right. this was the last effort =COUNTIFS(IF(LCR!V2:V690,B4),(LCR!R2:R690,G3)) Please help! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match data, count data and report to diffrent sheet.
Hi,
v2:V690 = 8386 and r2:r690 = 1/20/09 =SUMPRODUCT(--(V2:V690=A1),--(R2:R690=B1)) And in A1 enter 8386 and in B1 enter 1/20/09 -- If this helps, please click the Yes button. Cheers, Shane Devenshire "George W. W." wrote: I have a work sheet that I want to report to another page. I am putting togehter a production tracking tool, and need to pull information from two diffren colums in the work sheet and count them. Basically what I want is to be able to if v2:V690 = 8386 and r2:r690 = 1/20/09 then count 1 for every time it happes in the same row. I've tried this several diffrent was if then, count, sum, what am I doing wrong and how do I do it right. this was the last effort =COUNTIFS(IF(LCR!V2:V690,B4),(LCR!R2:R690,G3)) Please help! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match data, count data and report to diffrent sheet.
Shane & Francis,
I appreciate you intrest in helping with this problem. I tried the formulas and it is still not working properly, it now always reports as 0 and should actually be reporting as 4 in this instance. Any other ideas? "Shane Devenshire" wrote: Hi, v2:V690 = 8386 and r2:r690 = 1/20/09 =SUMPRODUCT(--(V2:V690=A1),--(R2:R690=B1)) And in A1 enter 8386 and in B1 enter 1/20/09 -- If this helps, please click the Yes button. Cheers, Shane Devenshire "George W. W." wrote: I have a work sheet that I want to report to another page. I am putting togehter a production tracking tool, and need to pull information from two diffren colums in the work sheet and count them. Basically what I want is to be able to if v2:V690 = 8386 and r2:r690 = 1/20/09 then count 1 for every time it happes in the same row. I've tried this several diffrent was if then, count, sum, what am I doing wrong and how do I do it right. this was the last effort =COUNTIFS(IF(LCR!V2:V690,B4),(LCR!R2:R690,G3)) Please help! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match data, count data and report to diffrent sheet.
Hi George
I have tested the formula before I posted and its work fine. Are your date format consistent? meaning that both the dataset and B1 should be formatted as MM/DD/YY format Do you have other data in your range? -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "George W. W." wrote: Shane & Francis, I appreciate you intrest in helping with this problem. I tried the formulas and it is still not working properly, it now always reports as 0 and should actually be reporting as 4 in this instance. Any other ideas? "Shane Devenshire" wrote: Hi, v2:V690 = 8386 and r2:r690 = 1/20/09 =SUMPRODUCT(--(V2:V690=A1),--(R2:R690=B1)) And in A1 enter 8386 and in B1 enter 1/20/09 -- If this helps, please click the Yes button. Cheers, Shane Devenshire "George W. W." wrote: I have a work sheet that I want to report to another page. I am putting togehter a production tracking tool, and need to pull information from two diffren colums in the work sheet and count them. Basically what I want is to be able to if v2:V690 = 8386 and r2:r690 = 1/20/09 then count 1 for every time it happes in the same row. I've tried this several diffrent was if then, count, sum, what am I doing wrong and how do I do it right. this was the last effort =COUNTIFS(IF(LCR!V2:V690,B4),(LCR!R2:R690,G3)) Please help! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match data, count data and report to diffrent sheet.
yes, I have multiple welder numbers 15 diffrent number. 8386 is one of 15
possible numbers. the date formats are the same on both work sheets. I've been working on this forever and it still isn't working. any other ideas, please help. "francis" wrote: Hi George I have tested the formula before I posted and its work fine. Are your date format consistent? meaning that both the dataset and B1 should be formatted as MM/DD/YY format Do you have other data in your range? -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "George W. W." wrote: Shane & Francis, I appreciate you intrest in helping with this problem. I tried the formulas and it is still not working properly, it now always reports as 0 and should actually be reporting as 4 in this instance. Any other ideas? "Shane Devenshire" wrote: Hi, v2:V690 = 8386 and r2:r690 = 1/20/09 =SUMPRODUCT(--(V2:V690=A1),--(R2:R690=B1)) And in A1 enter 8386 and in B1 enter 1/20/09 -- If this helps, please click the Yes button. Cheers, Shane Devenshire "George W. W." wrote: I have a work sheet that I want to report to another page. I am putting togehter a production tracking tool, and need to pull information from two diffren colums in the work sheet and count them. Basically what I want is to be able to if v2:V690 = 8386 and r2:r690 = 1/20/09 then count 1 for every time it happes in the same row. I've tried this several diffrent was if then, count, sum, what am I doing wrong and how do I do it right. this was the last effort =COUNTIFS(IF(LCR!V2:V690,B4),(LCR!R2:R690,G3)) Please help! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match data, count data and report to diffrent sheet.
Hey,
I finally figured it out if any one else runs into this try this formula =SUMPRODUCT((LCR!$V$2:$V$690=$B4)*(LCR!$R$2:$R$690 =C$3)) not only does it work but you can drag it across and then down to populate your data fields. Thanks Francis and Devenshire you guys put me on the right track. "George W. W." wrote: yes, I have multiple welder numbers 15 diffrent number. 8386 is one of 15 possible numbers. the date formats are the same on both work sheets. I've been working on this forever and it still isn't working. any other ideas, please help. "francis" wrote: Hi George I have tested the formula before I posted and its work fine. Are your date format consistent? meaning that both the dataset and B1 should be formatted as MM/DD/YY format Do you have other data in your range? -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "George W. W." wrote: Shane & Francis, I appreciate you intrest in helping with this problem. I tried the formulas and it is still not working properly, it now always reports as 0 and should actually be reporting as 4 in this instance. Any other ideas? "Shane Devenshire" wrote: Hi, v2:V690 = 8386 and r2:r690 = 1/20/09 =SUMPRODUCT(--(V2:V690=A1),--(R2:R690=B1)) And in A1 enter 8386 and in B1 enter 1/20/09 -- If this helps, please click the Yes button. Cheers, Shane Devenshire "George W. W." wrote: I have a work sheet that I want to report to another page. I am putting togehter a production tracking tool, and need to pull information from two diffren colums in the work sheet and count them. Basically what I want is to be able to if v2:V690 = 8386 and r2:r690 = 1/20/09 then count 1 for every time it happes in the same row. I've tried this several diffrent was if then, count, sum, what am I doing wrong and how do I do it right. this was the last effort =COUNTIFS(IF(LCR!V2:V690,B4),(LCR!R2:R690,G3)) Please help! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match data, count data and report to diffrent sheet.
I don't have 2007 version to test for COUNTIFS
but you can try this =SUMPRODUCT(--(LCR!V2:V690=B4),--(LCR!R2:R690=G3)) this assume that B4 hold the value 8386 and G3 hold the date 1/20/09 the date format need to be the same as your data -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "George W. W." wrote: I have a work sheet that I want to report to another page. I am putting togehter a production tracking tool, and need to pull information from two diffren colums in the work sheet and count them. Basically what I want is to be able to if v2:V690 = 8386 and r2:r690 = 1/20/09 then count 1 for every time it happes in the same row. I've tried this several diffrent was if then, count, sum, what am I doing wrong and how do I do it right. this was the last effort =COUNTIFS(IF(LCR!V2:V690,B4),(LCR!R2:R690,G3)) Please help! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match data, count data and report to diffrent sheet.
Further to my post, I am assuming that you want this in another sheet
hence the LCR!. If this is not the case, the formula can be shorten to just =SUMPRODUCT(--(V2:V690=B4),--(R2:R690=G3)) -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "francis" wrote: I don't have 2007 version to test for COUNTIFS but you can try this =SUMPRODUCT(--(LCR!V2:V690=B4),--(LCR!R2:R690=G3)) this assume that B4 hold the value 8386 and G3 hold the date 1/20/09 the date format need to be the same as your data -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "George W. W." wrote: I have a work sheet that I want to report to another page. I am putting togehter a production tracking tool, and need to pull information from two diffren colums in the work sheet and count them. Basically what I want is to be able to if v2:V690 = 8386 and r2:r690 = 1/20/09 then count 1 for every time it happes in the same row. I've tried this several diffrent was if then, count, sum, what am I doing wrong and how do I do it right. this was the last effort =COUNTIFS(IF(LCR!V2:V690,B4),(LCR!R2:R690,G3)) Please help! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Report data from cell if dates match | Excel Discussion (Misc queries) | |||
| Report data from cell if dates match | Excel Discussion (Misc queries) | |||
| Report data from cell if dates match | Excel Discussion (Misc queries) | |||
| Report data from cell if dates match | Excel Discussion (Misc queries) | |||
| match data to another sheet and return all the data on that row | Excel Worksheet Functions | |||
Hybrid Mode
