Results 1 to 4 of 4

Thread: Lookup & countif

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. 03-25-2012, 11:16 PM #3
    Rick Rothstein
    Rick Rothstein is offline
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    14
    Quote Originally Posted by Admin View Post
    Hi Babu,

    Please find attached.
    The formula you used in your attachment was this...

    =SUMPRODUCT(--($B$2:$B$25>=E2),--($B$2:$B$25<F2))

    An alternate formula that will produce the same results is this...

    =COUNTIF(B$2:B$25,"<"&F2)-COUNTIF(B$2:B$25,"<"&E2)

    I am not sure which of these two formulas would be more efficient. In my experience, the SUMPRODUCT function tends to be slow because, underneath it all, it is actually an array function that does not require using CTRL+SHIFT+ENTER to enable the array; however, making two COUNTIF function calls against the same data set might also be slow... I am just not sure.
    Last edited by Rick Rothstein; 03-25-2012 at 11:48 PM.
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Similar Threads

  1. Match and CountIF Across Multiple Sheets Using Formula
    By khateeb in forum Excel Help
    Replies: 3
    Last Post: 03-12-2013, 12:54 PM
  2. Integration Formulas & International Options
    By PcMax in forum Excel Help
    Replies: 5
    Last Post: 01-04-2013, 01:54 AM
  3. Lookup lookup lookup just can't make it work
    By work2live in forum Excel Help
    Replies: 1
    Last Post: 12-08-2012, 11:48 PM
  4. Delete unwanted rows & column
    By sanjeevi888 in forum Excel Help
    Replies: 1
    Last Post: 09-30-2012, 08:52 AM
  5. COUNTIF on Non-contiguous Range
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 04-23-2011, 04:05 PM

Bookmarks

Posting Permissions

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

Forum Rules