Re: using DSUM in formulae instead of SUMIF

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

Date: Sun, 13 Feb 2005 10:31:30 -0600

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!)

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

Quantcast