Excel formulas

[Guest]

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.

 

[Guest]

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?

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

Thanks, It works. Just one clarification. Can't we calculate Reviewed and OK
records in the same column.

 

[Guest]

try this formula

=SUMPRODUCT(--(Sheet1!$A$1:$A$65535=$A2),--(Sheet1!$B$1:B$65535=$B2),(Sheet1!$C$1:C$65535="Reviewed")+(Sheet1!$C$1:C$65535="OK"))

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

Thanks, Ur suggestions were of great help to my team to save time.