I have an existing spreadsheet that I wish to use to make an audit sheet with
in the same workbook. However, I have the items in numerical order rather
than descriptive order. In other words, the column name is for produce but
the each item number has a different type of apple or orange. I cannot
reorganize the groups to make formatting easier for the new sheet. I need the
types seperated for received date and spoil date. What is a function I could
use for the new worksheet to keep the date counts current for each type of
produce in column A? The best one I found was a DCOUNTA function but I need
specific data.

Example: Column A Column B Column C
Apples 02/03/09 02/05/09
Oranges 01/02/09 01/07/09
Bananas 02/03/09 02/07/09
Apples 02/02/09 02/04/09

What is a formula or function I could use to get all the info for the apples
on a new worksheet that would stay updated? (All dates equal to the value of
1)

--
Dorydoodle

If I understand you correctly you are looking for the total number of days
for each item

'Count of dates excluding the starting date
=SUMPRODUCT((A1:A10="apples")*(C1:C10-B1:B10))

'Count of dates including the starting date
=SUMPRODUCT((A1:A10="apples")*(C1:C10-B1:B10+1))

If this post helps click Yes
---------------
Jacob Skaria


"Dorydoodle" wrote:

I have an existing spreadsheet that I wish to use to make an audit sheet with
in the same workbook. However, I have the items in numerical order rather
than descriptive order. In other words, the column name is for produce but
the each item number has a different type of apple or orange. I cannot
reorganize the groups to make formatting easier for the new sheet. I need the
types seperated for received date and spoil date. What is a function I could
use for the new worksheet to keep the date counts current for each type of
produce in column A? The best one I found was a DCOUNTA function but I need
specific data.

Example: Column A Column B Column C
Apples 02/03/09 02/05/09
Oranges 01/02/09 01/07/09
Bananas 02/03/09 02/07/09
Apples 02/02/09 02/04/09

What is a formula or function I could use to get all the info for the apples
on a new worksheet that would stay updated? (All dates equal to the value of
1)

--
Dorydoodle

You have the idea except I need the formula to transfer the information onto
a new worksheet. SUMPRODUCT would work if I were using it for that sheet. How
would I do that for a new worksheet?
--
Dorydoodle


"Jacob Skaria" wrote:

If I understand you correctly you are looking for the total number of days
for each item

'Count of dates excluding the starting date
=SUMPRODUCT((A1:A10="apples")*(C1:C10-B1:B10))

'Count of dates including the starting date
=SUMPRODUCT((A1:A10="apples")*(C1:C10-B1:B10+1))

If this post helps click Yes
---------------
Jacob Skaria


"Dorydoodle" wrote:

I have an existing spreadsheet that I wish to use to make an audit sheet with
in the same workbook. However, I have the items in numerical order rather
than descriptive order. In other words, the column name is for produce but
the each item number has a different type of apple or orange. I cannot
reorganize the groups to make formatting easier for the new sheet. I need the
types seperated for received date and spoil date. What is a function I could
use for the new worksheet to keep the date counts current for each type of
produce in column A? The best one I found was a DCOUNTA function but I need
specific data.

Example: Column A Column B Column C
Apples 02/03/09 02/05/09
Oranges 01/02/09 01/07/09
Bananas 02/03/09 02/07/09
Apples 02/02/09 02/04/09

What is a formula or function I could use to get all the info for the apples
on a new worksheet that would stay updated? (All dates equal to the value of
1)

--
Dorydoodle

Another thing I just realized is that you are asking if I am looking for the
number of days. That is not what I am trying to do. Each date is equal to one
so the sum would 1 for each item in the list. In this case I have two dates
for apples so my total is 2. I need the Counta function for that since I want
it to count but I need the info sorted too. It is a little tricky. The
example I gave is how my spreedsheet is set up.
--
Dorydoodle


"Jacob Skaria" wrote:

