Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: SUMIF based on two criterias

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is it possible to do a SUMIF formula based on two criterias? Basically what I have is a sheet with three columns - ID (Column A), Date (Column B) and Amount (Column C)

    What I need to find is how much a certain user took on a certain day, so basically add together all the amount entries taken by ID number 2222 on the 28/04/02. Is this possible?

    Janie

    xx

    [ This Message was edited by: buntykins on 2002-04-29 07:29 ]

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-29 07:28, buntykins wrote:
    Is it possible to do a SUMIF formula based on two criterias? Basically what I have is a sheet with three columns - ID (Column A), Date (Column B) and Amount (Column C)

    What I need to find is how much a certain user took on a certain day, so basically add together all the amount entries taken by ID number 2222 on the 28/04/02. Is this possible?

    Janie

    xx

    [ This Message was edited by: buntykins on 2002-04-29 07:29 ]
    =SUMPRODUCT((A1:A100=2222)*(B1:B100=Date(2002,4,28))*(C1:C100))

    should work for you. Adjust your ranges as necessary.

    Bye,
    Jay

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Sort of works, except that the place that the formula is going is a separate sheet and because of this it doesnt seem to work.

    Any idea on how I could fix this?

    [ This Message was edited by: buntykins on 2002-04-29 07:51 ]

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Maybe I should have mentioned that the things that the SUMPRODUCT is looking for are values in cells. For instance, my formula at the moment looks like this:

    =SUMPRODUCT((B1:B60000=X2)*(J1:J60000=DATE(AA2,Z2,Y2))*(E1:E60000))

    Help!!!

  5. #5
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How are the dates formatted? Can you break it down into smaller bits that you can post? Your formula should be working.

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    The date is formatted like this: 28/04/02 but I have an =Date and =Month and an =Day formula to break the date down into three cells so that I have 3 separate parts for the SUMPRODUCT formula

    Weird!


  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-04-29 08:26, buntykins wrote:

    The date is formatted like this: 28/04/02 but I have an =Date and =Month and an =Day formula to break the date down into three cells so that I have 3 separate parts for the SUMPRODUCT formula

    Weird!
    Does it work now? In fact you shouldn't to break up the criterion date.

    Is the actual, used range really 60000 big? If not, I think you should be better off using dynamic name ranges.

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    You could use a frinkin' pivot table I guess?

    Audiojoe

  9. #9
    Board Regular
    Join Date
    Apr 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Of course!!!! Nice one Joe, here I am fiddling with formulas when I could just display it there all the time!!

    Thanks baby

    xxxxxx

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •