View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Anthony
 
Posts: n/a
Default =SUMPRODUCT formula help

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

"JulieD" wrote:

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


"Anthony" wrote:

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