using DSUM in formulae instead of SUMIF

[Kevin C. McGregor]

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

 

[Kevin C. McGregor]

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,,

 

[Tom Ogilvy]

Why not just use a pivot Table?

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

 

[Kevin C. McGregor]

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?

 

[Debra Dalgleish]

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 here:

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

 

[Kevin]

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]

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]

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

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

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

 

[Tom Ogilvy]

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.

 

[Kevin]

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

 

[Kevin]

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]

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.

 

[Kevin]

that's cold man... why you wanna talk about my shitty diapers! (lol)