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.

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.

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.

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).

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).

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).

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).

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).

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).