Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: SUMPRODUCT() question

  1. #11
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I agree! It most certainly is


  2. #12
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    *sigh*

    yup, it's very probable I'll be using 250 criteria ummmmm vertically

    ie if one of my criteria labels is "Project" there will be 200+ of them listed downwards

    I had been using a VLOOKUP to check them, but it meant 25 minutes calculation time and helped swell the filesize to over 14megs...

    Dave's DSUM and DCOUNT funtions look like making my tax checking life much easier


  3. #13
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi John and Dave, ... :
    I am going to pick up here where Dave suggested using the Database functions in Excel ... couple of things, we can use the DCOUNT function and we don't have to do use the sum function at all, and 2) we don't need two crireria, we can have the two conditions to be fulfilled in a single criterion. So, with

    COL A = {01;06;07;01;08;03;05}
    COL B = {01;11;12;01;01;01;03}

    so if my data is in cells A18:B25, and my criterion is in cells E18:F20 (head1>5, or head2>10), then my formula to get the count to satisfy the criterion is:

    =DCOUNT(A18:B25,1,E18:E20) ... resulting in 3
    or
    =DCOUNT(A18:B25,2,E18:E20) ... resulting in 3

    Well, what do you think guys ... I didn't miss anything here did I? please do post your comments back!




    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

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
  •