Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=SUMPRODUCT formula help
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=SUMPRODUCT formula help
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=SUMPRODUCT formula help
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 "Anthony" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=SUMPRODUCT formula help
"Anthony" wrote in message ... 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")) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=SUMPRODUCT formula help
Julkie/Bob
Thanks to you both for your help, with it, I got a formula that worked, so cheersa guys ! "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Hide formula | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions |