software

Hi,
I'm new here, and a bit of an Excel newbie and would like to learn more but
for starters I have a question about trying to retrieve and sort information
from data in a worksheet.
I work in a laboratory that tests patients for genetic abnormalities and
this information goes into a system that we can pull out the data as a text
file that will go into Excel as a new worksheet.
What I want to do is this: In one column is the type of test and in
another is the date the specimen was received. I want to find out the
number of tests done for a particular month. I know I can do autofilter and
do longwinded counts using that but is there an easier way to do this? Some
sort of function? Would setting up the worksheet that is linked to Microsoft
Access a better way to routinely pull up information??
I'll be asking the boss to send me on a course I think!!
Any help would be appreciated.
cheers
Jasen
If your test types are in Column A, and the corresponding dates are in
Column B,
AND, there is a date for every test, AND *no* date in Column B if Column A
is blank, then you could just count the dates in Column B that match a given
month and year.

Enter a date in C1, and this will count the dates in Column B that match
that month and year in Column B:

=SUMPRODUCT((TEXT(B2:B100,quot;m/yquot;)=TEXT(C1,quot;m/yquot;))*(B2:B100lt;gt;quot;quot;))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
quot;Jasenquot; gt; wrote in message
...
gt; Hi,
gt; I'm new here, and a bit of an Excel newbie and would like to learn more
but
gt; for starters I have a question about trying to retrieve and sort
information
gt; from data in a worksheet.
gt; I work in a laboratory that tests patients for genetic abnormalities and
gt; this information goes into a system that we can pull out the data as a
text
gt; file that will go into Excel as a new worksheet.
gt; What I want to do is this: In one column is the type of test and in
gt; another is the date the specimen was received. I want to find out the
gt; number of tests done for a particular month. I know I can do autofilter
and
gt; do longwinded counts using that but is there an easier way to do this?
Some
gt; sort of function? Would setting up the worksheet that is linked to
Microsoft
gt; Access a better way to routinely pull up information??
gt; I'll be asking the boss to send me on a course I think!!
gt; Any help would be appreciated.
gt; cheers
gt; Jasen
gt;
gt;
quot;Ragdyerquot; gt; wrote in message
...
gt; If your test types are in Column A, and the corresponding dates are in
gt; Column B,
gt; AND, there is a date for every test, AND *no* date in Column B if Column A
gt; is blank, then you could just count the dates in Column B that match a
gt; given
gt; month and year.
gt;
gt; Enter a date in C1, and this will count the dates in Column B that match
gt; that month and year in Column B:
gt;
gt; =SUMPRODUCT((TEXT(B2:B100,quot;m/yquot;)=TEXT(C1,quot;m/yquot;))*(B2:B100lt;gt;quot;quot;))Thanks RD, I assume this works to retrieve the numbers on specified dates
for all tests together, but I forgot to add that I want to retrieve the
numbers for quot;Test Aquot; or quot;Test Bquot; (for example) not both at the same
time.....sorry. How do you adjust the formula to pull that out? Many
thanks for this.
Jasen

gt;
gt; --
gt; HTH,
gt;
gt; RD
gt;
gt; ---------------------------------------------------------------------------
gt; Please keep all correspondence within the NewsGroup, so all may benefit !
gt; ---------------------------------------------------------------------------
gt; quot;Jasenquot; gt; wrote in message
gt; ...
gt;gt; Hi,
gt;gt; I'm new here, and a bit of an Excel newbie and would like to learn more
gt; but
gt;gt; for starters I have a question about trying to retrieve and sort
gt; information
gt;gt; from data in a worksheet.
gt;gt; I work in a laboratory that tests patients for genetic abnormalities and
gt;gt; this information goes into a system that we can pull out the data as a
gt; text
gt;gt; file that will go into Excel as a new worksheet.
gt;gt; What I want to do is this: In one column is the type of test and in
gt;gt; another is the date the specimen was received. I want to find out the
gt;gt; number of tests done for a particular month. I know I can do autofilter
gt; and
gt;gt; do longwinded counts using that but is there an easier way to do this?
gt; Some
gt;gt; sort of function? Would setting up the worksheet that is linked to
gt; Microsoft
gt;gt; Access a better way to routinely pull up information??
gt;gt; I'll be asking the boss to send me on a course I think!!
gt;gt; Any help would be appreciated.
gt;gt; cheers
gt;gt; Jasen
gt;gt;
gt;gt;
gt;
On Mon, 27 Feb 2006 09:33:40 1000, quot;Jasenquot; gt;
wrote:

