Re: using DSUM in formulae instead of SUMIF

From: Debra Dalgleish (dsd_at_contexturesXSPAM.com)
Date: 02/13/05 

Date: Sun, 13 Feb 2005 11:03:24 -0500

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

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