using DSUM in formulae instead of SUMIF
 

I have a database that consists entries that are either cash or check. I
want to be able to use the DSUM function to sort by date (the first column
below). I have been able to do a workaround via SUMIF, but I feel that DSUM
ought to work.

=SUMIF(Date,A1,Cash)
where each record has a different value in the CRITERIA field and I am able
to use labels for the RANGE and SUMRANGE arguments supplied to SUMIF.

What I actually need is a way to sort for two columns (Date and Contact),
but for starters sorting by date would be fantastic. Thanks in advance for
your help!


Date Contact Signature Cash Check
10/1/2005 Kevin Gabe Kent 5
10/1/2005 Kevin Vernon Lukehart 10
10/2/2005 Kevin Don Curry 11
10/2/2005 George Wendy Bryant 36
10/3/2005 George Dorothy Miller 20
10/4/2005 Bart Jeanette Fox 5
10/4/2005 Bart Stacy Taiber 10
10/4/2005 Bart Jill Norton 11
10/5/2005 Bart Judith Jessup 36
10/5/2005 Rubben Ron Paine 15
10/5/2005 Rubben Kathy Weinberg 5
10/5/2005 Rubben Amy Lau 10
10/6/2005 Craig Marissa Smith 36
10/6/2005 Craig Marlin Weber 20
10/6/2005 Craig John Smith 15

I reposted the data with commas separating the data below...

Date,Contact,Signature,Cash,Check
10/1/2005,Kevin,Gabe Kent,5,,
10/1/2005,Kevin,Vernon Lukehart,,10
10/2/2005,Kevin,Don Curry,11,,
10/2/2005,George,Wendy Bryant,,36
10/3/2005,George,Dorothy Miller,,20
10/4/2005,Bart,Jeanette Fox,5,,
10/4/2005,Bart,Stacy Taiber,10,,
10/4/2005,Bart,Jill Norton,11,,
10/5/2005,Bart,Judith Jessup,36,,
10/5/2005,Rubben,Ron Paine,,15
10/5/2005,Rubben,Kathy Weinberg,5,,
10/5/2005,Rubben,Amy Lau,10,,
10/6/2005,Craig,Marissa Smith,,36
10/6/2005,Craig,Marlin Weber,,20
10/6/2005,Craig,John Smith,15,,

Why not just use a pivot Table?

If you wanted to use DSum, you would need a separate criteria range for each
unique date.

--
Regards,
Tom Ogilvy


"Kevin C. McGregor" wrote in message
...
I reposted the data with commas separating the data below...

Date,Contact,Signature,Cash,Check
10/1/2005,Kevin,Gabe Kent,5,,
10/1/2005,Kevin,Vernon Lukehart,,10
10/2/2005,Kevin,Don Curry,11,,
10/2/2005,George,Wendy Bryant,,36
10/3/2005,George,Dorothy Miller,,20
10/4/2005,Bart,Jeanette Fox,5,,
10/4/2005,Bart,Stacy Taiber,10,,
10/4/2005,Bart,Jill Norton,11,,
10/5/2005,Bart,Judith Jessup,36,,
10/5/2005,Rubben,Ron Paine,,15
10/5/2005,Rubben,Kathy Weinberg,5,,
10/5/2005,Rubben,Amy Lau,10,,
10/6/2005,Craig,Marissa Smith,,36
10/6/2005,Craig,Marlin Weber,,20
10/6/2005,Craig,John Smith,15,,

If I use Pivot Table won't I have to adjust the Pivot for each change? What
I want to be able to do is add to my data range daily and have the
spreadsheet caculate accordingly. Should I assume, then, that the DSUM
function isn't really appropriate for what I'm attempting to do -- i.e.,
take data entered:
date1
date2
date3
date4
and then manipulate it accordingly?

"Tom Ogilvy" wrote in message
...
Why not just use a pivot Table?

If you wanted to use DSum, you would need a separate criteria range for
each
unique date.

--
Regards,
Tom Ogilvy


"Kevin C. McGregor" wrote in message
...
I reposted the data with commas separating the data below...

Date,Contact,Signature,Cash,Check
10/1/2005,Kevin,Gabe Kent,5,,
10/1/2005,Kevin,Vernon Lukehart,,10
10/2/2005,Kevin,Don Curry,11,,
10/2/2005,George,Wendy Bryant,,36
10/3/2005,George,Dorothy Miller,,20
10/4/2005,Bart,Jeanette Fox,5,,
10/4/2005,Bart,Stacy Taiber,10,,
10/4/2005,Bart,Jill Norton,11,,
10/5/2005,Bart,Judith Jessup,36,,
10/5/2005,Rubben,Ron Paine,,15
10/5/2005,Rubben,Kathy Weinberg,5,,
10/5/2005,Rubben,Amy Lau,10,,
10/6/2005,Craig,Marissa Smith,,36
10/6/2005,Craig,Marlin Weber,,20
10/6/2005,Craig,John Smith,15,,

