+ Reply to Thread
Results 1 to 5 of 5

SUMPRODUCT multiple criteria help

  1. 03-09-2016, 10:34 AM #1
    AndyBobby
    AndyBobby is offline
    Registered User
    Join Date
    03-09-2016
    Location
    UK
    MS-Off Ver
    2003
    Posts
    3

    SUMPRODUCT multiple criteria help

    Hi,

    I currently have the formula 
    Please Login or Register  to view this content.
    which works but i need to add another "ISNUMBER(MATCH" due to now needing another criteria.

    Please Login or Register  to view this content.
    references a date range 
    Please Login or Register  to view this content.
    references the building name that repairs have been completed on and 
    Please Login or Register  to view this content.
    references the cost of repair but i now need to add another ISNUMBER(MATCH or its equivalent to reference the type of repair which is kept in column '2015'!I1:I9999.

    Im sorry if i haven't explained it well, if further information is needed i will try to explain as best i can.

    Also I am on excel 2003, i know that with later versions the SUMIFS function would completely solve my problem.
    Reply With QuoteRegister To Reply
  2. 03-09-2016, 10:50 AM #2
    AndyBobby
    AndyBobby is offline
    Registered User
    Join Date
    03-09-2016
    Location
    UK
    MS-Off Ver
    2003
    Posts
    3

    Re: SUMPRODUCT multiple criteria help

    formula help.png If this helps
    Reply With QuoteRegister To Reply
  3. 03-09-2016, 12:26 PM #3
    ben_hensel
    ben_hensel is offline
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: SUMPRODUCT multiple criteria help

    Not too hard?

    Please Login or Register  to view this content.
    1) You should refer to B1 for the MATCH, not B1:B1 (not that it makes a difference per se, it's just that the formula needs a cell, and you're giving it a one-cell-long range).

    2) I assumed the second match is feeding off B2, just replace that reference with whatever cell it needs to be.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.
    Reply With QuoteRegister To Reply
  4. 03-09-2016, 12:32 PM #4
    AndyBobby
    AndyBobby is offline
    Registered User
    Join Date
    03-09-2016
    Location
    UK
    MS-Off Ver
    2003
    Posts
    3
    Quote Originally Posted by ben_hensel View Post
    Not too hard?

    Please Login or Register  to view this content.
    1) You should refer to B1 for the MATCH, not B1:B1 (not that it makes a difference per se, it's just that the formula needs a cell, and you're giving it a one-cell-long range).

    2) I assumed the second match is feeding off B2, just replace that reference with whatever cell it needs to be.
    I originally attempted that but to no avail, currently on my phone but will attempt when back at PC, thanks.
    Last edited by AndyBobby; 03-09-2016 at 12:34 PM.
    Reply With QuoteRegister To Reply
  5. 03-09-2016, 12:47 PM #5
    ben_hensel
    ben_hensel is offline
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: SUMPRODUCT multiple criteria help

    What if there are no values in the range that fit the criteria?

    That will return zero from the SUMPRODUCT.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] SUMPRODUCT of multiple columns with multiple criteria over multiple sheets
    By BellyGas in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-16-2015, 08:27 AM
  2. [SOLVED] SUMIFS,INDEX, MATCH, OFFSET COUNTA / SUMPRODUCT? Multiple criteria across multiple sheets.
    By TheKlaw in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-06-2015, 08:44 PM
  3. [SOLVED] Multiple criteria SUMPRODUCT (3 criteria) Excel 2003
    By lelrich in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-10-2014, 01:58 PM
  4. Sumproduct to count number of times multiple criteria are met across multiple spreadsheets
    By Victoria6517 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-21-2014, 09:09 PM
  5. SUMPRODUCT: Multiple criteria in same column and criteria NOT hard coded
    By Asmar in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-04-2013, 11:53 AM
  6. [SOLVED] Sumproduct with Multiple (Header) Criteria - Sumproduct, Index, Match
    By dluhut in forum Excel General
    Replies: 5
    Last Post: 04-20-2012, 08:54 AM
  7. Sumproduct/sumif/indirect across multiple workbooks, worksheets, multiple criteria
    By robgardner15 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-08-2011, 02:35 AM

Tags for this Thread

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


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1