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

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

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

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

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

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

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

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

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

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

Hello Peo,
Thanks for the information. I tried the formulas below, but couldn't get
it work in all cases. Had a problem with this one:

Dates: 11/08 12/08 1/09 2/09 3/09 4/09 5/09 06/09
Values: 7 9 5 6 3 4 10 11
Returns Min = 11/08 and Max = 3/09



"Peo Sjoblom" wrote:

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

Works fine for me when I test it using your example, you might have text is
some of the values and your dates are very ambiguous

Excel can only accept dates that includes year, month and day if you omit
one it will put whatever it considers a date so if you type in 05/09 today
Excel will guess that you are trying to type 05/09/2007 even though you did
not mean that


Anyway I just tested it and using my formula and the dates in your example
it returned 12/08 for MAX and 03/09 for MIN


--


Regards,


Peo Sjoblom


"Blueglass" wrote in message
...
Hello Peo,
Thanks for the information. I tried the formulas below, but couldn't
get
it work in all cases. Had a problem with this one:

Dates: 11/08 12/08 1/09 2/09 3/09 4/09 5/09 06/09
Values: 7 9 5 6 3 4 10 11
Returns Min = 11/08 and Max = 3/09



"Peo Sjoblom" wrote:

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

OK, I see what's happening. You're formula tests for max and min values and
returns the associated date.

What I really need is to find the first value in the range and return that
date and the last value in the range and return that date. When dealing with
hours, I'll have the start date of the work and the end date of the work.

Can we do that?


"Peo Sjoblom" wrote:

Works fine for me when I test it using your example, you might have text is
some of the values and your dates are very ambiguous

Excel can only accept dates that includes year, month and day if you omit
one it will put whatever it considers a date so if you type in 05/09 today
Excel will guess that you are trying to type 05/09/2007 even though you did
not mean that


Anyway I just tested it and using my formula and the dates in your example
it returned 12/08 for MAX and 03/09 for MIN


--


Regards,


Peo Sjoblom


"Blueglass" wrote in message
...
Hello Peo,
Thanks for the information. I tried the formulas below, but couldn't
get
it work in all cases. Had a problem with this one:

Dates: 11/08 12/08 1/09 2/09 3/09 4/09 5/09 06/09
Values: 7 9 5 6 3 4 10 11
Returns Min = 11/08 and Max = 3/09



"Peo Sjoblom" wrote:

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

OK, I got it to work using: =MIN(IF(D3:N3<"",D$2:N$2)). Thanks for the
assistance!

"Blueglass" wrote:

OK, I see what's happening. You're formula tests for max and min values and
returns the associated date.

What I really need is to find the first value in the range and return that
date and the last value in the range and return that date. When dealing with
hours, I'll have the start date of the work and the end date of the work.

Can we do that?


"Peo Sjoblom" wrote:

Works fine for me when I test it using your example, you might have text is
some of the values and your dates are very ambiguous

Excel can only accept dates that includes year, month and day if you omit
one it will put whatever it considers a date so if you type in 05/09 today
Excel will guess that you are trying to type 05/09/2007 even though you did
not mean that


Anyway I just tested it and using my formula and the dates in your example
it returned 12/08 for MAX and 03/09 for MIN


--


Regards,


Peo Sjoblom


"Blueglass" wrote in message
...
Hello Peo,
Thanks for the information. I tried the formulas below, but couldn't
get
it work in all cases. Had a problem with this one:

Dates: 11/08 12/08 1/09 2/09 3/09 4/09 5/09 06/09
Values: 7 9 5 6 3 4 10 11
Returns Min = 11/08 and Max = 3/09



"Peo Sjoblom" wrote:

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