If the pivot table source data will change size frequently, you can use
a dynamic range as the pivot table source, and it will automatically
expand as rows or columns are added. There are instructions he

http://www.contextures.com/xlPivot01.html

Kevin C. McGregor wrote:
If I use Pivot Table won't I have to adjust the Pivot for each change? What
I want to be able to do is add to my data range daily and have the
spreadsheet caculate accordingly. Should I assume, then, that the DSUM
function isn't really appropriate for what I'm attempting to do -- i.e.,
take data entered:
date1
date2
date3
date4
and then manipulate it accordingly?

"Tom Ogilvy" wrote in message
...

Why not just use a pivot Table?

If you wanted to use DSum, you would need a separate criteria range for

each

unique date.

--
Regards,
Tom Ogilvy


"Kevin C. McGregor" wrote in message
...

I reposted the data with commas separating the data below...

Date,Contact,Signature,Cash,Check
10/1/2005,Kevin,Gabe Kent,5,,
10/1/2005,Kevin,Vernon Lukehart,,10
10/2/2005,Kevin,Don Curry,11,,
10/2/2005,George,Wendy Bryant,,36
10/3/2005,George,Dorothy Miller,,20
10/4/2005,Bart,Jeanette Fox,5,,
10/4/2005,Bart,Stacy Taiber,10,,
10/4/2005,Bart,Jill Norton,11,,
10/5/2005,Bart,Judith Jessup,36,,
10/5/2005,Rubben,Ron Paine,,15
10/5/2005,Rubben,Kathy Weinberg,5,,
10/5/2005,Rubben,Amy Lau,10,,
10/6/2005,Craig,Marissa Smith,,36
10/6/2005,Craig,Marlin Weber,,20
10/6/2005,Craig,John Smith,15,,








--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Here is the scenario I am dealing with:
I have a group of salespeople whose daily numbers need to be managed.
I have a spreadsheet that I enter the pertinent customer contact data into
date,rep,customer,amt,address,city,state,zip (actually about twenty fields)
if I use the following formula:

=SUMIF(DATE,A1,AMT) I am able to sort total daily numbers, but not numbers
at the REP level.

Is it possible to somehow sort as the above example illustrates, but only
pulliing records for specific reps? I am very new at Excel programming, so
I don't feel comfortable trying to design a form via VBA or otherwise using
macro functionality. I want to be able to use the functions that are built
into the spreadsheet, i.e., SUMIF, IF, DSUM, those sorts of tools, if
possible.

I have tried to use the following:

=IF(AND(REP=A2,DATE=A1),SUMIF(DATE,A1,AMT,))

but that doesn't work logically, because it simply does the same thing as
the first example above -- is there a logical way to attack this problem? I
mean it isn't LOGICAL to want a flat datafile to behave like a relational
datafile, but I feel like I should be able to trick EXCEL into doing
something like this. Will the SUMIF function accept IF types of arguments
as criteria?

For instance, shouldn't I be able to use some sort of formula in the
criteria of the SUMIF function, the help file says that this is possible, so
my assumption is that I am simply not looking at this the right way.

Lastly, what if I were able to use some sort of FOR LOOP

FOR EACH REP
DO
....

Thanks for any help beforehand.
"Debra Dalgleish" wrote in message
...
If the pivot table source data will change size frequently, you can use
a dynamic range as the pivot table source, and it will automatically
expand as rows or columns are added. There are instructions he

http://www.contextures.com/xlPivot01.html

Kevin C. McGregor wrote:
If I use Pivot Table won't I have to adjust the Pivot for each change?
What
I want to be able to do is add to my data range daily and have the
spreadsheet caculate accordingly. Should I assume, then, that the DSUM
function isn't really appropriate for what I'm attempting to do -- i.e.,
take data entered:
date1
date2
date3
date4
and then manipulate it accordingly?

"Tom Ogilvy" wrote in message
...

Why not just use a pivot Table?

If you wanted to use DSum, you would need a separate criteria range for

each

unique date.

--
Regards,
Tom Ogilvy


"Kevin C. McGregor" wrote in message
...

I reposted the data with commas separating the data below...

