If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SUMPRODUCT: Help to use this to find min date in range for criter
I am trying to find the minimum date in a range, which contains blanks.
subject to a criteria. As the range contains blanks, these are currently picked up as the min date and display 00-Jan-00. Is there a way of using sumproduct and excluding/ignoring blank cells? PO1 is my range for criteria and Created1 is my range on the dates I am using in the following formula =Sumproduct(MIN(--(PO1=A2)*Created1)) Gwynneth -- Gwynneth |
#2
|
|||
|
|||
SUMPRODUCT: Help to use this to find min date in range for criter
=MIN(IF(Date"",Date))
entered with ctrl + shift & enter will find the earliest date that is not blank -- Regards, Peo Sjoblom "Gwynneth" wrote in message news I am trying to find the minimum date in a range, which contains blanks. subject to a criteria. As the range contains blanks, these are currently picked up as the min date and display 00-Jan-00. Is there a way of using sumproduct and excluding/ignoring blank cells? PO1 is my range for criteria and Created1 is my range on the dates I am using in the following formula =Sumproduct(MIN(--(PO1=A2)*Created1)) Gwynneth -- Gwynneth |
#3
|
|||
|
|||
SUMPRODUCT: Help to use this to find min date in range for criter
This finds the minimum value in the A range subject to the corresponding B
value being "a". But if there are two cases you get twice the answer. =SUMPRODUCT(--(A1:A16=MIN(A1:A16)),--(B1:B16="a")) Can you please expand on your problem. You want to find the minimum value of PO1 with the proviso that the date in also in the range Created1? With named ranges we cannot see the whole picture. For example what is the size of each range? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Gwynneth" wrote in message news I am trying to find the minimum date in a range, which contains blanks. subject to a criteria. As the range contains blanks, these are currently picked up as the min date and display 00-Jan-00. Is there a way of using sumproduct and excluding/ignoring blank cells? PO1 is my range for criteria and Created1 is my range on the dates I am using in the following formula =Sumproduct(MIN(--(PO1=A2)*Created1)) Gwynneth -- Gwynneth |
#4
|
|||
|
|||
SUMPRODUCT: Help to use this to find min date in range for criter
See if this array formula works for you:
=MIN(IF(PO1=A2,IF(Created"",Created),"")) NOTE: Array functions are entered with CTRL-SHIFT-ENTER instead of just Enter. If done properly, the formula will be enclosed in { }. HTH, Elkar "Gwynneth" wrote: I am trying to find the minimum date in a range, which contains blanks. subject to a criteria. As the range contains blanks, these are currently picked up as the min date and display 00-Jan-00. Is there a way of using sumproduct and excluding/ignoring blank cells? PO1 is my range for criteria and Created1 is my range on the dates I am using in the following formula =Sumproduct(MIN(--(PO1=A2)*Created1)) Gwynneth -- Gwynneth |
#5
|
|||
|
|||
SUMPRODUCT: Help to use this to find min date in range for cr
Thanks you for responding Peo
If you meant for me to add this to the sumproduct formula, it failed. If you meant for me to use as is, it returns the min date without considering the criteria. What I am looking for is: 'What is the earliest date (ignor blanks) in this range (column B) which meets the specifed criteria (Column A, Row n). If there are no blanks I can use: sumproduct(MIN(--(PO1=A2)*Issued1)) and this returns the latest date, where PO1 is my criteria range and Issued1 are the dates. Is there something I can do to this formula to obtain the minimum date in the range? Thanks Gwynneth "Peo Sjoblom" wrote: =MIN(IF(Date"",Date)) entered with ctrl + shift & enter will find the earliest date that is not blank -- Regards, Peo Sjoblom "Gwynneth" wrote in message news I am trying to find the minimum date in a range, which contains blanks. subject to a criteria. As the range contains blanks, these are currently picked up as the min date and display 00-Jan-00. Is there a way of using sumproduct and excluding/ignoring blank cells? PO1 is my range for criteria and Created1 is my range on the dates I am using in the following formula =Sumproduct(MIN(--(PO1=A2)*Created1)) Gwynneth -- Gwynneth |
#6
|
|||
|
|||
SUMPRODUCT: Help to use this to find min date in range for cr
Hi Bernard and thank you for responding. I found a formula yesterday which
enabled me to find the latest/max date within a range and subject to a criteria. This formula was: =sumproduct(MAX(--(A:A=A1)*B:B)). So I thought I could use this to find the first date/min. However, because there are blanks agains some criteria these are picked up as the minimum values. I am therefore hoping there is something I can do the the sumproduct formula above that will skip the blanks and give the the first date against a specifed criteria with multiple dates. If this is not enough info. I will send you an example spreadsheet if that pos. Thanks again -- Gwynneth "Bernard Liengme" wrote: This finds the minimum value in the A range subject to the corresponding B value being "a". But if there are two cases you get twice the answer. =SUMPRODUCT(--(A1:A16=MIN(A1:A16)),--(B1:B16="a")) Can you please expand on your problem. You want to find the minimum value of PO1 with the proviso that the date in also in the range Created1? With named ranges we cannot see the whole picture. For example what is the size of each range? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Gwynneth" wrote in message news I am trying to find the minimum date in a range, which contains blanks. subject to a criteria. As the range contains blanks, these are currently picked up as the min date and display 00-Jan-00. Is there a way of using sumproduct and excluding/ignoring blank cells? PO1 is my range for criteria and Created1 is my range on the dates I am using in the following formula =Sumproduct(MIN(--(PO1=A2)*Created1)) Gwynneth -- Gwynneth |
#7
|
|||
|
|||
SUMPRODUCT: Help to use this to find min date in range for cr
Well that formula is not needed and it doesn't make any sense. You seem to
want 2 criteria, no blank dates since they would be dates over 100 years ago and a criteria in another range, =MIN(IF(Range1="x",IF(Date_Range"",Date_Range))) will return the earliest date where Range1 equals x, adapt to fit your requirements and this time enter the formula with ctrl + shift & enter and finally make sure the cell with this formula is formatted as date or you will get the serial number -- Regards, Peo Sjoblom "Gwynneth" wrote in message ... Hi Bernard and thank you for responding. I found a formula yesterday which enabled me to find the latest/max date within a range and subject to a criteria. This formula was: =sumproduct(MAX(--(A:A=A1)*B:B)). So I thought I could use this to find the first date/min. However, because there are blanks agains some criteria these are picked up as the minimum values. I am therefore hoping there is something I can do the the sumproduct formula above that will skip the blanks and give the the first date against a specifed criteria with multiple dates. If this is not enough info. I will send you an example spreadsheet if that pos. Thanks again -- Gwynneth "Bernard Liengme" wrote: This finds the minimum value in the A range subject to the corresponding B value being "a". But if there are two cases you get twice the answer. =SUMPRODUCT(--(A1:A16=MIN(A1:A16)),--(B1:B16="a")) Can you please expand on your problem. You want to find the minimum value of PO1 with the proviso that the date in also in the range Created1? With named ranges we cannot see the whole picture. For example what is the size of each range? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Gwynneth" wrote in message news I am trying to find the minimum date in a range, which contains blanks. subject to a criteria. As the range contains blanks, these are currently picked up as the min date and display 00-Jan-00. Is there a way of using sumproduct and excluding/ignoring blank cells? PO1 is my range for criteria and Created1 is my range on the dates I am using in the following formula =Sumproduct(MIN(--(PO1=A2)*Created1)) Gwynneth -- Gwynneth |
#8
|
|||
|
|||
SUMPRODUCT: Help to use this to find min date in range for cr
You are a star Poe. Your suggestion worked like a dream. Thank you so much
and have a great day -- Gwynneth "Peo Sjoblom" wrote: Well that formula is not needed and it doesn't make any sense. You seem to want 2 criteria, no blank dates since they would be dates over 100 years ago and a criteria in another range, =MIN(IF(Range1="x",IF(Date_Range"",Date_Range))) will return the earliest date where Range1 equals x, adapt to fit your requirements and this time enter the formula with ctrl + shift & enter and finally make sure the cell with this formula is formatted as date or you will get the serial number -- Regards, Peo Sjoblom "Gwynneth" wrote in message ... Hi Bernard and thank you for responding. I found a formula yesterday which enabled me to find the latest/max date within a range and subject to a criteria. This formula was: =sumproduct(MAX(--(A:A=A1)*B:B)). So I thought I could use this to find the first date/min. However, because there are blanks agains some criteria these are picked up as the minimum values. I am therefore hoping there is something I can do the the sumproduct formula above that will skip the blanks and give the the first date against a specifed criteria with multiple dates. If this is not enough info. I will send you an example spreadsheet if that pos. Thanks again -- Gwynneth "Bernard Liengme" wrote: This finds the minimum value in the A range subject to the corresponding B value being "a". But if there are two cases you get twice the answer. =SUMPRODUCT(--(A1:A16=MIN(A1:A16)),--(B1:B16="a")) Can you please expand on your problem. You want to find the minimum value of PO1 with the proviso that the date in also in the range Created1? With named ranges we cannot see the whole picture. For example what is the size of each range? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Gwynneth" wrote in message news I am trying to find the minimum date in a range, which contains blanks. subject to a criteria. As the range contains blanks, these are currently picked up as the min date and display 00-Jan-00. Is there a way of using sumproduct and excluding/ignoring blank cells? PO1 is my range for criteria and Created1 is my range on the dates I am using in the following formula =Sumproduct(MIN(--(PO1=A2)*Created1)) Gwynneth -- Gwynneth |
#9
|
|||
|
|||
SUMPRODUCT: Help to use this to find min date in range for cr
Hello Peo,
I'm working with the formula below, but can't figure it out. I've got a row of dates and values: 10/07 11/07 12/07 01/08 02/08 20 20 And would like to test the second row for the existence of data and return the Max date in one column and the Min dates in another column. Is that possible? Thank you. "Peo Sjoblom" wrote: Well that formula is not needed and it doesn't make any sense. You seem to want 2 criteria, no blank dates since they would be dates over 100 years ago and a criteria in another range, =MIN(IF(Range1="x",IF(Date_Range"",Date_Range))) will return the earliest date where Range1 equals x, adapt to fit your requirements and this time enter the formula with ctrl + shift & enter and finally make sure the cell with this formula is formatted as date or you will get the serial number -- Regards, Peo Sjoblom "Gwynneth" wrote in message ... Hi Bernard and thank you for responding. I found a formula yesterday which enabled me to find the latest/max date within a range and subject to a criteria. This formula was: =sumproduct(MAX(--(A:A=A1)*B:B)). So I thought I could use this to find the first date/min. However, because there are blanks agains some criteria these are picked up as the minimum values. I am therefore hoping there is something I can do the the sumproduct formula above that will skip the blanks and give the the first date against a specifed criteria with multiple dates. If this is not enough info. I will send you an example spreadsheet if that pos. Thanks again -- Gwynneth "Bernard Liengme" wrote: This finds the minimum value in the A range subject to the corresponding B value being "a". But if there are two cases you get twice the answer. =SUMPRODUCT(--(A1:A16=MIN(A1:A16)),--(B1:B16="a")) Can you please expand on your problem. You want to find the minimum value of PO1 with the proviso that the date in also in the range Created1? With named ranges we cannot see the whole picture. For example what is the size of each range? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Gwynneth" wrote in message news I am trying to find the minimum date in a range, which contains blanks. subject to a criteria. As the range contains blanks, these are currently picked up as the min date and display 00-Jan-00. Is there a way of using sumproduct and excluding/ignoring blank cells? PO1 is my range for criteria and Created1 is my range on the dates I am using in the following formula =Sumproduct(MIN(--(PO1=A2)*Created1)) Gwynneth -- Gwynneth |
#10
|
|||
|
|||
SUMPRODUCT: Help to use this to find min date in range for cr
For max
=INDEX(A1:E1,MATCH(MAX(A2:E2),A2:E2,0)) for min =INDEX(A1:E1,MATCH(MIN(A2:E2),A2:E2,0)) where I assume the dates are in A1:E1 and the values in A2:E2 -- Regards, Peo Sjoblom -- Regards, Peo Sjoblom "Blueglass" wrote in message ... Hello Peo, I'm working with the formula below, but can't figure it out. I've got a row of dates and values: 10/07 11/07 12/07 01/08 02/08 20 20 And would like to test the second row for the existence of data and return the Max date in one column and the Min dates in another column. Is that possible? Thank you. "Peo Sjoblom" wrote: Well that formula is not needed and it doesn't make any sense. You seem to want 2 criteria, no blank dates since they would be dates over 100 years ago and a criteria in another range, =MIN(IF(Range1="x",IF(Date_Range"",Date_Range))) will return the earliest date where Range1 equals x, adapt to fit your requirements and this time enter the formula with ctrl + shift & enter and finally make sure the cell with this formula is formatted as date or you will get the serial number -- Regards, Peo Sjoblom "Gwynneth" wrote in message ... Hi Bernard and thank you for responding. I found a formula yesterday which enabled me to find the latest/max date within a range and subject to a criteria. This formula was: =sumproduct(MAX(--(A:A=A1)*B:B)). So I thought I could use this to find the first date/min. However, because there are blanks agains some criteria these are picked up as the minimum values. I am therefore hoping there is something I can do the the sumproduct formula above that will skip the blanks and give the the first date against a specifed criteria with multiple dates. If this is not enough info. I will send you an example spreadsheet if that pos. Thanks again -- Gwynneth "Bernard Liengme" wrote: This finds the minimum value in the A range subject to the corresponding B value being "a". But if there are two cases you get twice the answer. =SUMPRODUCT(--(A1:A16=MIN(A1:A16)),--(B1:B16="a")) Can you please expand on your problem. You want to find the minimum value of PO1 with the proviso that the date in also in the range Created1? With named ranges we cannot see the whole picture. For example what is the size of each range? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Gwynneth" wrote in message news I am trying to find the minimum date in a range, which contains blanks. subject to a criteria. As the range contains blanks, these are currently picked up as the min date and display 00-Jan-00. Is there a way of using sumproduct and excluding/ignoring blank cells? PO1 is my range for criteria and Created1 is my range on the dates I am using in the following formula =Sumproduct(MIN(--(PO1=A2)*Created1)) Gwynneth -- Gwynneth |
|
Thread Tools | |
Display Modes | |
|
|