+ Reply to Thread
Results 1 to 5 of 5

Simplest formula for sumproduct with or conditions

  1. #1
    Forum Contributor
    Join Date
    05-05-2014
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2013
    Posts
    103

    Simplest formula for sumproduct with or conditions

    Hello,

    I am looking for a formula for cell D5 on the YTD tab. It should sum the values in row 16 on the Actual Revenue tab based on the following conditions:
    1)Row 6 on the Actual Revenue tab matches the value in cell D1 on the YTD tab.
    2) Row 5 on the Actual Revenue tab match Cells S1, or T1 or U1 on the YTD tab.

    I am wondering if there is a formula that can sum these with out creating all the individual conditions. Here is my formula that doesn't work:
    =SUMPRODUCT(('Actual Revenue'!E5:AN5=YTD!S1&YTD!T1&YTD!U1&YTD!V1)*('Actual Revenue'!E6:AN6=YTD!D1),'Actual Revenue'!E16:AN16)

    Thanks for your help!

    Laura
    Attached Files Attached Files

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,679

    Re: Simplest formula for sumproduct with or conditions

    If you only want to sum for Q1, Q2 or Q3 try this version

    =SUMPRODUCT(SUMIFS('Actual Revenue'!E16:AN16,'Actual Revenue'!E5:AN5,S1:U1,'Actual Revenue'!E6:AN6,D1))
    Audere est facere

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Simplest formula for sumproduct with or conditions

    I didn't download your file.

    Maybe this...

    =SUMPRODUCT(SUMIFS('Actual Revenue'!E16:AN16,'Actual Revenue'!E5:AN5,YTD!S1:V1,'Actual Revenue'!E6:AN6,YTD!D1))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Forum Contributor
    Join Date
    05-05-2014
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2013
    Posts
    103

    Re: Simplest formula for sumproduct with or conditions

    Thank you all for your time and help!

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Simplest formula for sumproduct with or conditions

    You're welcome. We appreciate the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Excel formula to sumproduct but with multiple conditions
    By Dani8826 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-07-2014, 04:25 AM
  2. [SOLVED] Ratio formula in it's original form (NOT in simplest/lowest form)
    By tlacloche in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-22-2013, 01:08 PM
  3. Squeezing more conditions into a sample SUMPRODUCT formula - show a novice pls!
    By RogerRangeRover in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-01-2012, 06:33 AM
  4. sumproduct with 2 conditions
    By tek9step in forum Excel General
    Replies: 4
    Last Post: 02-16-2011, 09:37 AM
  5. SUMPRODUCT-formula with changing conditions
    By BCB in forum Excel General
    Replies: 2
    Last Post: 02-09-2009, 04:49 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1