Date,Contact,Signature,Cash,Check
10/1/2005,Kevin,Gabe Kent,5,,
10/1/2005,Kevin,Vernon Lukehart,,10
10/2/2005,Kevin,Don Curry,11,,
10/2/2005,George,Wendy Bryant,,36
10/3/2005,George,Dorothy Miller,,20
10/4/2005,Bart,Jeanette Fox,5,,
10/4/2005,Bart,Stacy Taiber,10,,
10/4/2005,Bart,Jill Norton,11,,
10/5/2005,Bart,Judith Jessup,36,,
10/5/2005,Rubben,Ron Paine,,15
10/5/2005,Rubben,Kathy Weinberg,5,,
10/5/2005,Rubben,Amy Lau,10,,
10/6/2005,Craig,Marissa Smith,,36
10/6/2005,Craig,Marlin Weber,,20
10/6/2005,Craig,John Smith,15,,








--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

You can use the SUMPRODUCT function, e.g.:

=SUMPRODUCT(--(DATE=A2),--(REP=A1),--(AMT))

Or, set up a pivot table, with Rep in the page area. Then, select a Rep
from the dropdown list in the page field, and the pivot table will show
only the data for that Rep.

Kevin wrote:
Here is the scenario I am dealing with:
I have a group of salespeople whose daily numbers need to be managed.
I have a spreadsheet that I enter the pertinent customer contact data into
date,rep,customer,amt,address,city,state,zip (actually about twenty fields)
if I use the following formula:

=SUMIF(DATE,A1,AMT) I am able to sort total daily numbers, but not numbers
at the REP level.

Is it possible to somehow sort as the above example illustrates, but only
pulliing records for specific reps? I am very new at Excel programming, so
I don't feel comfortable trying to design a form via VBA or otherwise using
macro functionality. I want to be able to use the functions that are built
into the spreadsheet, i.e., SUMIF, IF, DSUM, those sorts of tools, if
possible.

I have tried to use the following:

=IF(AND(REP=A2,DATE=A1),SUMIF(DATE,A1,AMT,))

but that doesn't work logically, because it simply does the same thing as
the first example above -- is there a logical way to attack this problem? I
mean it isn't LOGICAL to want a flat datafile to behave like a relational
datafile, but I feel like I should be able to trick EXCEL into doing
something like this. Will the SUMIF function accept IF types of arguments
as criteria?

For instance, shouldn't I be able to use some sort of formula in the
criteria of the SUMIF function, the help file says that this is possible, so
my assumption is that I am simply not looking at this the right way.

Lastly, what if I were able to use some sort of FOR LOOP

FOR EACH REP
DO
...

Thanks for any help beforehand.
"Debra Dalgleish" wrote in message
...

If the pivot table source data will change size frequently, you can use
a dynamic range as the pivot table source, and it will automatically
expand as rows or columns are added. There are instructions he

http://www.contextures.com/xlPivot01.html

Kevin C. McGregor wrote:

If I use Pivot Table won't I have to adjust the Pivot for each change?

What

I want to be able to do is add to my data range daily and have the
spreadsheet caculate accordingly. Should I assume, then, that the DSUM
function isn't really appropriate for what I'm attempting to do -- i.e.,
take data entered:
date1
date2
date3
date4
and then manipulate it accordingly?

"Tom Ogilvy" wrote in message
...


Why not just use a pivot Table?

If you wanted to use DSum, you would need a separate criteria range for

each


unique date.

--
Regards,
Tom Ogilvy


"Kevin C. McGregor" wrote in message
.. .


I reposted the data with commas separating the data below...

Date,Contact,Signature,Cash,Check
10/1/2005,Kevin,Gabe Kent,5,,
10/1/2005,Kevin,Vernon Lukehart,,10
10/2/2005,Kevin,Don Curry,11,,
10/2/2005,George,Wendy Bryant,,36
10/3/2005,George,Dorothy Miller,,20
10/4/2005,Bart,Jeanette Fox,5,,
10/4/2005,Bart,Stacy Taiber,10,,
10/4/2005,Bart,Jill Norton,11,,
10/5/2005,Bart,Judith Jessup,36,,
10/5/2005,Rubben,Ron Paine,,15
10/5/2005,Rubben,Kathy Weinberg,5,,
10/5/2005,Rubben,Amy Lau,10,,
10/6/2005,Craig,Marissa Smith,,36
10/6/2005,Craig,Marlin Weber,,20
10/6/2005,Craig,John Smith,15,,






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Hey guys, it looks like I found a solution using SUMPRODUCT whe

=SUMPRODUCT(--(REP=B8),--(DATE=A8),DATARANGE)

thanks to all (I seen some advice that Aladin gave to someone else -- thank
you my friend!)

