Sumif with open Values

T

tamato43

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

Bob Umlas

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
 
B

Bob Phillips

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

--

HTH

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

tamato43

Thank you! You're a genius!!!

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

--

HTH

RP
(remove nothere from the email address if mailing direct)
Click to expand...
 
B

Bob Phillips

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)
 
Top