Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel formulas
Hi can any one help me on this
In sheet1 we have the data Date Task name Remarks 9/15/2005 Incoming OK 9/15/2005 Outgoing OK 9/15/2005 Incoming Not OK 9/16/2005 Outgoing OK 9/16/2005 Outgoing OK 9/16/2005 Incoming OK In sheet2 i need data for that particular date of that task only if it is OK Eg: - on 15 - Sep it should 1 only one record in total if it is OK and task is Incomig On 16/sep it should be 2 outging and 1 incoming Pls help me. |
#2
|
|||
|
|||
Assuming Date, Task Name, and Remarks are entered in columns A, B, and C of
sheet1 respectively - on sheet2 enter the following: A B C 1 9/15/05 Incoming 2 9/15/05 Outgoing In cell C1 enter =SUMPRODUCT(--(Sheet1!$A$1:$A$65535=A1),--(Sheet1!$B$1:B65535=B1),--(Sheet1!$C$1:C65535="OK")) and copy down to cell C2, C3, etc. This will count the number of items in your table that match the given date, incoming/outgoing, and are OK. Is this what you are wanting? "dj_george" wrote: Hi can any one help me on this In sheet1 we have the data Date Task name Remarks 9/15/2005 Incoming OK 9/15/2005 Outgoing OK 9/15/2005 Incoming Not OK 9/16/2005 Outgoing OK 9/16/2005 Outgoing OK 9/16/2005 Incoming OK In sheet2 i need data for that particular date of that task only if it is OK Eg: - on 15 - Sep it should 1 only one record in total if it is OK and task is Incomig On 16/sep it should be 2 outging and 1 incoming Pls help me. |
#3
|
|||
|
|||
Thanks it works, just one more thing if the remark also says Reviewed and i
need the data for both "OK" and "Reviewed" or that particular date and task. Please advise. "JMB" wrote: Assuming Date, Task Name, and Remarks are entered in columns A, B, and C of sheet1 respectively - on sheet2 enter the following: A B C 1 9/15/05 Incoming 2 9/15/05 Outgoing In cell C1 enter =SUMPRODUCT(--(Sheet1!$A$1:$A$65535=A1),--(Sheet1!$B$1:B65535=B1),--(Sheet1!$C$1:C65535="OK")) and copy down to cell C2, C3, etc. This will count the number of items in your table that match the given date, incoming/outgoing, and are OK. Is this what you are wanting? "dj_george" wrote: Hi can any one help me on this In sheet1 we have the data Date Task name Remarks 9/15/2005 Incoming OK 9/15/2005 Outgoing OK 9/15/2005 Incoming Not OK 9/16/2005 Outgoing OK 9/16/2005 Outgoing OK 9/16/2005 Incoming OK In sheet2 i need data for that particular date of that task only if it is OK Eg: - on 15 - Sep it should 1 only one record in total if it is OK and task is Incomig On 16/sep it should be 2 outging and 1 incoming Pls help me. |
#4
|
|||
|
|||
I would include headers on Sheet2.
A B C D 1 DATE TASK REVIEWED OK 2 9/15/05 Incoming 3 9/15/05 Outgoing In cell C2 enter =SUMPRODUCT(--(Sheet1!$A$1:$A$65535=$A2),--(Sheet1!$B$1:B$65535=$B2),--(Sheet1!$C$1:C$65535=C$1)) then copy across to cell D2, and down as far as you need. I also had to make a correction to a previous mistake - I had omitted the "$" in front of "65535" in my previous post - so be sure to include the "$" in your formula (otherwise you will get an error when you start copying). |
#5
|
|||
|
|||
Thanks, It works. Just one clarification. Can't we calculate Reviewed and OK
records in the same column. "JMB" wrote: I would include headers on Sheet2. A B C D 1 DATE TASK REVIEWED OK 2 9/15/05 Incoming 3 9/15/05 Outgoing In cell C2 enter =SUMPRODUCT(--(Sheet1!$A$1:$A$65535=$A2),--(Sheet1!$B$1:B$65535=$B2),--(Sheet1!$C$1:C$65535=C$1)) then copy across to cell D2, and down as far as you need. I also had to make a correction to a previous mistake - I had omitted the "$" in front of "65535" in my previous post - so be sure to include the "$" in your formula (otherwise you will get an error when you start copying). |
#6
|
|||
|
|||
try this formula
=SUMPRODUCT(--(Sheet1!$A$1:$A$65535=$A2),--(Sheet1!$B$1:B$65535=$B2),(Sheet1!$C$1:C$65535="Re viewed")+(Sheet1!$C$1:C$65535="OK")) "dj_george" wrote: Thanks, It works. Just one clarification. Can't we calculate Reviewed and OK records in the same column. "JMB" wrote: I would include headers on Sheet2. A B C D 1 DATE TASK REVIEWED OK 2 9/15/05 Incoming 3 9/15/05 Outgoing In cell C2 enter =SUMPRODUCT(--(Sheet1!$A$1:$A$65535=$A2),--(Sheet1!$B$1:B$65535=$B2),--(Sheet1!$C$1:C$65535=C$1)) then copy across to cell D2, and down as far as you need. I also had to make a correction to a previous mistake - I had omitted the "$" in front of "65535" in my previous post - so be sure to include the "$" in your formula (otherwise you will get an error when you start copying). |
#7
|
|||
|
|||
Thanks, You were of great help to me.
One last clarification. I feel that due to these functions the file size seems to be huge, bcoz i need to calculate data for a whole month.(records crossing 15000). Any suggestions. "JMB" wrote: try this formula =SUMPRODUCT(--(Sheet1!$A$1:$A$65535=$A2),--(Sheet1!$B$1:B$65535=$B2),(Sheet1!$C$1:C$65535="Re viewed")+(Sheet1!$C$1:C$65535="OK")) "dj_george" wrote: Thanks, It works. Just one clarification. Can't we calculate Reviewed and OK records in the same column. "JMB" wrote: I would include headers on Sheet2. A B C D 1 DATE TASK REVIEWED OK 2 9/15/05 Incoming 3 9/15/05 Outgoing In cell C2 enter =SUMPRODUCT(--(Sheet1!$A$1:$A$65535=$A2),--(Sheet1!$B$1:B$65535=$B2),--(Sheet1!$C$1:C$65535=C$1)) then copy across to cell D2, and down as far as you need. I also had to make a correction to a previous mistake - I had omitted the "$" in front of "65535" in my previous post - so be sure to include the "$" in your formula (otherwise you will get an error when you start copying). |
#8
|
|||
|
|||
No great ideas about that. You will need to look for a way to split your
data into separate workbooks. Possibly put the raw data in one workbook and your Sumproduct functions in another (they appear to be a summary of your data). Or if your issue is the workbook is slow, you could set the calculation to manual (Tools/Options/Calculation - set to manual). Then Excel would only calculate when you tell it to (F9 key or go back through the menu and you'll see a button to calculate). This would prevent Excel from calculating every time you enter something. "dj_george" wrote: Thanks, You were of great help to me. One last clarification. I feel that due to these functions the file size seems to be huge, bcoz i need to calculate data for a whole month.(records crossing 15000). Any suggestions. "JMB" wrote: try this formula =SUMPRODUCT(--(Sheet1!$A$1:$A$65535=$A2),--(Sheet1!$B$1:B$65535=$B2),(Sheet1!$C$1:C$65535="Re viewed")+(Sheet1!$C$1:C$65535="OK")) "dj_george" wrote: Thanks, It works. Just one clarification. Can't we calculate Reviewed and OK records in the same column. "JMB" wrote: I would include headers on Sheet2. A B C D 1 DATE TASK REVIEWED OK 2 9/15/05 Incoming 3 9/15/05 Outgoing In cell C2 enter =SUMPRODUCT(--(Sheet1!$A$1:$A$65535=$A2),--(Sheet1!$B$1:B$65535=$B2),--(Sheet1!$C$1:C$65535=C$1)) then copy across to cell D2, and down as far as you need. I also had to make a correction to a previous mistake - I had omitted the "$" in front of "65535" in my previous post - so be sure to include the "$" in your formula (otherwise you will get an error when you start copying). |
#9
|
|||
|
|||
Thanks, Ur suggestions were of great help to my team to save time.
"JMB" wrote: No great ideas about that. You will need to look for a way to split your data into separate workbooks. Possibly put the raw data in one workbook and your Sumproduct functions in another (they appear to be a summary of your data). Or if your issue is the workbook is slow, you could set the calculation to manual (Tools/Options/Calculation - set to manual). Then Excel would only calculate when you tell it to (F9 key or go back through the menu and you'll see a button to calculate). This would prevent Excel from calculating every time you enter something. "dj_george" wrote: Thanks, You were of great help to me. One last clarification. I feel that due to these functions the file size seems to be huge, bcoz i need to calculate data for a whole month.(records crossing 15000). Any suggestions. "JMB" wrote: try this formula =SUMPRODUCT(--(Sheet1!$A$1:$A$65535=$A2),--(Sheet1!$B$1:B$65535=$B2),(Sheet1!$C$1:C$65535="Re viewed")+(Sheet1!$C$1:C$65535="OK")) "dj_george" wrote: Thanks, It works. Just one clarification. Can't we calculate Reviewed and OK records in the same column. "JMB" wrote: I would include headers on Sheet2. A B C D 1 DATE TASK REVIEWED OK 2 9/15/05 Incoming 3 9/15/05 Outgoing In cell C2 enter =SUMPRODUCT(--(Sheet1!$A$1:$A$65535=$A2),--(Sheet1!$B$1:B$65535=$B2),--(Sheet1!$C$1:C$65535=C$1)) then copy across to cell D2, and down as far as you need. I also had to make a correction to a previous mistake - I had omitted the "$" in front of "65535" in my previous post - so be sure to include the "$" in your formula (otherwise you will get an error when you start copying). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing Excel Formulas without file paths updating | Excel Discussion (Misc queries) | |||
Simple formulas in existing Excel 2002 no longer working. | Excel Worksheet Functions | |||
Help, Urgent Excel Formulas are not calculating | Excel Discussion (Misc queries) | |||
I want Excel to allow cells with formulas and unrelated text | Excel Discussion (Misc queries) | |||
Problems with Excel formulas when 2002 upgraded to XP | Excel Worksheet Functions |