"Kevin" wrote in message
...
Here is the scenario I am dealing with:
I have a group of salespeople whose daily numbers need to be managed.
I have a spreadsheet that I enter the pertinent customer contact data into
date,rep,customer,amt,address,city,state,zip (actually about twenty
fields)
if I use the following formula:

=SUMIF(DATE,A1,AMT) I am able to sort total daily numbers, but not numbers
at the REP level.

Is it possible to somehow sort as the above example illustrates, but only
pulliing records for specific reps? I am very new at Excel programming,
so
I don't feel comfortable trying to design a form via VBA or otherwise
using
macro functionality. I want to be able to use the functions that are
built
into the spreadsheet, i.e., SUMIF, IF, DSUM, those sorts of tools, if
possible.

I have tried to use the following:

=IF(AND(REP=A2,DATE=A1),SUMIF(DATE,A1,AMT,))

but that doesn't work logically, because it simply does the same thing as
the first example above -- is there a logical way to attack this problem?
I
mean it isn't LOGICAL to want a flat datafile to behave like a relational
datafile, but I feel like I should be able to trick EXCEL into doing
something like this. Will the SUMIF function accept IF types of arguments
as criteria?

For instance, shouldn't I be able to use some sort of formula in the
criteria of the SUMIF function, the help file says that this is possible,
so
my assumption is that I am simply not looking at this the right way.

Lastly, what if I were able to use some sort of FOR LOOP

FOR EACH REP
DO
...

Thanks for any help beforehand.
"Debra Dalgleish" wrote in message
...
If the pivot table source data will change size frequently, you can use
a dynamic range as the pivot table source, and it will automatically
expand as rows or columns are added. There are instructions he

http://www.contextures.com/xlPivot01.html

Kevin C. McGregor wrote:
If I use Pivot Table won't I have to adjust the Pivot for each change?
What
I want to be able to do is add to my data range daily and have the
spreadsheet caculate accordingly. Should I assume, then, that the
DSUM
function isn't really appropriate for what I'm attempting to do --
i.e.,
take data entered:
date1
date2
date3
date4
and then manipulate it accordingly?

"Tom Ogilvy" wrote in message
...

Why not just use a pivot Table?

If you wanted to use DSum, you would need a separate criteria range
for

each

unique date.

--
Regards,
Tom Ogilvy


"Kevin C. McGregor" wrote in message
...

I reposted the data with commas separating the data below...

Date,Contact,Signature,Cash,Check
10/1/2005,Kevin,Gabe Kent,5,,
10/1/2005,Kevin,Vernon Lukehart,,10
10/2/2005,Kevin,Don Curry,11,,
10/2/2005,George,Wendy Bryant,,36
10/3/2005,George,Dorothy Miller,,20
10/4/2005,Bart,Jeanette Fox,5,,
10/4/2005,Bart,Stacy Taiber,10,,
10/4/2005,Bart,Jill Norton,11,,
10/5/2005,Bart,Judith Jessup,36,,
10/5/2005,Rubben,Ron Paine,,15
10/5/2005,Rubben,Kathy Weinberg,5,,
10/5/2005,Rubben,Amy Lau,10,,
10/6/2005,Craig,Marissa Smith,,36
10/6/2005,Craig,Marlin Weber,,20
10/6/2005,Craig,John Smith,15,,








--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Actually, it sounds like you should use a Pivot Table.

While Debra didn't suggest it, take a look at her site

http://www.contextures.com/tiptech.html

See P for Pivot Table.

--
Regards,
Tom Ogilvy


"Kevin" wrote in message
...
Here is the scenario I am dealing with:
I have a group of salespeople whose daily numbers need to be managed.
I have a spreadsheet that I enter the pertinent customer contact data into
date,rep,customer,amt,address,city,state,zip (actually about twenty
fields)
if I use the following formula:

=SUMIF(DATE,A1,AMT) I am able to sort total daily numbers, but not numbers
at the REP level.

Is it possible to somehow sort as the above example illustrates, but only
pulliing records for specific reps? I am very new at Excel programming,
so
I don't feel comfortable trying to design a form via VBA or otherwise
using
macro functionality. I want to be able to use the functions that are
built
into the spreadsheet, i.e., SUMIF, IF, DSUM, those sorts of tools, if
possible.

I have tried to use the following:

=IF(AND(REP=A2,DATE=A1),SUMIF(DATE,A1,AMT,))

but that doesn't work logically, because it simply does the same thing as
the first example above -- is there a logical way to attack this problem?
I
mean it isn't LOGICAL to want a flat datafile to behave like a relational
datafile, but I feel like I should be able to trick EXCEL into doing
something like this. Will the SUMIF function accept IF types of arguments
as criteria?

