+ Reply to Thread
Results 1 to 2 of 2

Return Single Value from Mutliple Criteria

  1. #1
    Registered User
    Join Date
    01-31-2010
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2003
    Posts
    1

    Return Single Value from Mutliple Criteria

    Hi

    I need to lookup and reference 3 separate criteria, then return a single value based on that criteria. I have been using a Sumproduct formula , =SUMPRODUCT(--('Data 2007-Current'!$AD$2:$AD$1002=$B21),--('Data 2007-Current'!$AH$2:$AH$1002=C$5),--('Data 2007-Current'!$AI$2:$AI$1002=$B$5)), but it is very very slow to calculate, so I am seeking a better option.

    The criteria is unchanging and will be the same based on the data I'm extracting:-

    NVT Consulftant (A Persons name)
    Month year (MMM-YY) - A date
    Category (the categoy of work)

    The source data for the lookup is contained within yearly tables and the layout/design of each table is identical. There are 4 source data tables altogether, 2007,2008,2009,2010.
    The value found is then going into a separate Data sheet as Chart Data. Can anyone help?
    Thanks

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Return Single Value from Mutliple Criteria

    The single value being returned by your existing formula is a Count of the times the combination exists in the specified ranges, correct ?

    Most multi conditional tests prior to XL2007 require the use of Arrays / Sumproduct unless you adopt use of helper cells so as to revert to singular condition testing, eg:

    Please Login or Register  to view this content.
    Your existing SUMPRODUCT can thus revert to a far (far) more efficient COUNTIF, eg:

    Please Login or Register  to view this content.
    (obviously the above can be replicated on each annual sheet)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1