Remove Intermediate Formulas

[RLJ]

I want to try and remove some intermediate formulas from a shared workbook.

Excel Workbook
	B	C
2		April, 2011
3
4	Analyst	Total Hours
5	Joel McRae	8:24
Monthly Summary

The Formula in C5 Refers to a Range K5:K2000 which has this formula:
=IF(A5="","",DATE(YEAR(A5),MONTH(A5),DAY(1)))

What I am looking for is the formula in C5 to take the value of C2 which is 4/1/2011 and in Range A5:A2000 from the other sheet to Extract the Month, and Year, but keep the day as the 1.

I hope I have explained this well enough.

 

[Aladin Akyurek]

Looks like you want something like...

=IF(A5="","",
    SUMPRODUCT(
      --('Joel McRae'!$A$5:$A$2000-DAY('Joel McRae'!$A$5:$A$2000)+1=C2,
      'Joel McRae'!$F$5:$F$2000))

 

[RLJ]

I'm Getting a #Value! with this
=SUMPRODUCT(--('Joel McRae'!$A$5:$A$2000-DAY('Joel McRae'!$A$5:$A$2000)+1=C2,'Joel McRae'!$F$5:$F$2000))

I do not want the If Statement on the formula from the intermdiate formula that I am trying to get rid of.

Column A in the Joel McRae sheet is the date entered so all I want to extract from it is the Month & Year, but keep the day as 1 as is in cell C2.

 

[Aladin Akyurek]

I'm Getting a #Value! with this
=SUMPRODUCT(--('Joel McRae'!$A$5:$A$2000-DAY('Joel McRae'!$A$5:$A$2000)+1=C2,'Joel McRae'!$F$5:$F$2000))

I do not want the If Statement on the formula from the intermdiate formula that I am trying to get rid of.

Column A in the Joel McRae sheet is the date entered so all I want to extract from it is the Month & Year, but keep the day as 1 as is in cell C2.

Does...

=SUMPRODUCT(--(TEXT('Joel McRae'!$A$5:$A$2000,"mmmyy")=TEXT(C2,"mmmyy")),'Joel McRae'!$F$5:$F$2000))

do better?

 

[RLJ]

I get the "The Formula you typed contained an error."

 

[Aladin Akyurek]

I get the "The Formula you typed contained an error."

Oops... A stray paren. Try:

=SUMPRODUCT(--(TEXT('Joel McRae'!$A$5:$A$2000,"mmmyy")=TEXT(C2,"mmmyy")),'Joel McRae'!$F$5:$F$2000)

 

[T. Valko]

I want to try and remove some intermediate formulas from a shared workbook.

Monthly Summary

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 146px"><COL style="WIDTH: 166px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; TEXT-ALIGN: right">April, 2011</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 37px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #969696; TEXT-ALIGN: center">Analyst</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #969696; TEXT-ALIGN: center">Total Hours</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold">Joel McRae</TD><TD style="TEXT-ALIGN: center">8:24</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C5</TD><TD>=SUMIF('Joel McRae'!$K$5:$K$2000,C2,'Joel McRae'!$F$5:$F$2000)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

The Formula in C5 Refers to a Range K5:K2000 which has this formula:
=IF(A5="","",DATE(YEAR(A5),MONTH(A5),DAY(1)))

What I am looking for is the formula in C5 to take the value of C2 which is 4/1/2011 and in Range A5:A2000 from the other sheet to Extract the Month, and Year, but keep the day as the 1.

I hope I have explained this well enough.

In other words...

You want the sum for all of April 2011?

Here's one way...

=SUMIF('Joel McRae'!$K$5:$K$2000,">="&C2,'Joel McRae'!$F$5:$F$2000)-SUMIF('Joel McRae'!$K$5:$K$2000,">"&EOMONTH(C2,0),'Joel McRae'!$F$5:$F$2000)

Note that the EOMONTH function requires the Analysis ToolPak add-in be
installed if you're using a version of Excel prior to Excel 2007. If you enter the formula and get a #NAME? error look in Excel help for the EOMONTH function. It'll tell you how to fix the problem.

 

[T. Valko]

In other words...

You want the sum for all of April 2011?

Here's one way...

=SUMIF('Joel McRae'!$K$5:$K$2000,">="&C2,'Joel McRae'!$F$5:$F$2000)-SUMIF('Joel McRae'!$K$5:$K$2000,">"&EOMONTH(C2,0),'Joel McRae'!$F$5:$F$2000)

Note that the EOMONTH function requires the Analysis ToolPak add-in be
installed if you're using a version of Excel prior to Excel 2007. If you enter the formula and get a #NAME? error look in Excel help for the EOMONTH function. It'll tell you how to fix the problem.

If you're using Excel 2007 or later...

=SUMIFS('Joel McRae'!$F$5:$F$2000,'Joel McRae'!$K$5:$K$2000,">="&C2,'Joel McRae'!$K$5:$K$2000,"<="&EOMONTH(C2,0))