For instance, shouldn't I be able to use some sort of formula in the
criteria of the SUMIF function, the help file says that this is possible,
so
my assumption is that I am simply not looking at this the right way.

Lastly, what if I were able to use some sort of FOR LOOP

FOR EACH REP
DO
...

Thanks for any help beforehand.
"Debra Dalgleish" wrote in message
...
If the pivot table source data will change size frequently, you can use
a dynamic range as the pivot table source, and it will automatically
expand as rows or columns are added. There are instructions he

http://www.contextures.com/xlPivot01.html

Kevin C. McGregor wrote:
If I use Pivot Table won't I have to adjust the Pivot for each change?
What
I want to be able to do is add to my data range daily and have the
spreadsheet caculate accordingly. Should I assume, then, that the
DSUM
function isn't really appropriate for what I'm attempting to do --
i.e.,
take data entered:
date1
date2
date3
date4
and then manipulate it accordingly?

"Tom Ogilvy" wrote in message
...

Why not just use a pivot Table?

If you wanted to use DSum, you would need a separate criteria range
for

each

unique date.

--
Regards,
Tom Ogilvy


"Kevin C. McGregor" wrote in message
...

I reposted the data with commas separating the data below...

Date,Contact,Signature,Cash,Check
10/1/2005,Kevin,Gabe Kent,5,,
10/1/2005,Kevin,Vernon Lukehart,,10
10/2/2005,Kevin,Don Curry,11,,
10/2/2005,George,Wendy Bryant,,36
10/3/2005,George,Dorothy Miller,,20
10/4/2005,Bart,Jeanette Fox,5,,
10/4/2005,Bart,Stacy Taiber,10,,
10/4/2005,Bart,Jill Norton,11,,
10/5/2005,Bart,Judith Jessup,36,,
10/5/2005,Rubben,Ron Paine,,15
10/5/2005,Rubben,Kathy Weinberg,5,,
10/5/2005,Rubben,Amy Lau,10,,
10/6/2005,Craig,Marissa Smith,,36
10/6/2005,Craig,Marlin Weber,,20
10/6/2005,Craig,John Smith,15,,








--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra, thanks for the input... this is the solution that I am going to go
for -- you guys are great!

"Debra Dalgleish" wrote in message
...
You can use the SUMPRODUCT function, e.g.:

=SUMPRODUCT(--(DATE=A2),--(REP=A1),--(AMT))

Or, set up a pivot table, with Rep in the page area. Then, select a Rep
from the dropdown list in the page field, and the pivot table will show
only the data for that Rep.

Kevin wrote:
Here is the scenario I am dealing with:
I have a group of salespeople whose daily numbers need to be managed.
I have a spreadsheet that I enter the pertinent customer contact data
into
date,rep,customer,amt,address,city,state,zip (actually about twenty
fields)
if I use the following formula:

=SUMIF(DATE,A1,AMT) I am able to sort total daily numbers, but not
numbers
at the REP level.

Is it possible to somehow sort as the above example illustrates, but
only
pulliing records for specific reps? I am very new at Excel programming,
so
I don't feel comfortable trying to design a form via VBA or otherwise
using
macro functionality. I want to be able to use the functions that are
built
into the spreadsheet, i.e., SUMIF, IF, DSUM, those sorts of tools, if
possible.

I have tried to use the following:

=IF(AND(REP=A2,DATE=A1),SUMIF(DATE,A1,AMT,))

but that doesn't work logically, because it simply does the same thing
as
the first example above -- is there a logical way to attack this
problem? I
mean it isn't LOGICAL to want a flat datafile to behave like a
relational
datafile, but I feel like I should be able to trick EXCEL into doing
something like this. Will the SUMIF function accept IF types of
arguments
as criteria?

For instance, shouldn't I be able to use some sort of formula in the
criteria of the SUMIF function, the help file says that this is
possible, so
my assumption is that I am simply not looking at this the right way.

Lastly, what if I were able to use some sort of FOR LOOP

FOR EACH REP
DO
...

Thanks for any help beforehand.
"Debra Dalgleish" wrote in message
...

If the pivot table source data will change size frequently, you can use
a dynamic range as the pivot table source, and it will automatically
expand as rows or columns are added. There are instructions he

http://www.contextures.com/xlPivot01.html

Kevin C. McGregor wrote:

If I use Pivot Table won't I have to adjust the Pivot for each change?

What

I want to be able to do is add to my data range daily and have the
spreadsheet caculate accordingly. Should I assume, then, that the DSUM
function isn't really appropriate for what I'm attempting to do --
i.e.,
take data entered:
date1
date2
date3
date4
and then manipulate it accordingly?

