Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sumproduct for latest date
I have literally typed in
=sumproduct(--(B2:B366=name),--(E2:E366<""),E2:E366) but I am getting #NAME? error. I want cells in column D to read the client name in column B and go to column E and find the latest finish date for that person. Where am I going wrong with the above formula? Clients name can appear on up to 5 rows scattered intermittently down the spreadsheet but only one entry will have the finish date for that client and the rest will have defaulted to 0 due to post linking from another workbook. Can anyone please help. I have just this problem to go. Thanks Sue. B D E Client Names where latest finish date to be inserted Finish dates |
#2
|
|||
|
|||
Hi!
=sumproduct(--(B2:B366="name"),E2:E366) But use this instead: =SUMIF(B2:B366,"Name",E2:E366) Format cell as DATE Biff "Sue" wrote in message ... I have literally typed in =sumproduct(--(B2:B366=name),--(E2:E366<""),E2:E366) but I am getting #NAME? error. I want cells in column D to read the client name in column B and go to column E and find the latest finish date for that person. Where am I going wrong with the above formula? Clients name can appear on up to 5 rows scattered intermittently down the spreadsheet but only one entry will have the finish date for that client and the rest will have defaulted to 0 due to post linking from another workbook. Can anyone please help. I have just this problem to go. Thanks Sue. B D E Client Names where latest finish date to be inserted Finish dates |
#3
|
|||
|
|||
Hi Biff,
Tried both suggestions but they both came back with 0/01/00 and I know the finish date for client B2 in column E is 25/03/05. Column D is already formated Date. Can you help further? Thanks Sue "Biff" wrote: Hi! =sumproduct(--(B2:B366="name"),E2:E366) But use this instead: =SUMIF(B2:B366,"Name",E2:E366) Format cell as DATE Biff "Sue" wrote in message ... I have literally typed in =sumproduct(--(B2:B366=name),--(E2:E366<""),E2:E366) but I am getting #NAME? error. I want cells in column D to read the client name in column B and go to column E and find the latest finish date for that person. Where am I going wrong with the above formula? Clients name can appear on up to 5 rows scattered intermittently down the spreadsheet but only one entry will have the finish date for that client and the rest will have defaulted to 0 due to post linking from another workbook. Can anyone please help. I have just this problem to go. Thanks Sue. B D E Client Names where latest finish date to be inserted Finish dates |
#4
|
|||
|
|||
Hi!
0/01/00 is a zero formatted as a date. So that means the result of the formula was 0. Column D is already formated Date. I thought the dates were in column E? Anyhow, are you certain they're real dates? =ISNUMBER(E2) Should return TRUE for real dates. Are all the client names the same? For example: B2 = Jones B50 = <spaceJones B55 = Jones<space You could try this: =COUNTIF(B2:B366,"Jones") Do you get the correct result? =SUMIF(B2:B366,"Name",E2:E366) This is a very basic formula and there's not too much that can cause a problem. Either the dates aren't really dates and are just TEXT strings or the client name entries don't all match. Biff "Sue" wrote in message ... Hi Biff, Tried both suggestions but they both came back with 0/01/00 and I know the finish date for client B2 in column E is 25/03/05. Column D is already formated Date. Can you help further? Thanks Sue "Biff" wrote: Hi! =sumproduct(--(B2:B366="name"),E2:E366) But use this instead: =SUMIF(B2:B366,"Name",E2:E366) Format cell as DATE Biff "Sue" wrote in message ... I have literally typed in =sumproduct(--(B2:B366=name),--(E2:E366<""),E2:E366) but I am getting #NAME? error. I want cells in column D to read the client name in column B and go to column E and find the latest finish date for that person. Where am I going wrong with the above formula? Clients name can appear on up to 5 rows scattered intermittently down the spreadsheet but only one entry will have the finish date for that client and the rest will have defaulted to 0 due to post linking from another workbook. Can anyone please help. I have just this problem to go. Thanks Sue. B D E Client Names where latest finish date to be inserted Finish dates |
#5
|
|||
|
|||
Hi Biff,
Sorry I thought you were refering to the receiving column having to be Date formatted. But anyway both columns D and E are Date formatted. Did the =ISNUMBER(E2) test and got True. Did the =COUNTIF(B2:B366,"Bob Jones") and got 4 entries for that name which was correct. Did a test on other names and got 3 and 5 which was also correct. The names are copied and pasted all the way through the other workbook and therefore should be identical. Did your =SUMIF(B2:B366,"Name",E2:E366) and got 0/01/00. I think it just uses the first date it comes across for that person which of course will be a 0 or 0/01/00. I want it to look for a date greater than 0/01/00. Due to the post linking, where there is no date from the other workbook, it means column E defaults to 0 and with the column formated to Date, it becomes 0/01/00. Could this be the reason and if so how do I alter the formula to look for a date greater than 0/01/00. Thanks Sue "Biff" wrote: Hi! 0/01/00 is a zero formatted as a date. So that means the result of the formula was 0. Column D is already formated Date. I thought the dates were in column E? Anyhow, are you certain they're real dates? =ISNUMBER(E2) Should return TRUE for real dates. Are all the client names the same? For example: B2 = Jones B50 = <spaceJones B55 = Jones<space You could try this: =COUNTIF(B2:B366,"Jones") Do you get the correct result? =SUMIF(B2:B366,"Name",E2:E366) This is a very basic formula and there's not too much that can cause a problem. Either the dates aren't really dates and are just TEXT strings or the client name entries don't all match. Biff "Sue" wrote in message ... Hi Biff, Tried both suggestions but they both came back with 0/01/00 and I know the finish date for client B2 in column E is 25/03/05. Column D is already formated Date. Can you help further? Thanks Sue "Biff" wrote: Hi! =sumproduct(--(B2:B366="name"),E2:E366) But use this instead: =SUMIF(B2:B366,"Name",E2:E366) Format cell as DATE Biff "Sue" wrote in message ... I have literally typed in =sumproduct(--(B2:B366=name),--(E2:E366<""),E2:E366) but I am getting #NAME? error. I want cells in column D to read the client name in column B and go to column E and find the latest finish date for that person. Where am I going wrong with the above formula? Clients name can appear on up to 5 rows scattered intermittently down the spreadsheet but only one entry will have the finish date for that client and the rest will have defaulted to 0 due to post linking from another workbook. Can anyone please help. I have just this problem to go. Thanks Sue. B D E Client Names where latest finish date to be inserted Finish dates |
#6
|
|||
|
|||
Try
=MAX(IF(B2:B366="Bob Jones",E2:E366)) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH Bob Phillips "Sue" wrote in message ... Hi Biff, Sorry I thought you were refering to the receiving column having to be Date formatted. But anyway both columns D and E are Date formatted. Did the =ISNUMBER(E2) test and got True. Did the =COUNTIF(B2:B366,"Bob Jones") and got 4 entries for that name which was correct. Did a test on other names and got 3 and 5 which was also correct. The names are copied and pasted all the way through the other workbook and therefore should be identical. Did your =SUMIF(B2:B366,"Name",E2:E366) and got 0/01/00. I think it just uses the first date it comes across for that person which of course will be a 0 or 0/01/00. I want it to look for a date greater than 0/01/00. Due to the post linking, where there is no date from the other workbook, it means column E defaults to 0 and with the column formated to Date, it becomes 0/01/00. Could this be the reason and if so how do I alter the formula to look for a date greater than 0/01/00. Thanks Sue "Biff" wrote: Hi! 0/01/00 is a zero formatted as a date. So that means the result of the formula was 0. Column D is already formated Date. I thought the dates were in column E? Anyhow, are you certain they're real dates? =ISNUMBER(E2) Should return TRUE for real dates. Are all the client names the same? For example: B2 = Jones B50 = <spaceJones B55 = Jones<space You could try this: =COUNTIF(B2:B366,"Jones") Do you get the correct result? =SUMIF(B2:B366,"Name",E2:E366) This is a very basic formula and there's not too much that can cause a problem. Either the dates aren't really dates and are just TEXT strings or the client name entries don't all match. Biff "Sue" wrote in message ... Hi Biff, Tried both suggestions but they both came back with 0/01/00 and I know the finish date for client B2 in column E is 25/03/05. Column D is already formated Date. Can you help further? Thanks Sue "Biff" wrote: Hi! =sumproduct(--(B2:B366="name"),E2:E366) But use this instead: =SUMIF(B2:B366,"Name",E2:E366) Format cell as DATE Biff "Sue" wrote in message ... I have literally typed in =sumproduct(--(B2:B366=name),--(E2:E366<""),E2:E366) but I am getting #NAME? error. I want cells in column D to read the client name in column B and go to column E and find the latest finish date for that person. Where am I going wrong with the above formula? Clients name can appear on up to 5 rows scattered intermittently down the spreadsheet but only one entry will have the finish date for that client and the rest will have defaulted to 0 due to post linking from another workbook. Can anyone please help. I have just this problem to go. Thanks Sue. B D E Client Names where latest finish date to be inserted Finish dates |
#7
|
|||
|
|||
Hi Bob,
You have cracked it. It works beautifully. I substituted the name for the cell address as I won't know who is in that name cell and it works great. Thanks so very much. I am too young to be going grey yet but it was starting to happen. Thanks again Sue "Bob Phillips" wrote: Try =MAX(IF(B2:B366="Bob Jones",E2:E366)) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH Bob Phillips "Sue" wrote in message ... Hi Biff, Sorry I thought you were refering to the receiving column having to be Date formatted. But anyway both columns D and E are Date formatted. Did the =ISNUMBER(E2) test and got True. Did the =COUNTIF(B2:B366,"Bob Jones") and got 4 entries for that name which was correct. Did a test on other names and got 3 and 5 which was also correct. The names are copied and pasted all the way through the other workbook and therefore should be identical. Did your =SUMIF(B2:B366,"Name",E2:E366) and got 0/01/00. I think it just uses the first date it comes across for that person which of course will be a 0 or 0/01/00. I want it to look for a date greater than 0/01/00. Due to the post linking, where there is no date from the other workbook, it means column E defaults to 0 and with the column formated to Date, it becomes 0/01/00. Could this be the reason and if so how do I alter the formula to look for a date greater than 0/01/00. Thanks Sue "Biff" wrote: Hi! 0/01/00 is a zero formatted as a date. So that means the result of the formula was 0. Column D is already formated Date. I thought the dates were in column E? Anyhow, are you certain they're real dates? =ISNUMBER(E2) Should return TRUE for real dates. Are all the client names the same? For example: B2 = Jones B50 = <spaceJones B55 = Jones<space You could try this: =COUNTIF(B2:B366,"Jones") Do you get the correct result? =SUMIF(B2:B366,"Name",E2:E366) This is a very basic formula and there's not too much that can cause a problem. Either the dates aren't really dates and are just TEXT strings or the client name entries don't all match. Biff "Sue" wrote in message ... Hi Biff, Tried both suggestions but they both came back with 0/01/00 and I know the finish date for client B2 in column E is 25/03/05. Column D is already formated Date. Can you help further? Thanks Sue "Biff" wrote: Hi! =sumproduct(--(B2:B366="name"),E2:E366) But use this instead: =SUMIF(B2:B366,"Name",E2:E366) Format cell as DATE Biff "Sue" wrote in message ... I have literally typed in =sumproduct(--(B2:B366=name),--(E2:E366<""),E2:E366) but I am getting #NAME? error. I want cells in column D to read the client name in column B and go to column E and find the latest finish date for that person. Where am I going wrong with the above formula? Clients name can appear on up to 5 rows scattered intermittently down the spreadsheet but only one entry will have the finish date for that client and the rest will have defaulted to 0 due to post linking from another workbook. Can anyone please help. I have just this problem to go. Thanks Sue. B D E Client Names where latest finish date to be inserted Finish dates |
#8
|
|||
|
|||
Hi Biff,
Thank you for your input and assitance. I really appreciate it. Doing a great job! Problem now solved. Sue "Biff" wrote: Hi! 0/01/00 is a zero formatted as a date. So that means the result of the formula was 0. Column D is already formated Date. I thought the dates were in column E? Anyhow, are you certain they're real dates? =ISNUMBER(E2) Should return TRUE for real dates. Are all the client names the same? For example: B2 = Jones B50 = <spaceJones B55 = Jones<space You could try this: =COUNTIF(B2:B366,"Jones") Do you get the correct result? =SUMIF(B2:B366,"Name",E2:E366) This is a very basic formula and there's not too much that can cause a problem. Either the dates aren't really dates and are just TEXT strings or the client name entries don't all match. Biff "Sue" wrote in message ... Hi Biff, Tried both suggestions but they both came back with 0/01/00 and I know the finish date for client B2 in column E is 25/03/05. Column D is already formated Date. Can you help further? Thanks Sue "Biff" wrote: Hi! =sumproduct(--(B2:B366="name"),E2:E366) But use this instead: =SUMIF(B2:B366,"Name",E2:E366) Format cell as DATE Biff "Sue" wrote in message ... I have literally typed in =sumproduct(--(B2:B366=name),--(E2:E366<""),E2:E366) but I am getting #NAME? error. I want cells in column D to read the client name in column B and go to column E and find the latest finish date for that person. Where am I going wrong with the above formula? Clients name can appear on up to 5 rows scattered intermittently down the spreadsheet but only one entry will have the finish date for that client and the rest will have defaulted to 0 due to post linking from another workbook. Can anyone please help. I have just this problem to go. Thanks Sue. B D E Client Names where latest finish date to be inserted Finish dates |
#9
|
|||
|
|||
Glad to help Sue. I didn't use the name as others had used "Name" and names
don't have quotes. Best to be cautious thought I. Bob "Sue" wrote in message ... Hi Bob, You have cracked it. It works beautifully. I substituted the name for the cell address as I won't know who is in that name cell and it works great. Thanks so very much. I am too young to be going grey yet but it was starting to happen. Thanks again Sue "Bob Phillips" wrote: Try =MAX(IF(B2:B366="Bob Jones",E2:E366)) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH Bob Phillips "Sue" wrote in message ... Hi Biff, Sorry I thought you were refering to the receiving column having to be Date formatted. But anyway both columns D and E are Date formatted. Did the =ISNUMBER(E2) test and got True. Did the =COUNTIF(B2:B366,"Bob Jones") and got 4 entries for that name which was correct. Did a test on other names and got 3 and 5 which was also correct. The names are copied and pasted all the way through the other workbook and therefore should be identical. Did your =SUMIF(B2:B366,"Name",E2:E366) and got 0/01/00. I think it just uses the first date it comes across for that person which of course will be a 0 or 0/01/00. I want it to look for a date greater than 0/01/00. Due to the post linking, where there is no date from the other workbook, it means column E defaults to 0 and with the column formated to Date, it becomes 0/01/00. Could this be the reason and if so how do I alter the formula to look for a date greater than 0/01/00. Thanks Sue "Biff" wrote: Hi! 0/01/00 is a zero formatted as a date. So that means the result of the formula was 0. Column D is already formated Date. I thought the dates were in column E? Anyhow, are you certain they're real dates? =ISNUMBER(E2) Should return TRUE for real dates. Are all the client names the same? For example: B2 = Jones B50 = <spaceJones B55 = Jones<space You could try this: =COUNTIF(B2:B366,"Jones") Do you get the correct result? =SUMIF(B2:B366,"Name",E2:E366) This is a very basic formula and there's not too much that can cause a problem. Either the dates aren't really dates and are just TEXT strings or the client name entries don't all match. Biff "Sue" wrote in message ... Hi Biff, Tried both suggestions but they both came back with 0/01/00 and I know the finish date for client B2 in column E is 25/03/05. Column D is already formated Date. Can you help further? Thanks Sue "Biff" wrote: Hi! =sumproduct(--(B2:B366="name"),E2:E366) But use this instead: =SUMIF(B2:B366,"Name",E2:E366) Format cell as DATE Biff "Sue" wrote in message ... I have literally typed in =sumproduct(--(B2:B366=name),--(E2:E366<""),E2:E366) but I am getting #NAME? error. I want cells in column D to read the client name in column B and go to column E and find the latest finish date for that person. Where am I going wrong with the above formula? Clients name can appear on up to 5 rows scattered intermittently down the spreadsheet but only one entry will have the finish date for that client and the rest will have defaulted to 0 due to post linking from another workbook. Can anyone please help. I have just this problem to go. Thanks Sue. B D E Client Names where latest finish date to be inserted Finish dates |
#10
|
|||
|
|||
Hi!
Glad you got it worked out! I don't know why the SUMIF didn't work, it should have! I too, was going to suggest MAX IF as a last resort but it's a bit of "overkill". But hey, if it's working....... Biff "Sue" wrote in message ... Hi Biff, Thank you for your input and assitance. I really appreciate it. Doing a great job! Problem now solved. Sue "Biff" wrote: Hi! 0/01/00 is a zero formatted as a date. So that means the result of the formula was 0. Column D is already formated Date. I thought the dates were in column E? Anyhow, are you certain they're real dates? =ISNUMBER(E2) Should return TRUE for real dates. Are all the client names the same? For example: B2 = Jones B50 = <spaceJones B55 = Jones<space You could try this: =COUNTIF(B2:B366,"Jones") Do you get the correct result? =SUMIF(B2:B366,"Name",E2:E366) This is a very basic formula and there's not too much that can cause a problem. Either the dates aren't really dates and are just TEXT strings or the client name entries don't all match. Biff "Sue" wrote in message ... Hi Biff, Tried both suggestions but they both came back with 0/01/00 and I know the finish date for client B2 in column E is 25/03/05. Column D is already formated Date. Can you help further? Thanks Sue "Biff" wrote: Hi! =sumproduct(--(B2:B366="name"),E2:E366) But use this instead: =SUMIF(B2:B366,"Name",E2:E366) Format cell as DATE Biff "Sue" wrote in message ... I have literally typed in =sumproduct(--(B2:B366=name),--(E2:E366<""),E2:E366) but I am getting #NAME? error. I want cells in column D to read the client name in column B and go to column E and find the latest finish date for that person. Where am I going wrong with the above formula? Clients name can appear on up to 5 rows scattered intermittently down the spreadsheet but only one entry will have the finish date for that client and the rest will have defaulted to 0 due to post linking from another workbook. Can anyone please help. I have just this problem to go. Thanks Sue. B D E Client Names where latest finish date to be inserted Finish dates |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
search for latest date | Excel Worksheet Functions | |||
Sumproduct with Date column | Excel Worksheet Functions | |||
Using sumproduct to count number by date | Excel Worksheet Functions | |||
SUMPRODUCT with date range question | Excel Discussion (Misc queries) |