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,
Dick, Harry" Should I try something like

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

or even

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

recursive output if amount x
 

$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,
Dick, Harry" Should I try something like

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

or even

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

recursive output if amount x
 

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,
Dick, Harry" Should I try something like

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

or even

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

recursive output if amount x
 

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,
Dick, Harry" Should I try something like

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

or even

=SUM(IF(All!D2:D4998="100000",IF(M49<=All!H2:H499 8,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.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

recursive output if amount x
 

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

"Dick 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,
Dick, Harry" Should I try something like

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

or even

=SUM(IF(All!D2:D4998="100000",IF(M49<=All!H2:H499 8,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.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

recursive output if amount x
 

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

-Benjamin

"Dick 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,
Dick, Harry" Should I try something like

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

or even

=SUM(IF(All!D2:D4998="100000",IF(M49<=All!H2:H499 8,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.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com