software

Good Morning
I have a worksheet that acts as a database. In the worksheet there are 17
columns of specific information, ie column A is 'date' column B is 'time'
etc. The number of rows is ongrowing (currently 3500).
What I am trying to do is count the number of times a letter 'Y' shows in
column N for each same date shown in column A or B, column A being standard
format 04 Jan 2006 and column B shown the date number - ie 38721.
I have tried this formula but can't seem to get it to work, I am NO expert
so must be something wrong with my effort.

=SUMPRODUCT(($B$5:$B$5000=quot;38720quot;) ($N$5:$N$5000=quot; Yquot;)).....Also is it possible to drag this formula (when correct) down to the next
row so that the date it is looking for moves to the next date , ie in thie
example above the search would be for '38721' and so on.

Many thanks
Hi Anthony

try
=SUMPRODUCT(--($B$5:$B$5000=(38719 ROW()-4)),--($N$5:$N$5000=quot;yquot;))

the ROW()-4
will give you the number of the row that the formula is in -4 rows (so if
you're in Z5 then this part of the formula would equate to 38719 5-4 = 38720
when you drag it down to Z6 it will equate to 38719 6-4 = 38721) so you'll
need to modify it depending on the rows your answers are in.

the formula above also assumes that column B is not formatted as quot;TEXTquot;.

--
Cheers
JulieDjulied_ng at hctsReMoVeThIs dot net dot auquot;Anthonyquot; wrote:

gt; Good Morning
gt; I have a worksheet that acts as a database. In the worksheet there are 17
gt; columns of specific information, ie column A is 'date' column B is 'time'
gt; etc. The number of rows is ongrowing (currently 3500).
gt; What I am trying to do is count the number of times a letter 'Y' shows in
gt; column N for each same date shown in column A or B, column A being standard
gt; format 04 Jan 2006 and column B shown the date number - ie 38721.
gt; I have tried this formula but can't seem to get it to work, I am NO expert
gt; so must be something wrong with my effort.
gt;
gt; =SUMPRODUCT(($B$5:$B$5000=quot;38720quot;) ($N$5:$N$5000=quot; Yquot;)).
gt;
gt;
gt; ...Also is it possible to drag this formula (when correct) down to the next
gt; row so that the date it is looking for moves to the next date , ie in thie
gt; example above the search would be for '38721' and so on.
gt;
gt; Many thanks
gt;
gt;

Hi Julie,
You have helped me before, so thanks and hope you can solve this for me here
but I'm not sure I follow you, also I have a further question,

So say I have placed this formula now in cell H2 of a new worksheet
=SUMPRODUCT(--(Log!$B$5:$B$65536=(38718 ROW()-4)),--(Log!$N$5:$N$65536=quot;Yquot;))

I want this formula to count the number of times that the letter 'y' is
shown in column N of the 'log' worksheet, when the number 38718 (or 01Jan) is
shown in column B of the 'log' worksheet.

As this number - 38718 represents a date (01Jan) when I drag the formula to
the next row H3 I want the same formula to check for the letter Y in column N
and 38719 (02Jan) in column B...does this make sense ??

added to this I would like another =SUMPRODUCT formula to count the number
of times when a 'time' is before 12:00 in the 'log' worksheet column C when
the number in column B is 38718 (01Jan) and so on.....
I guess this would be something like....--($N$5000=lt;quot;12:00quot;)) ??

Cheers Julie

quot;JulieDquot; wrote:

