Re: using DSUM in formulae instead of SUMIF

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

Date: Sun, 13 Feb 2005 12:24:30 -0600

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" <twogilvy@msn.com> wrote in message
news:eA6XCDfEFHA.3972@TK2MSFTNGP15.phx.gbl...
> 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" <mcgregorkc@hotmail.com> wrote in message
> news:OhqWZBeEFHA.1408@TK2MSFTNGP10.phx.gbl...
> > 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
> > >
> >
> >
>
>

Loading