Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
=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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Cannot open Excel attachment from e-mail when Excel is already ope | Excel Discussion (Misc queries) | |||
When I open excel document it always ask if I want to open it in . | Excel Discussion (Misc queries) | |||
Cannot open a file that Excel says is open | Excel Discussion (Misc queries) | |||
SUM(IF( Array to avoid #NUM! values | Excel Worksheet Functions |