gt; Hi Anthony
gt;
gt; try
gt; =SUMPRODUCT(--($B$5:$B$5000=(38719 ROW()-4)),--($N$5:$N$5000=quot;yquot;))
gt;
gt; the ROW()-4
gt; will give you the number of the row that the formula is in -4 rows (so if
gt; you're in Z5 then this part of the formula would equate to 38719 5-4 = 38720
gt; when you drag it down to Z6 it will equate to 38719 6-4 = 38721) so you'll
gt; need to modify it depending on the rows your answers are in.
gt;
gt; the formula above also assumes that column B is not formatted as quot;TEXTquot;.
gt;
gt; --
gt; Cheers
gt; JulieD
gt;
gt;
gt; julied_ng at hctsReMoVeThIs dot net dot au
gt;
gt;
gt; quot;Anthonyquot; wrote:
gt;
gt; gt; Good Morning
gt; gt; I have a worksheet that acts as a database. In the worksheet there are 17
gt; gt; columns of specific information, ie column A is 'date' column B is 'time'
gt; gt; etc. The number of rows is ongrowing (currently 3500).
gt; gt; What I am trying to do is count the number of times a letter 'Y' shows in
gt; gt; column N for each same date shown in column A or B, column A being standard
gt; gt; format 04 Jan 2006 and column B shown the date number - ie 38721.
gt; gt; I have tried this formula but can't seem to get it to work, I am NO expert
gt; gt; so must be something wrong with my effort.
gt; gt;
gt; gt; =SUMPRODUCT(($B$5:$B$5000=quot;38720quot;) ($N$5:$N$5000=quot; Yquot;)).
gt; gt;
gt; gt;
gt; gt; ...Also is it possible to drag this formula (when correct) down to the next
gt; gt; row so that the date it is looking for moves to the next date , ie in thie
gt; gt; example above the search would be for '38721' and so on.
gt; gt;
gt; gt; Many thanks
gt; gt;
gt; gt;

Hi Anthony

(got to rush out the door) but a part answer to your question

gt; So say I have placed this formula now in cell H2 of a new worksheet
gt; =SUMPRODUCT(--(Log!$B$5:$B$65536=(38718 ROW()-4)),--(Log!$N$5:$N$65536=quot;Yquot;))

H2 would make the row number 2, so
38718 2-4
would equal 38716
not 38720 ... so remove the -4 bit in the formula and it should work when
you drag it down.

as to the other part of your question, i'll have to leave that to later (or
someone else) due to an apt.

but hope this helps ...
Cheers
JulieD
Excel MVP

julied_ng at hctsReMoVeThIs dot net dot auquot;Anthonyquot; wrote:

gt; Hi Julie,
gt; You have helped me before, so thanks and hope you can solve this for me here
gt; but I'm not sure I follow you, also I have a further question,
gt;
gt; So say I have placed this formula now in cell H2 of a new worksheet
gt; =SUMPRODUCT(--(Log!$B$5:$B$65536=(38718 ROW()-4)),--(Log!$N$5:$N$65536=quot;Yquot;))
gt;
gt; I want this formula to count the number of times that the letter 'y' is
gt; shown in column N of the 'log' worksheet, when the number 38718 (or 01Jan) is
gt; shown in column B of the 'log' worksheet.
gt;
gt; As this number - 38718 represents a date (01Jan) when I drag the formula to
gt; the next row H3 I want the same formula to check for the letter Y in column N
gt; and 38719 (02Jan) in column B...does this make sense ??
gt;
gt; added to this I would like another =SUMPRODUCT formula to count the number
gt; of times when a 'time' is before 12:00 in the 'log' worksheet column C when
gt; the number in column B is 38718 (01Jan) and so on.....
gt; I guess this would be something like....--($N$5000=lt;quot;12:00quot;)) ??
gt;
gt; Cheers Julie
gt;
gt; quot;JulieDquot; wrote:
gt;
gt; gt; Hi Anthony
gt; gt;
gt; gt; try
gt; gt; =SUMPRODUCT(--($B$5:$B$5000=(38719 ROW()-4)),--($N$5:$N$5000=quot;yquot;))
gt; gt;
gt; gt; the ROW()-4
gt; gt; will give you the number of the row that the formula is in -4 rows (so if
gt; gt; you're in Z5 then this part of the formula would equate to 38719 5-4 = 38720
gt; gt; when you drag it down to Z6 it will equate to 38719 6-4 = 38721) so you'll
gt; gt; need to modify it depending on the rows your answers are in.
gt; gt;
gt; gt; the formula above also assumes that column B is not formatted as quot;TEXTquot;.
gt; gt;
gt; gt; --
gt; gt; Cheers
gt; gt; JulieD
gt; gt;
gt; gt;
gt; gt; julied_ng at hctsReMoVeThIs dot net dot au
gt; gt;
gt; gt;
gt; gt; quot;Anthonyquot; wrote:
gt; gt;
gt; gt; gt; Good Morning
gt; gt; gt; I have a worksheet that acts as a database. In the worksheet there are 17
gt; gt; gt; columns of specific information, ie column A is 'date' column B is 'time'
gt; gt; gt; etc. The number of rows is ongrowing (currently 3500).
gt; gt; gt; What I am trying to do is count the number of times a letter 'Y' shows in
gt; gt; gt; column N for each same date shown in column A or B, column A being standard
gt; gt; gt; format 04 Jan 2006 and column B shown the date number - ie 38721.
gt; gt; gt; I have tried this formula but can't seem to get it to work, I am NO expert
gt; gt; gt; so must be something wrong with my effort.
gt; gt; gt;
gt; gt; gt; =SUMPRODUCT(($B$5:$B$5000=quot;38720quot;) ($N$5:$N$5000=quot; Yquot;)).
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; ...Also is it possible to drag this formula (when correct) down to the next
gt; gt; gt; row so that the date it is looking for moves to the next date , ie in thie
gt; gt; gt; example above the search would be for '38721' and so on.
gt; gt; gt;
gt; gt; gt; Many thanks
gt; gt; gt;
gt; gt; gt;