"Tom Ogilvy" wrote in message
...


Why not just use a pivot Table?

If you wanted to use DSum, you would need a separate criteria range
for

each


unique date.

--
Regards,
Tom Ogilvy


"Kevin C. McGregor" wrote in message
.. .


I reposted the data with commas separating the data below...

Date,Contact,Signature,Cash,Check
10/1/2005,Kevin,Gabe Kent,5,,
10/1/2005,Kevin,Vernon Lukehart,,10
10/2/2005,Kevin,Don Curry,11,,
10/2/2005,George,Wendy Bryant,,36
10/3/2005,George,Dorothy Miller,,20
10/4/2005,Bart,Jeanette Fox,5,,
10/4/2005,Bart,Stacy Taiber,10,,
10/4/2005,Bart,Jill Norton,11,,
10/5/2005,Bart,Judith Jessup,36,,
10/5/2005,Rubben,Ron Paine,,15
10/5/2005,Rubben,Kathy Weinberg,5,,
10/5/2005,Rubben,Amy Lau,10,,
10/6/2005,Craig,Marissa Smith,,36
10/6/2005,Craig,Marlin Weber,,20
10/6/2005,Craig,John Smith,15,,






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Tom, thanks for the input. The problem that I have is I'm a very new user
to EXCEL and have trouble using some of the more advanced functions, such as
pivot tables. I think I'm going to go with the solution that Debra offered
as it is doing the exact thing I wanted -- filtering and summing my records
for the two conditions DATE and REP. Thanks again, you guys are fantastic!

"Tom Ogilvy" wrote in message
...
Actually, it sounds like you should use a Pivot Table.

While Debra didn't suggest it, take a look at her site

http://www.contextures.com/tiptech.html

See P for Pivot Table.

--
Regards,
Tom Ogilvy


"Kevin" wrote in message
...
Here is the scenario I am dealing with:
I have a group of salespeople whose daily numbers need to be managed.
I have a spreadsheet that I enter the pertinent customer contact data
into
date,rep,customer,amt,address,city,state,zip (actually about twenty
fields)
if I use the following formula:

=SUMIF(DATE,A1,AMT) I am able to sort total daily numbers, but not
numbers
at the REP level.

Is it possible to somehow sort as the above example illustrates, but
only
pulliing records for specific reps? I am very new at Excel programming,
so
I don't feel comfortable trying to design a form via VBA or otherwise
using
macro functionality. I want to be able to use the functions that are
built
into the spreadsheet, i.e., SUMIF, IF, DSUM, those sorts of tools, if
possible.

I have tried to use the following:

=IF(AND(REP=A2,DATE=A1),SUMIF(DATE,A1,AMT,))

but that doesn't work logically, because it simply does the same thing
as
the first example above -- is there a logical way to attack this
problem?
I
mean it isn't LOGICAL to want a flat datafile to behave like a
relational
datafile, but I feel like I should be able to trick EXCEL into doing
something like this. Will the SUMIF function accept IF types of
arguments
as criteria?

For instance, shouldn't I be able to use some sort of formula in the
criteria of the SUMIF function, the help file says that this is
possible,
so
my assumption is that I am simply not looking at this the right way.

Lastly, what if I were able to use some sort of FOR LOOP

FOR EACH REP
DO
...

Thanks for any help beforehand.
"Debra Dalgleish" wrote in message
...
If the pivot table source data will change size frequently, you can
use
a dynamic range as the pivot table source, and it will automatically
expand as rows or columns are added. There are instructions he

http://www.contextures.com/xlPivot01.html

Kevin C. McGregor wrote:
If I use Pivot Table won't I have to adjust the Pivot for each
change?
What
I want to be able to do is add to my data range daily and have the
spreadsheet caculate accordingly. Should I assume, then, that the
DSUM
function isn't really appropriate for what I'm attempting to do --
i.e.,
take data entered:
date1
date2
date3
date4
and then manipulate it accordingly?

"Tom Ogilvy" wrote in message
...

Why not just use a pivot Table?

If you wanted to use DSum, you would need a separate criteria range
for

each

unique date.

--
Regards,
Tom Ogilvy


"Kevin C. McGregor" wrote in message
...

I reposted the data with commas separating the data below...