gt;
gt;quot;Ragdyerquot; gt; wrote in message
...
gt;gt; If your test types are in Column A, and the corresponding dates are in
gt;gt; Column B,
gt;gt; AND, there is a date for every test, AND *no* date in Column B if Column A
gt;gt; is blank, then you could just count the dates in Column B that match a
gt;gt; given
gt;gt; month and year.
gt;gt;
gt;gt; Enter a date in C1, and this will count the dates in Column B that match
gt;gt; that month and year in Column B:
gt;gt;
gt;gt; =SUMPRODUCT((TEXT(B2:B100,quot;m/yquot;)=TEXT(C1,quot;m/yquot;))*(B2:B100lt;gt;quot;quot;))
gt;
gt;
gt;Thanks RD, I assume this works to retrieve the numbers on specified dates
gt;for all tests together, but I forgot to add that I want to retrieve the
gt;numbers for quot;Test Aquot; or quot;Test Bquot; (for example) not both at the same
gt;time.....sorry. How do you adjust the formula to pull that out? Many
gt;thanks for this.
gt;Jasen

This is untested but enter either Test A or Test B in say D1 and
extend the formula

=SUMPRODUCT((TEXT(B2:B100,quot;m/yquot;)=TEXT(C1,quot;m/yquot;))*(B2:B100lt;gt;quot;quot;)*(A1:A100=D1))

Hope this helps.

Rgds
Richard Buttrey
__

Enter the test type you're looking to count into C2, and try this:

=SUMPRODUCT((TEXT(B2:B100,quot;m/yquot;)=TEXT(C1,quot;m/yquot;))*(A2:A100=C2))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
quot;Jasenquot; gt; wrote in message
...
gt;
gt; quot;Ragdyerquot; gt; wrote in message
gt; ...
gt; gt; If your test types are in Column A, and the corresponding dates are in
gt; gt; Column B,
gt; gt; AND, there is a date for every test, AND *no* date in Column B if Column
A
gt; gt; is blank, then you could just count the dates in Column B that match a
gt; gt; given
gt; gt; month and year.
gt; gt;
gt; gt; Enter a date in C1, and this will count the dates in Column B that match
gt; gt; that month and year in Column B:
gt; gt;
gt; gt; =SUMPRODUCT((TEXT(B2:B100,quot;m/yquot;)=TEXT(C1,quot;m/yquot;))*(B2:B100lt;gt;quot;quot;))
gt;
gt;
gt; Thanks RD, I assume this works to retrieve the numbers on specified dates
gt; for all tests together, but I forgot to add that I want to retrieve the
gt; numbers for quot;Test Aquot; or quot;Test Bquot; (for example) not both at the same
gt; time.....sorry. How do you adjust the formula to pull that out? Many
gt; thanks for this.
gt; Jasen
gt;
gt;
gt;
gt;
gt; gt;
gt; gt; --
gt; gt; HTH,
gt; gt;
gt; gt; RD
gt; gt;
gt;
gt; --------------------------------------------------------------------------
-
gt; gt; Please keep all correspondence within the NewsGroup, so all may benefit
!
gt;
gt; --------------------------------------------------------------------------
-
gt; gt; quot;Jasenquot; gt; wrote in message
gt; gt; ...
gt; gt;gt; Hi,
gt; gt;gt; I'm new here, and a bit of an Excel newbie and would like to learn more
gt; gt; but
gt; gt;gt; for starters I have a question about trying to retrieve and sort
gt; gt; information
gt; gt;gt; from data in a worksheet.
gt; gt;gt; I work in a laboratory that tests patients for genetic abnormalities
and
gt; gt;gt; this information goes into a system that we can pull out the data as a
gt; gt; text
gt; gt;gt; file that will go into Excel as a new worksheet.
gt; gt;gt; What I want to do is this: In one column is the type of test and in
gt; gt;gt; another is the date the specimen was received. I want to find out the
gt; gt;gt; number of tests done for a particular month. I know I can do
autofilter
gt; gt; and
gt; gt;gt; do longwinded counts using that but is there an easier way to do this?
gt; gt; Some
gt; gt;gt; sort of function? Would setting up the worksheet that is linked to
gt; gt; Microsoft
gt; gt;gt; Access a better way to routinely pull up information??
gt; gt;gt; I'll be asking the boss to send me on a course I think!!
gt; gt;gt; Any help would be appreciated.
gt; gt;gt; cheers
gt; gt;gt; Jasen
gt; gt;gt;
gt; gt;gt;
gt; gt;
gt;
gt;
quot;Ragdyerquot; gt; wrote in message
...
gt; Enter the test type you're looking to count into C2, and try this:
gt;
gt; =SUMPRODUCT((TEXT(B2:B100,quot;m/yquot;)=TEXT(C1,quot;m/yquot;))*(A2:A100=C2))