quot;Anthonyquot; gt; wrote in message
...
gt; Hi Julie,
gt; You have helped me before, so thanks and hope you can solve this for me
here
gt; but I'm not sure I follow you, also I have a further question,
gt;
gt; So say I have placed this formula now in cell H2 of a new worksheet
gt;
=SUMPRODUCT(--(Log!$B$5:$B$65536=(38718 ROW()-4)),--(Log!$N$5:$N$65536=quot;Yquot;))

I would use the date column, not the date4 number column (why even have a
date number column?), and store the day before the starting date in a cell
on this other worksheet, say A1

=SUMPRODUCT(--(Log!$A$5:$A$5000=$A$1 ROW(A1)),--(Log!$N$5:$N$5000=quot;Yquot;))

gt; added to this I would like another =SUMPRODUCT formula to count the number
gt; of times when a 'time' is before 12:00 in the 'log' worksheet column C
when
gt; the number in column B is 38718 (01Jan) and so on.....
gt; I guess this would be something like....--($N$5000=lt;quot;12:00quot;)) ??=SUMPRODUCT(--(Log!$A$5:$A$5000=$A$1 ROW(A1)),--(Log!$C$5:$C$5000lt;=--quot;12:00:
00quot;),--(Log!$N$5:$N$5000=quot;Yquot;))
Julkie/Bob
Thanks to you both for your help, with it, I got a formula that worked, so
cheersa guys !

quot;Anthonyquot; wrote:

gt; Good Morning
gt; I have a worksheet that acts as a database. In the worksheet there are 17
gt; columns of specific information, ie column A is 'date' column B is 'time'
gt; etc. The number of rows is ongrowing (currently 3500).
gt; What I am trying to do is count the number of times a letter 'Y' shows in
gt; column N for each same date shown in column A or B, column A being standard
gt; format 04 Jan 2006 and column B shown the date number - ie 38721.
gt; I have tried this formula but can't seem to get it to work, I am NO expert
gt; so must be something wrong with my effort.
gt;
gt; =SUMPRODUCT(($B$5:$B$5000=quot;38720quot;) ($N$5:$N$5000=quot; Yquot;)).
gt;
gt;
gt; ...Also is it possible to drag this formula (when correct) down to the next
gt; row so that the date it is looking for moves to the next date , ie in thie
gt; example above the search would be for '38721' and so on.
gt;
gt; Many thanks
gt;
gt;