Date,Contact,Signature,Cash,Check
10/1/2005,Kevin,Gabe Kent,5,,
10/1/2005,Kevin,Vernon Lukehart,,10
10/2/2005,Kevin,Don Curry,11,,
10/2/2005,George,Wendy Bryant,,36
10/3/2005,George,Dorothy Miller,,20
10/4/2005,Bart,Jeanette Fox,5,,
10/4/2005,Bart,Stacy Taiber,10,,
10/4/2005,Bart,Jill Norton,11,,
10/5/2005,Bart,Judith Jessup,36,,
10/5/2005,Rubben,Ron Paine,,15
10/5/2005,Rubben,Kathy Weinberg,5,,
10/5/2005,Rubben,Amy Lau,10,,
10/6/2005,Craig,Marissa Smith,,36
10/6/2005,Craig,Marlin Weber,,20
10/6/2005,Craig,John Smith,15,,








--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

If our Mom's used that approach, we'd still be wearing diapers, but maybe
that is all you will ever need <g

By the way, that isn't filtering (or sorting as others say). those
functions (filtering and sorting) are also found under the Data menu. If
your working with data - that's why they labeled it Data.

--
Regards,
Tom Ogilvy

"Kevin" wrote in message
...
Tom, thanks for the input. The problem that I have is I'm a very new user
to EXCEL and have trouble using some of the more advanced functions, such
as
pivot tables. I think I'm going to go with the solution that Debra
offered
as it is doing the exact thing I wanted -- filtering and summing my
records
for the two conditions DATE and REP. Thanks again, you guys are fantastic!

"Tom Ogilvy" wrote in message
...
Actually, it sounds like you should use a Pivot Table.

While Debra didn't suggest it, take a look at her site

http://www.contextures.com/tiptech.html

See P for Pivot Table.

--
Regards,
Tom Ogilvy


"Kevin" wrote in message
...
Here is the scenario I am dealing with:
I have a group of salespeople whose daily numbers need to be managed.
I have a spreadsheet that I enter the pertinent customer contact data
into
date,rep,customer,amt,address,city,state,zip (actually about twenty
fields)
if I use the following formula:

=SUMIF(DATE,A1,AMT) I am able to sort total daily numbers, but not
numbers
at the REP level.

Is it possible to somehow sort as the above example illustrates, but
only
pulliing records for specific reps? I am very new at Excel
programming,
so
I don't feel comfortable trying to design a form via VBA or otherwise
using
macro functionality. I want to be able to use the functions that are
built
into the spreadsheet, i.e., SUMIF, IF, DSUM, those sorts of tools, if
possible.

I have tried to use the following:

=IF(AND(REP=A2,DATE=A1),SUMIF(DATE,A1,AMT,))

but that doesn't work logically, because it simply does the same thing
as
the first example above -- is there a logical way to attack this
problem?
I
mean it isn't LOGICAL to want a flat datafile to behave like a
relational
datafile, but I feel like I should be able to trick EXCEL into doing
something like this. Will the SUMIF function accept IF types of
arguments
as criteria?

For instance, shouldn't I be able to use some sort of formula in the
criteria of the SUMIF function, the help file says that this is
possible,
so
my assumption is that I am simply not looking at this the right way.

Lastly, what if I were able to use some sort of FOR LOOP

FOR EACH REP
DO
...

Thanks for any help beforehand.
"Debra Dalgleish" wrote in message
...
If the pivot table source data will change size frequently, you can
use
a dynamic range as the pivot table source, and it will automatically
expand as rows or columns are added. There are instructions he

http://www.contextures.com/xlPivot01.html

Kevin C. McGregor wrote:
If I use Pivot Table won't I have to adjust the Pivot for each
change?
What
I want to be able to do is add to my data range daily and have the
spreadsheet caculate accordingly. Should I assume, then, that the
DSUM
function isn't really appropriate for what I'm attempting to do --
i.e.,
take data entered:
date1
date2
date3
date4
and then manipulate it accordingly?

"Tom Ogilvy" wrote in message
...

Why not just use a pivot Table?

If you wanted to use DSum, you would need a separate criteria
range
for

each

unique date.

--
Regards,
Tom Ogilvy


"Kevin C. McGregor" wrote in message
...

I reposted the data with commas separating the data below...

Date,Contact,Signature,Cash,Check
10/1/2005,Kevin,Gabe Kent,5,,
10/1/2005,Kevin,Vernon Lukehart,,10
10/2/2005,Kevin,Don Curry,11,,
10/2/2005,George,Wendy Bryant,,36
10/3/2005,George,Dorothy Miller,,20
10/4/2005,Bart,Jeanette Fox,5,,
10/4/2005,Bart,Stacy Taiber,10,,
10/4/2005,Bart,Jill Norton,11,,
10/5/2005,Bart,Judith Jessup,36,,
10/5/2005,Rubben,Ron Paine,,15
10/5/2005,Rubben,Kathy Weinberg,5,,
10/5/2005,Rubben,Amy Lau,10,,
10/6/2005,Craig,Marissa Smith,,36
10/6/2005,Craig,Marlin Weber,,20
10/6/2005,Craig,John Smith,15,,








