sum if dates conditional

[Guest]

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)

 

[Guest]

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]

What do you get?

--

HTH

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

 

[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)

 

[Bob Phillips]

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

--

HTH

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

 

[Guest]

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

 

[Guest]

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