sum, not count money column in between date columns

[benjo4u]

I recently got some help on counting the number of rows with data between
certain dates. Now I need to sum up a dollar amount in rows between certain
dates. As of now, I have

=SUM(IF(AND(M49<=All!H2:H5000,N49>=All!H2:H5000),All!F2:F500,"0"))

where m49 and n49 are the range of the dates, H is the date column in the
worksheet and f is the money column in the worksheet. I also tried this with
a sumif function which didn't work.

and while I'm here, how would I output ideally the names of people donating
more than $100,000 or at least the number of people, again, within certain
dates

=SUMPRODUCT(--(All!F2:F5000>="100000"),--(M49<=All!H2:H5000),--(N49>=All!H2:H5000))

Thanks
-Benjamin

[Bob Phillips]

That formula should work okay if the data is good. Did you array enter it
(Ctrl-Shift-Enter)? What did you get?

--

HTH

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


"benjo4u" <[email protected]> wrote in message
news:[email protected]...
> I recently got some help on counting the number of rows with data between
> certain dates. Now I need to sum up a dollar amount in rows between
certain
> dates. As of now, I have
>
> =SUM(IF(AND(M49<=All!H2:H5000,N49>=All!H2:H5000),All!F2:F500,"0"))
>
> where m49 and n49 are the range of the dates, H is the date column in the
> worksheet and f is the money column in the worksheet. I also tried this
with
> a sumif function which didn't work.
>
> and while I'm here, how would I output ideally the names of people
donating
> more than $100,000 or at least the number of people, again, within certain
> dates
>
>
=SUMPRODUCT(--(All!F2:F5000>="100000"),--(M49<=All!H2:H5000),--(N49>=All!H2:
H5000))
>
> Thanks
> -Benjamin

[benjo4u]

I did that, and the few places where there were values went to 0. Any ideas?
It looks good to me, but I can't figure it out. Also, any help on
outputting names when the amount is greater than $100,000?

Thanks

-Benjamin

"Bob Phillips" wrote:

> That formula should work okay if the data is good. Did you array enter it
> (Ctrl-Shift-Enter)? What did you get?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "benjo4u" <[email protected]> wrote in message
> news:[email protected]...
> > I recently got some help on counting the number of rows with data between
> > certain dates. Now I need to sum up a dollar amount in rows between
> certain
> > dates. As of now, I have
> >
> > =SUM(IF(AND(M49<=All!H2:H5000,N49>=All!H2:H5000),All!F2:F500,"0"))
> >
> > where m49 and n49 are the range of the dates, H is the date column in the
> > worksheet and f is the money column in the worksheet. I also tried this
> with
> > a sumif function which didn't work.
> >
> > and while I'm here, how would I output ideally the names of people
> donating
> > more than $100,000 or at least the number of people, again, within certain
> > dates
> >
> >
> =SUMPRODUCT(--(All!F2:F5000>="100000"),--(M49<=All!H2:H5000),--(N49>=All!H2:
> H5000))
> >
> > Thanks
> > -Benjamin
>
>
>

[benjo4u]

I browsed the boards and came up with the following which worked

=SUMPRODUCT((M49<=All!H2:H4998)*(N49>=All!H2:H4998)*All!D2:D4998)

m and n are bookend dates to compare to h and d is the amount.

Thanks
-Benjamin

"benjo4u" wrote:

> I did that, and the few places where there were values went to 0. Any ideas?
> It looks good to me, but I can't figure it out. Also, any help on
> outputting names when the amount is greater than $100,000?
>
> Thanks
>
> -Benjamin
>
> "Bob Phillips" wrote:
>
> > That formula should work okay if the data is good. Did you array enter it
> > (Ctrl-Shift-Enter)? What did you get?
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "benjo4u" <[email protected]> wrote in message
> > news:[email protected]...
> > > I recently got some help on counting the number of rows with data between
> > > certain dates. Now I need to sum up a dollar amount in rows between
> > certain
> > > dates. As of now, I have
> > >
> > > =SUM(IF(AND(M49<=All!H2:H5000,N49>=All!H2:H5000),All!F2:F500,"0"))
> > >
> > > where m49 and n49 are the range of the dates, H is the date column in the
> > > worksheet and f is the money column in the worksheet. I also tried this
> > with
> > > a sumif function which didn't work.
> > >
> > > and while I'm here, how would I output ideally the names of people
> > donating
> > > more than $100,000 or at least the number of people, again, within certain
> > > dates
> > >
> > >
> > =SUMPRODUCT(--(All!F2:F5000>="100000"),--(M49<=All!H2:H5000),--(N49>=All!H2:
> > H5000))
> > >
> > > Thanks
> > > -Benjamin
> >
> >
> >