Thank you, it works great! Spot on.
I will take note of the format of how you did this and apply it elsewhere.
Thanks again,
Jasen
gt;
gt; --
gt; HTH,
gt;
gt; RD
gt;
gt; ---------------------------------------------------------------------------
gt; Please keep all correspondence within the NewsGroup, so all may benefit !
gt; ---------------------------------------------------------------------------
gt; quot;Jasenquot; gt; wrote in message
gt; ...
gt;gt;
gt;gt; quot;Ragdyerquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; If your test types are in Column A, and the corresponding dates are in
gt;gt; gt; Column B,
gt;gt; gt; AND, there is a date for every test, AND *no* date in Column B if
gt;gt; gt; Column
gt; A
gt;gt; gt; is blank, then you could just count the dates in Column B that match a
gt;gt; gt; given
gt;gt; gt; month and year.
gt;gt; gt;
gt;gt; gt; Enter a date in C1, and this will count the dates in Column B that
gt;gt; gt; match
gt;gt; gt; that month and year in Column B:
gt;gt; gt;
gt;gt; gt; =SUMPRODUCT((TEXT(B2:B100,quot;m/yquot;)=TEXT(C1,quot;m/yquot;))*(B2:B100lt;gt;quot;quot;))
gt;gt;
gt;gt;
gt;gt; Thanks RD, I assume this works to retrieve the numbers on specified
gt;gt; dates
gt;gt; for all tests together, but I forgot to add that I want to retrieve the
gt;gt; numbers for quot;Test Aquot; or quot;Test Bquot; (for example) not both at the same
gt;gt; time.....sorry. How do you adjust the formula to pull that out? Many
gt;gt; thanks for this.
gt;gt; Jasen
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt; gt;
gt;gt; gt; --
gt;gt; gt; HTH,
gt;gt; gt;
gt;gt; gt; RD
gt;gt; gt;
gt;gt;
gt;gt; --------------------------------------------------------------------------
gt; -
gt;gt; gt; Please keep all correspondence within the NewsGroup, so all may benefit
gt; !
gt;gt;
gt;gt; --------------------------------------------------------------------------
gt; -
gt;gt; gt; quot;Jasenquot; gt; wrote in message
gt;gt; gt; ...
gt;gt; gt;gt; Hi,
gt;gt; gt;gt; I'm new here, and a bit of an Excel newbie and would like to learn
gt;gt; gt;gt; more
gt;gt; gt; but
gt;gt; gt;gt; for starters I have a question about trying to retrieve and sort
gt;gt; gt; information
gt;gt; gt;gt; from data in a worksheet.
gt;gt; gt;gt; I work in a laboratory that tests patients for genetic abnormalities
gt; and
gt;gt; gt;gt; this information goes into a system that we can pull out the data as a
gt;gt; gt; text
gt;gt; gt;gt; file that will go into Excel as a new worksheet.
gt;gt; gt;gt; What I want to do is this: In one column is the type of test and in
gt;gt; gt;gt; another is the date the specimen was received. I want to find out the
gt;gt; gt;gt; number of tests done for a particular month. I know I can do
gt; autofilter
gt;gt; gt; and
gt;gt; gt;gt; do longwinded counts using that but is there an easier way to do this?
gt;gt; gt; Some
gt;gt; gt;gt; sort of function? Would setting up the worksheet that is linked to
gt;gt; gt; Microsoft
gt;gt; gt;gt; Access a better way to routinely pull up information??
gt;gt; gt;gt; I'll be asking the boss to send me on a course I think!!
gt;gt; gt;gt; Any help would be appreciated.
gt;gt; gt;gt; cheers
gt;gt; gt;gt; Jasen
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;
gt;gt;
gt;gt;
gt;