Help with Dates in a Formula
 

Hi,

I need to change the reference (=B474) in this formula to something that
will select all entries in the range A12:A399 that fall within the month of
SEP 06 then sum the numbers found in Column E. The cells in range A12:A39
are formatted as dates. I don't know how to enter this. Please help.

=SUMPRODUCT(($B$12:$B$399="PI")*($A$12:$A$399=B474 )*($E$12:$E$399))

Thanks

--
Jerry

Help with Dates in a Formula
 

Try this im assuming that there is no data for 2007
9 is for the month of Sept
=SUMPRODUCT((MONTH($A$12:$A$399)=9)*($B$12:$B$399= "PI")*($E$12:$E$399))

"Jerry Rogers" wrote:

Hi,

I need to change the reference (=B474) in this formula to something that
will select all entries in the range A12:A399 that fall within the month of
SEP 06 then sum the numbers found in Column E. The cells in range A12:A39
are formatted as dates. I don't know how to enter this. Please help.

=SUMPRODUCT(($B$12:$B$399="PI")*($A$12:$A$399=B474 )*($E$12:$E$399))

Thanks

--
Jerry

Help with Dates in a Formula
 

One way

Try:
=SUMPRODUCT(($B$12:$B$399="PI")*(TEXT($A$12:$A$399 ,"mmmyy")="Sep06")*($E$12:$E$399))

Above assumes real dates within A12:A399
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jerry Rogers" wrote:
Hi,

I need to change the reference (=B474) in this formula to something that
will select all entries in the range A12:A399 that fall within the month of
SEP 06 then sum the numbers found in Column E. The cells in range A12:A39
are formatted as dates. I don't know how to enter this. Please help.

=SUMPRODUCT(($B$12:$B$399="PI")*($A$12:$A$399=B474 )*($E$12:$E$399))

Thanks

--
Jerry

Help with Dates in a Formula
 

I like not to hard code formula so this example lets you choose in cell A1
a number from 1 - 12 to represent the month and cell A2 lets you choose the
year
Try it out let me know if this is what your looking for
=SUMPRODUCT((MONTH($A$12:$A$399)=A1)*(YEAR($A$12:$ A$399)=A2)*($B$12:$B$399="PI")*($E$12:$E$399))

"Jerry Rogers" wrote:

Hi,

I need to change the reference (=B474) in this formula to something that
will select all entries in the range A12:A399 that fall within the month of
SEP 06 then sum the numbers found in Column E. The cells in range A12:A39
are formatted as dates. I don't know how to enter this. Please help.

=SUMPRODUCT(($B$12:$B$399="PI")*($A$12:$A$399=B474 )*($E$12:$E$399))

Thanks

--
Jerry

Help with Dates in a Formula
 

Hi Mike,

Thanks for the response.

I plugged in the formula you provided and received a #VALUE message. I do
have data with dates in 2007. Is the 2007 data the cause of the error? How
can I differentiate between years?
--
Jerry


"Mike" wrote:

Try this im assuming that there is no data for 2007
9 is for the month of Sept
=SUMPRODUCT((MONTH($A$12:$A$399)=9)*($B$12:$B$399= "PI")*($E$12:$E$399))

"Jerry Rogers" wrote:

Hi,

I need to change the reference (=B474) in this formula to something that
will select all entries in the range A12:A399 that fall within the month of
SEP 06 then sum the numbers found in Column E. The cells in range A12:A39
are formatted as dates. I don't know how to enter this. Please help.

=SUMPRODUCT(($B$12:$B$399="PI")*($A$12:$A$399=B474 )*($E$12:$E$399))

Thanks

--
Jerry

Help with Dates in a Formula
 

I like not to hard code formula so this example lets you choose in cell A1
a number from 1 - 12 to represent the month and cell A2 lets you choose the
year
Try it out let me know if this is what your looking for
=SUMPRODUCT((MONTH($A$12:$A$399)=A1)*(YEAR($A$12:$ A$399)=A2)*($B$12:$B$399="PI")*($E$12:$E$399))


"Jerry Rogers" wrote:

Hi Mike,

Thanks for the response.

I plugged in the formula you provided and received a #VALUE message. I do
have data with dates in 2007. Is the 2007 data the cause of the error? How
can I differentiate between years?
--
Jerry


"Mike" wrote:

Try this im assuming that there is no data for 2007
9 is for the month of Sept
=SUMPRODUCT((MONTH($A$12:$A$399)=9)*($B$12:$B$399= "PI")*($E$12:$E$399))

"Jerry Rogers" wrote:

Hi,

I need to change the reference (=B474) in this formula to something that
will select all entries in the range A12:A399 that fall within the month of
SEP 06 then sum the numbers found in Column E. The cells in range A12:A39
are formatted as dates. I don't know how to enter this. Please help.

=SUMPRODUCT(($B$12:$B$399="PI")*($A$12:$A$399=B474 )*($E$12:$E$399))

Thanks

--
Jerry

Help with Dates in a Formula
 

Thanks Mike,
I plugged in Max's suggestion just before this arrived and it worked fine.
I haven't tried your suggestion so I'm not sure if it works or not.




--
Jerry


"Mike" wrote:

I like not to hard code formula so this example lets you choose in cell A1
a number from 1 - 12 to represent the month and cell A2 lets you choose the
year
Try it out let me know if this is what your looking for
=SUMPRODUCT((MONTH($A$12:$A$399)=A1)*(YEAR($A$12:$ A$399)=A2)*($B$12:$B$399="PI")*($E$12:$E$399))

"Jerry Rogers" wrote:

Hi,

I need to change the reference (=B474) in this formula to something that
will select all entries in the range A12:A399 that fall within the month of
SEP 06 then sum the numbers found in Column E. The cells in range A12:A39
are formatted as dates. I don't know how to enter this. Please help.

=SUMPRODUCT(($B$12:$B$399="PI")*($A$12:$A$399=B474 )*($E$12:$E$399))

Thanks

--
Jerry