Re: using DSUM in formulae instead of SUMIF

From: Kevin (mcgregorkc_at_hotmail.com)
Date: 02/13/05 

Date: Sun, 13 Feb 2005 09:26:04 -0600

Here is the scenario I am dealing with:
I have a group of salespeople whose daily numbers need to be managed.
I have a spread*** 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 spread***, 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" <dsd@contexturesXSPAM.com> wrote in message
news:42095F5C.5050208@contexturesXSPAM.com...
> 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 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
> > spread*** 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" <twogilvy@msn.com> wrote in message
> > news:eF$ADngDFHA.3728@TK2MSFTNGP14.phx.gbl...
> >
> >>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" <mcgregorkc@hotmail.com> wrote in message
> >>news:OjMhCIgDFHA.4020@TK2MSFTNGP14.phx.gbl...
> >>
> >>>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
>