=SUMPRODUCT formula help

[Guest]

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="38720")+($N$5:$N$5000="Y")).


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

 

[Guest]

Hi Anthony

try
=SUMPRODUCT(--($B$5:$B$5000=(38719+ROW()-4)),--($N$5:$N$5000="y"))

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 "TEXT".

--
Cheers
JulieD


julied_ng at hctsReMoVeThIs dot net dot au

 

[Guest]

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="Y"))

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=<"12:00")) ??

Cheers Julie

 

[Guest]

Hi Anthony

(got to rush out the door) but a part answer to your 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="Y"))

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 au

 

[Bob Phillips]

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="Y"))

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="Y"))

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=<"12:00")) ??

=SUMPRODUCT(--(Log!$A$5:$A$5000=$A$1+ROW(A1)),--(Log!$C$5:$C$5000<=--"12:00:
00"),--(Log!$N$5:$N$5000="Y"))

 

[Guest]

Julkie/Bob
Thanks to you both for your help, with it, I got a formula that worked, so
cheersa guys !