If I understand you correctly you are looking for the total number of days
for each item

'Count of dates excluding the starting date
=SUMPRODUCT((A1:A10="apples")*(C1:C10-B1:B10))

'Count of dates including the starting date
=SUMPRODUCT((A1:A10="apples")*(C1:C10-B1:B10+1))

If this post helps click Yes
---------------
Jacob Skaria


"Dorydoodle" wrote:

I have an existing spreadsheet that I wish to use to make an audit sheet with
in the same workbook. However, I have the items in numerical order rather
than descriptive order. In other words, the column name is for produce but
the each item number has a different type of apple or orange. I cannot
reorganize the groups to make formatting easier for the new sheet. I need the
types seperated for received date and spoil date. What is a function I could
use for the new worksheet to keep the date counts current for each type of
produce in column A? The best one I found was a DCOUNTA function but I need
specific data.

Example: Column A Column B Column C
Apples 02/03/09 02/05/09
Oranges 01/02/09 01/07/09
Bananas 02/03/09 02/07/09
Apples 02/02/09 02/04/09

What is a formula or function I could use to get all the info for the apples
on a new worksheet that would stay updated? (All dates equal to the value of
1)

--
Dorydoodle

Hallo Dorydoodle,

it is a bit difficult to understand your exact objective. If possible give
us an example of what your audit sheet should look like.

If you just want to count the number of occurrences for received and spoiled
dates the easiest way is probably to create a pivot table with the produce as
rowfields and received as well as spoiled date as data fields.
--
Regards

Joachim


"Dorydoodle" wrote:

I have an existing spreadsheet that I wish to use to make an audit sheet with
in the same workbook. However, I have the items in numerical order rather
than descriptive order. In other words, the column name is for produce but
the each item number has a different type of apple or orange. I cannot
reorganize the groups to make formatting easier for the new sheet. I need the
types seperated for received date and spoil date. What is a function I could
use for the new worksheet to keep the date counts current for each type of
produce in column A? The best one I found was a DCOUNTA function but I need
specific data.

Example: Column A Column B Column C
Apples 02/03/09 02/05/09
Oranges 01/02/09 01/07/09
Bananas 02/03/09 02/07/09
Apples 02/02/09 02/04/09

What is a formula or function I could use to get all the info for the apples
on a new worksheet that would stay updated? (All dates equal to the value of
1)

--
Dorydoodle

I tried to use a pivot table but the sheet is protected somehow. I think it
was developed on a different version of excel. I even had a friend who uses
them regularly help me and it would not allow her to do it either. I have to
keep the info on the original spreedsheet so that when it is updated, the new
one will update as well so that option was an unfortunate flop in my case.
Thank you for the suggestion though. .
--
Dorydoodle


"Joachim" wrote:

Hallo Dorydoodle,

it is a bit difficult to understand your exact objective. If possible give
us an example of what your audit sheet should look like.

If you just want to count the number of occurrences for received and spoiled
dates the easiest way is probably to create a pivot table with the produce as
rowfields and received as well as spoiled date as data fields.
--
Regards

Joachim


"Dorydoodle" wrote:

I have an existing spreadsheet that I wish to use to make an audit sheet with
in the same workbook. However, I have the items in numerical order rather
than descriptive order. In other words, the column name is for produce but
the each item number has a different type of apple or orange. I cannot
reorganize the groups to make formatting easier for the new sheet. I need the
types seperated for received date and spoil date. What is a function I could
use for the new worksheet to keep the date counts current for each type of
produce in column A? The best one I found was a DCOUNTA function but I need
specific data.

Example: Column A Column B Column C
Apples 02/03/09 02/05/09
Oranges 01/02/09 01/07/09
Bananas 02/03/09 02/07/09
Apples 02/02/09 02/04/09

What is a formula or function I could use to get all the info for the apples
on a new worksheet that would stay updated? (All dates equal to the value of
1)

--
Dorydoodle