sum if dates conditional
 

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),MONT H(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

sum if dates conditional
 

=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" wrote in message
...
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),MONT H(A48),DAY(M49))=All!J3:J
214,DATE(YEAR(A48),MONTH(A48),DAY(N49))<=All!J3:J2 14),"1","0")))

But that just gives 0. Any ideas?

-Benjamin

sum if dates conditional
 

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" wrote in message
...
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),MONT H(A48),DAY(M49))=All!J3:J
214,DATE(YEAR(A48),MONTH(A48),DAY(N49))<=All!J3:J2 14),"1","0")))

But that just gives 0. Any ideas?

-Benjamin

sum if dates conditional
 

What do you get?

--

HTH

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


"benjo4u" wrote in message
...
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" wrote in message
...
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),MONT H(A48),DAY(M49))=All!J3:J
214,DATE(YEAR(A48),MONTH(A48),DAY(N49))<=All!J3:J2 14),"1","0")))

But that just gives 0. Any ideas?

-Benjamin

sum if dates conditional
 

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.

sum if dates conditional
 

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" wrote in message
oups.com...
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.

sum if dates conditional
 

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

--

HTH

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


"gpie" wrote in message
oups.com...
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.

sum if dates conditional
 

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" wrote in message
oups.com...
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.

sum if dates conditional
 

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