--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

that's cold man... why you wanna talk about my ****ty diapers! (lol)
"Tom Ogilvy" wrote in message
...
If our Mom's used that approach, we'd still be wearing diapers, but maybe
that is all you will ever need <g

By the way, that isn't filtering (or sorting as others say). those
functions (filtering and sorting) are also found under the Data menu. If
your working with data - that's why they labeled it Data.

--
Regards,
Tom Ogilvy

"Kevin" wrote in message
...
Tom, thanks for the input. The problem that I have is I'm a very new
user
to EXCEL and have trouble using some of the more advanced functions,
such
as
pivot tables. I think I'm going to go with the solution that Debra
offered
as it is doing the exact thing I wanted -- filtering and summing my
records
for the two conditions DATE and REP. Thanks again, you guys are
fantastic!

"Tom Ogilvy" wrote in message
...
Actually, it sounds like you should use a Pivot Table.

While Debra didn't suggest it, take a look at her site

http://www.contextures.com/tiptech.html

See P for Pivot Table.

--
Regards,
Tom Ogilvy


"Kevin" wrote in message
...
Here is the scenario I am dealing with:
I have a group of salespeople whose daily numbers need to be
managed.
I have a spreadsheet that I enter the pertinent customer contact
data
into
date,rep,customer,amt,address,city,state,zip (actually about twenty
fields)
if I use the following formula:

=SUMIF(DATE,A1,AMT) I am able to sort total daily numbers, but not
numbers
at the REP level.

Is it possible to somehow sort as the above example illustrates, but
only
pulliing records for specific reps? I am very new at Excel
programming,
so
I don't feel comfortable trying to design a form via VBA or
otherwise
using
macro functionality. I want to be able to use the functions that
are
built
into the spreadsheet, i.e., SUMIF, IF, DSUM, those sorts of tools,
if
possible.

I have tried to use the following:

=IF(AND(REP=A2,DATE=A1),SUMIF(DATE,A1,AMT,))

but that doesn't work logically, because it simply does the same
thing
as
the first example above -- is there a logical way to attack this
problem?
I
mean it isn't LOGICAL to want a flat datafile to behave like a
relational
datafile, but I feel like I should be able to trick EXCEL into doing
something like this. Will the SUMIF function accept IF types of
arguments
as criteria?

For instance, shouldn't I be able to use some sort of formula in the
criteria of the SUMIF function, the help file says that this is
possible,
so
my assumption is that I am simply not looking at this the right way.

Lastly, what if I were able to use some sort of FOR LOOP

FOR EACH REP
DO
...

Thanks for any help beforehand.
"Debra Dalgleish" wrote in message
...
If the pivot table source data will change size frequently, you
can
use
a dynamic range as the pivot table source, and it will
automatically
expand as rows or columns are added. There are instructions he

http://www.contextures.com/xlPivot01.html

Kevin C. McGregor wrote:
If I use Pivot Table won't I have to adjust the Pivot for each
change?
What
I want to be able to do is add to my data range daily and have
the
spreadsheet caculate accordingly. Should I assume, then, that
the
DSUM
function isn't really appropriate for what I'm attempting to
do --
i.e.,
take data entered:
date1
date2
date3
date4
and then manipulate it accordingly?

"Tom Ogilvy" wrote in message
...

Why not just use a pivot Table?

If you wanted to use DSum, you would need a separate criteria
range
for

each

unique date.

--
Regards,
Tom Ogilvy


"Kevin C. McGregor" wrote in message
...

I reposted the data with commas separating the data below...

Date,Contact,Signature,Cash,Check
10/1/2005,Kevin,Gabe Kent,5,,
10/1/2005,Kevin,Vernon Lukehart,,10
10/2/2005,Kevin,Don Curry,11,,
10/2/2005,George,Wendy Bryant,,36
10/3/2005,George,Dorothy Miller,,20
10/4/2005,Bart,Jeanette Fox,5,,
10/4/2005,Bart,Stacy Taiber,10,,
10/4/2005,Bart,Jill Norton,11,,
10/5/2005,Bart,Judith Jessup,36,,
10/5/2005,Rubben,Ron Paine,,15
10/5/2005,Rubben,Kathy Weinberg,5,,
10/5/2005,Rubben,Amy Lau,10,,
10/6/2005,Craig,Marissa Smith,,36
10/6/2005,Craig,Marlin Weber,,20
10/6/2005,Craig,John Smith,15,,








--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html