sum if dates conditional

[benjo4u]

I have a spreadsheet with two columns:
H contains dates and J contains x's.

The dates go from May to December. I have figured out the days that each
month starts and ends. So, I want to sum up all rows with an x and in between
two dates. For this, I have

=SUM(IF(All!J3:J214="x",IF(AND(DATE(YEAR(A48),MONTH(A48),DAY(M49))>=All!J3:J214,DATE(YEAR(A48),MONTH(A48),DAY(N49))<=All!J3:J214),"1","0")))

But that just gives 0. Any ideas?

-Benjamin

[Bob Phillips]

=SUMPRODUCT(--(All!J3:J214="x"),--(DATE(YEAR(A48),MONTH(A48),DAY(M49))>=All!
J3:J214),--(DATE(YEAR(A48),MONTH(A48),DAY(N49))<=All!J3:J214))

--

HTH

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


"benjo4u" <[email protected]> wrote in message
news:[email protected]...
> I have a spreadsheet with two columns:
> H contains dates and J contains x's.
>
> The dates go from May to December. I have figured out the days that each
> month starts and ends. So, I want to sum up all rows with an x and in
between
> two dates. For this, I have
>
>
=SUM(IF(All!J3:J214="x",IF(AND(DATE(YEAR(A48),MONTH(A48),DAY(M49))>=All!J3:J
214,DATE(YEAR(A48),MONTH(A48),DAY(N49))<=All!J3:J214),"1","0")))
>
> But that just gives 0. Any ideas?
>
> -Benjamin
>

[benjo4u]

I had made a mistake in typing it in (wrong columns), but even with your new
formula it still doesn't work.

=SUMPRODUCT(--(All!J2:J214="x"),--(DATE(YEAR(A47),MONTH(A47),DAY(M48))>=All!H2:H214),--(DATE(YEAR(A47),MONTH(A47),DAY(N48))<=All!H2:H214))

the date column is formatted as date...

-Benjamin

"Bob Phillips" wrote:

> =SUMPRODUCT(--(All!J3:J214="x"),--(DATE(YEAR(A48),MONTH(A48),DAY(M49))>=All!
> J3:J214),--(DATE(YEAR(A48),MONTH(A48),DAY(N49))<=All!J3:J214))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "benjo4u" <[email protected]> wrote in message
> news:[email protected]...
> > I have a spreadsheet with two columns:
> > H contains dates and J contains x's.
> >
> > The dates go from May to December. I have figured out the days that each
> > month starts and ends. So, I want to sum up all rows with an x and in
> between
> > two dates. For this, I have
> >
> >
> =SUM(IF(All!J3:J214="x",IF(AND(DATE(YEAR(A48),MONTH(A48),DAY(M49))>=All!J3:J
> 214,DATE(YEAR(A48),MONTH(A48),DAY(N49))<=All!J3:J214),"1","0")))
> >
> > But that just gives 0. Any ideas?
> >
> > -Benjamin
> >
>
>
>

[Bob Phillips]

What do you get?

--

HTH

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


"benjo4u" <[email protected]> wrote in message
news:[email protected]...
> I had made a mistake in typing it in (wrong columns), but even with your
new
> formula it still doesn't work.
>
>
=SUMPRODUCT(--(All!J2:J214="x"),--(DATE(YEAR(A47),MONTH(A47),DAY(M48))>=All!
H2:H214),--(DATE(YEAR(A47),MONTH(A47),DAY(N48))<=All!H2:H214))
>
> the date column is formatted as date...
>
> -Benjamin
>
> "Bob Phillips" wrote:
>
> >
=SUMPRODUCT(--(All!J3:J214="x"),--(DATE(YEAR(A48),MONTH(A48),DAY(M49))>=All!
> > J3:J214),--(DATE(YEAR(A48),MONTH(A48),DAY(N49))<=All!J3:J214))
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "benjo4u" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have a spreadsheet with two columns:
> > > H contains dates and J contains x's.
> > >
> > > The dates go from May to December. I have figured out the days that
each
> > > month starts and ends. So, I want to sum up all rows with an x and in
> > between
> > > two dates. For this, I have
> > >
> > >
> >
=SUM(IF(All!J3:J214="x",IF(AND(DATE(YEAR(A48),MONTH(A48),DAY(M49))>=All!J3:J
> > 214,DATE(YEAR(A48),MONTH(A48),DAY(N49))<=All!J3:J214),"1","0")))
> > >
> > > But that just gives 0. Any ideas?
> > >
> > > -Benjamin
> > >
> >
> >
> >

[gpie]

I have encountered the same problem, so I added columns to my original
dataset that separate the date into Month, Day and Year, and then added
conditions to the SUMPRODUCT formula accordingly.

I believe that the date functions cannot be analyzed in an array (at
least that is what I have been told, and it seems to be true). I use
Excel 2000 on Windows XP.

[STEVE BELL]

Don't know if this would help -
but I just finished setting up some formulas for a friend.
The formulas did a SumIf. The ranges were determined using the Indirect
function.
The data condition(s) were created by separating the date out of the column
header.
The headers were "Text" "Date"

This was used to summarize data from many sheets onto a master sheet.

If you think this would help - let me know...

(you could also use this in a SumProduct formula)

--
steveB

Remove "AYN" from email to respond
"gpie" <[email protected]> wrote in message
news:[email protected]...
>I have encountered the same problem, so I added columns to my original
> dataset that separate the date into Month, Day and Year, and then added
> conditions to the SUMPRODUCT formula accordingly.
>
> I believe that the date functions cannot be analyzed in an array (at
> least that is what I have been told, and it seems to be true). I use
> Excel 2000 on Windows XP.
>

[Bob Phillips]

Not correct, we do it all the time in responses here.

--

HTH

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


"gpie" <[email protected]> wrote in message
news:[email protected]...
> I have encountered the same problem, so I added columns to my original
> dataset that separate the date into Month, Day and Year, and then added
> conditions to the SUMPRODUCT formula accordingly.
>
> I believe that the date functions cannot be analyzed in an array (at
> least that is what I have been told, and it seems to be true). I use
> Excel 2000 on Windows XP.
>

[benjo4u]

removing the date functions worked

SUMPRODUCT(--(All!J2:J214="x"),--(M49<=All!H2:H214),--(N49>=All!H2:H214))

where m49 and n49 are the date() functions

Thanks!

-Benjamin

"STEVE BELL" wrote:

> Don't know if this would help -
> but I just finished setting up some formulas for a friend.
> The formulas did a SumIf. The ranges were determined using the Indirect
> function.
> The data condition(s) were created by separating the date out of the column
> header.
> The headers were "Text" "Date"
>
> This was used to summarize data from many sheets onto a master sheet.
>
> If you think this would help - let me know...
>
> (you could also use this in a SumProduct formula)
>
> --
> steveB
>
> Remove "AYN" from email to respond
> "gpie" <[email protected]> wrote in message
> news:[email protected]...
> >I have encountered the same problem, so I added columns to my original
> > dataset that separate the date into Month, Day and Year, and then added
> > conditions to the SUMPRODUCT formula accordingly.
> >
> > I believe that the date functions cannot be analyzed in an array (at
> > least that is what I have been told, and it seems to be true). I use
> > Excel 2000 on Windows XP.
> >
>
>
>

[benjo4u]

another question: given that
=SUMPRODUCT(--(All!L2:L5000="x"),--(M49<=All!H2:H5000),--(N49>=All!H2:H5000))

works to count the number of entries, how can I sum up dollaramounts within
the same date periods (bordered by m49 and n49)

thanks
-Benjamin