recursive output if amount > x

I'm trying to output the names of all donors of $100,000 or greater per week
of giving. I know how to sum all the money or the count all greater than
$100k, but this is in between. My best guess is:

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

where d is the dollar amount, m and n are the bookend dates to compare to h,
the row's date, and q is the name of the fellow. I want an output like "John,
****, Harry" Should I try something like

=SUM(IF(AND(All!D2:D4998>="100000",M49<=All!H2:H4998,N49>=All!H2:H4998),All!Q2:Q4998,""))

or even

=SUM(IF(All!D2:D4998>="100000",IF(M49<=All!H2:H4998,IF(N49>=All!H2:H4998,All!Q2:Q4998&", ",""),""),""))

$100K?? Where can I get that list of donors?

Have you tried using an array formula? At a glance that seems to be what
you are trying to do. See help on "Creating an array formula"


"benjo4u" wrote:

> I'm trying to output the names of all donors of $100,000 or greater per week
> of giving. I know how to sum all the money or the count all greater than
> $100k, but this is in between. My best guess is:
>
> =SUMPRODUCT((All!D2:D4998>="100000")*(M49<=All!H2:H4998)*(N49>=All!H2:H4998)*(All!Q2:Q4998))
>
> where d is the dollar amount, m and n are the bookend dates to compare to h,
> the row's date, and q is the name of the fellow. I want an output like "John,
> ****, Harry" Should I try something like
>
> =SUM(IF(AND(All!D2:D4998>="100000",M49<=All!H2:H4998,N49>=All!H2:H4998),All!Q2:Q4998,""))
>
> or even
>
> =SUM(IF(All!D2:D4998>="100000",IF(M49<=All!H2:H4998,IF(N49>=All!H2:H4998,All!Q2:Q4998&", ",""),""),""))
>

making it an array doesn't work... and I wish I had my own endowment, too

"Charlie" wrote:

> $100K?? Where can I get that list of donors?
>
> Have you tried using an array formula? At a glance that seems to be what
> you are trying to do. See help on "Creating an array formula"
>
>
> "benjo4u" wrote:
>
> > I'm trying to output the names of all donors of $100,000 or greater per week
> > of giving. I know how to sum all the money or the count all greater than
> > $100k, but this is in between. My best guess is:
> >
> > =SUMPRODUCT((All!D2:D4998>="100000")*(M49<=All!H2:H4998)*(N49>=All!H2:H4998)*(All!Q2:Q4998))
> >
> > where d is the dollar amount, m and n are the bookend dates to compare to h,
> > the row's date, and q is the name of the fellow. I want an output like "John,
> > ****, Harry" Should I try something like
> >
> > =SUM(IF(AND(All!D2:D4998>="100000",M49<=All!H2:H4998,N49>=All!H2:H4998),All!Q2:Q4998,""))
> >
> > or even
> >
> > =SUM(IF(All!D2:D4998>="100000",IF(M49<=All!H2:H4998,IF(N49>=All!H2:H4998,All!Q2:Q4998&", ",""),""),""))
> >

