Sumif with open Values
 

If the cell is formatted to show a date as follows:

"Monday 3/21/2005"

How can I create a "SUMIF" condition to say".....

SUMIF "Monday" shows anywhere in the cell.

Have a look in HELP index for WEEKDAY

--
Don Guillett
SalesAid Software

"tamato43" wrote in message
...
If the cell is formatted to show a date as follows:

"Monday 3/21/2005"

How can I create a "SUMIF" condition to say".....

SUMIF "Monday" shows anywhere in the cell.

Cells FORMATTED as dddd m/d/yyyy are different from cells actually
containing the text MONDAY.... etc.
The formula bar for 3/21/05 would say 3/21/2005 but the cell could show
MONDAY 3/21/05, and you can't find MONDAY in that cell. However, you can use
the Weekday function to see if the cell's weekday returns 2 and use
SUMPRODUCT:
=SUMPRODUCT((WEEKDAY(A1:A100=2)*B1:B100) to return the sum of all of column
B where A is monday.
If text, use =SUMIF(A1:A100,"MONDAY*",B1:B100)

HTH
Bob Umlas
Excel MVP

"tamato43" wrote in message
...
If the cell is formatted to show a date as follows:

"Monday 3/21/2005"

How can I create a "SUMIF" condition to say".....

SUMIF "Monday" shows anywhere in the cell.

=SUMPRODUCT(--(TEXT(A1:A25,"dddd")="Monday"),B1:B25)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"tamato43" wrote in message
...
If the cell is formatted to show a date as follows:

"Monday 3/21/2005"

How can I create a "SUMIF" condition to say".....

SUMIF "Monday" shows anywhere in the cell.

Thank you! You're a genius!!!

"Bob Phillips" wrote:

=SUMPRODUCT(--(TEXT(A1:A25,"dddd")="Monday"),B1:B25)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"tamato43" wrote in message
...
If the cell is formatted to show a date as follows:

"Monday 3/21/2005"

How can I create a "SUMIF" condition to say".....

SUMIF "Monday" shows anywhere in the cell.

Not really, the others are correct, I just frigged it so you thought you
were getting what you asked for :-)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"tamato43" wrote in message
...
Thank you! You're a genius!!!

"Bob Phillips" wrote:

=SUMPRODUCT(--(TEXT(A1:A25,"dddd")="Monday"),B1:B25)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"tamato43" wrote in message
...
If the cell is formatted to show a date as follows:

"Monday 3/21/2005"

How can I create a "SUMIF" condition to say".....

SUMIF "Monday" shows anywhere in the cell.