benjo4u wrote:
> I'm trying to output the names of all donors of $100,000 or greater
> per week
> of giving. I know how to sum all the money or the count all greater
> than $100k, but this is in between. My best guess is:
>
> =SUMPRODUCT((All!D2:D4998>="100000")*(M49<=All!H2:H4998)*(N49>=All!H2:H4998)*(All!Q2:Q4998))
>
> where d is the dollar amount, m and n are the bookend dates to
> compare to h,
> the row's date, and q is the name of the fellow. I want an output
> like "John,
> ****, Harry" Should I try something like
>
> =SUM(IF(AND(All!D2:D4998>="100000",M49<=All!H2:H4998,N49>=All!H2:H4998),All!Q2:Q4998,""))
>
> or even
>
> =SUM(IF(All!D2:D4998>="100000",IF(M49<=All!H2:H4998,IF(N49>=All!H2:H4998,All!Q2:Q4998&",
> ",""),""),""))

You can't concatenate strings in an array formula, but it would be really
cool if you could. I think your best bet is a user-defined function

Public Function DonorNames(rNames As Range, _
rDates As Range, _
rAmt As Range, _
dtLower As Date, _
dtUpper As Date, _
dLimit As Double) As String

Dim i As Long
Dim sTemp As String

For i = 1 To rAmt.Cells.Count
If rAmt.Cells(i).Value > dLimit Then
If rDates.Cells(i).Value >= dtLower And rDates.Cells(i).Value <=
dtUpper Then
sTemp = sTemp & rNames.Cells(i).Text & ","
End If
End If
Next i

DonorNames = Left(sTemp, Len(sTemp) - 1)

End Function

Use it like

=donornames(A3:A6,B3:B6,C3:C6,DATE(2005,6,1),DATE(2005,7,31),100000)

to get all donor names from june 1st to july 31st over 100,000.

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com

looks great, thanks, but my boss just got suddenly afraid that it was too
complex for anyone to use (even though I think this simplifies things). so,
looks like it might not get used in the end cool code, though

"**** Kusleika" wrote:

> benjo4u wrote:
> > I'm trying to output the names of all donors of $100,000 or greater
> > per week
> > of giving. I know how to sum all the money or the count all greater
> > than $100k, but this is in between. My best guess is:
> >
> > =SUMPRODUCT((All!D2:D4998>="100000")*(M49<=All!H2:H4998)*(N49>=All!H2:H4998)*(All!Q2:Q4998))
> >
> > where d is the dollar amount, m and n are the bookend dates to
> > compare to h,
> > the row's date, and q is the name of the fellow. I want an output
> > like "John,
> > ****, Harry" Should I try something like
> >
> > =SUM(IF(AND(All!D2:D4998>="100000",M49<=All!H2:H4998,N49>=All!H2:H4998),All!Q2:Q4998,""))
> >
> > or even
> >
> > =SUM(IF(All!D2:D4998>="100000",IF(M49<=All!H2:H4998,IF(N49>=All!H2:H4998,All!Q2:Q4998&",
> > ",""),""),""))
>
> You can't concatenate strings in an array formula, but it would be really
> cool if you could. I think your best bet is a user-defined function
>
> Public Function DonorNames(rNames As Range, _
> rDates As Range, _
> rAmt As Range, _
> dtLower As Date, _
> dtUpper As Date, _
> dLimit As Double) As String
>
> Dim i As Long
> Dim sTemp As String
>
> For i = 1 To rAmt.Cells.Count
> If rAmt.Cells(i).Value > dLimit Then
> If rDates.Cells(i).Value >= dtLower And rDates.Cells(i).Value <=
> dtUpper Then
> sTemp = sTemp & rNames.Cells(i).Text & ","
> End If
> End If
> Next i
>
> DonorNames = Left(sTemp, Len(sTemp) - 1)
>
> End Function
>
> Use it like
>
> =donornames(A3:A6,B3:B6,C3:C6,DATE(2005,6,1),DATE(2005,7,31),100000)
>
> to get all donor names from june 1st to july 31st over 100,000.
>
> --
> **** Kusleika
> Excel MVP
> Daily Dose of Excel
> www.*****-blog.com
>
>
>

fyi, it gave me a "name" error.
the debug compiles ok

-Benjamin

"**** Kusleika" wrote:

> benjo4u wrote:
> > I'm trying to output the names of all donors of $100,000 or greater
> > per week
> > of giving. I know how to sum all the money or the count all greater
> > than $100k, but this is in between. My best guess is:
> >
> > =SUMPRODUCT((All!D2:D4998>="100000")*(M49<=All!H2:H4998)*(N49>=All!H2:H4998)*(All!Q2:Q4998))
> >
> > where d is the dollar amount, m and n are the bookend dates to
> > compare to h,
> > the row's date, and q is the name of the fellow. I want an output
> > like "John,
> > ****, Harry" Should I try something like
> >
> > =SUM(IF(AND(All!D2:D4998>="100000",M49<=All!H2:H4998,N49>=All!H2:H4998),All!Q2:Q4998,""))
> >
> > or even
> >
> > =SUM(IF(All!D2:D4998>="100000",IF(M49<=All!H2:H4998,IF(N49>=All!H2:H4998,All!Q2:Q4998&",
> > ",""),""),""))
>
> You can't concatenate strings in an array formula, but it would be really
> cool if you could. I think your best bet is a user-defined function
>
> Public Function DonorNames(rNames As Range, _
> rDates As Range, _
> rAmt As Range, _
> dtLower As Date, _
> dtUpper As Date, _
> dLimit As Double) As String
>
> Dim i As Long
> Dim sTemp As String
>
> For i = 1 To rAmt.Cells.Count
> If rAmt.Cells(i).Value > dLimit Then
> If rDates.Cells(i).Value >= dtLower And rDates.Cells(i).Value <=
> dtUpper Then
> sTemp = sTemp & rNames.Cells(i).Text & ","
> End If
> End If
> Next i
>
> DonorNames = Left(sTemp, Len(sTemp) - 1)
>
> End Function
>
> Use it like
>
> =donornames(A3:A6,B3:B6,C3:C6,DATE(2005,6,1),DATE(2005,7,31),100000)
>
> to get all donor names from june 1st to july 31st over 100,000.
>
> --
> **** Kusleika
> Excel MVP
> Daily Dose of Excel
> www.*****-blog.com
>
>
>