+ Reply to Thread
Results 1 to 4 of 4

Excel Formula Help

  1. #1
    Registered User
    Join Date

    Excel Formula Help

    Okay So i'm having a brain freeze at the moment and need some help with a formula in excel.

    I have tabs in a worksheet and have two conditions I want met on the totals page. Basically I want to check if a partner is equal to let's say NBA and the ESP is equal to GPM to add the value of sales colum to my totals cell on the second workbook. I will have this function for a total of about 10 different combination and have them add separately into the totals page.

    I have this right now which it checks the conditions properly but it doesn't add the values into the cell, it just shows 0.

    =IF(AND('Offer Performance'!F:F="NBA",'Offer Performance'!B:B="GPM"), +'Offer Performance'!H:H,0)

    So to recap first condition it checks to see if colum F has "NBA" and Column B has "GPM" and i want to take the value in Colum H from that sheet and add it to a running total.

    Thanks in advance.

  2. #2
    Forum Contributor ptm0412's Avatar
    Join Date
    MS-Off Ver
    Office 2003 and 2007
    It maybe:
    =Sum(IF(AND('Offer Performance'!F:F="NBA",'Offer Performance'!B:B="GPM"), 'Offer Performance'!H:H,0))
    Surely Ctrl+Shift+Enter

  3. #3
    Forum Expert Colin Legg's Avatar
    Join Date
    MS-Off Ver
    2003, 2007, 2010, 2013
    Here's a non-array formula solution which is a touch more efficient:

    =SUMPRODUCT(-('Offer Performance'!B2:B300="GPM"),-('Offer Performance'!F2:F300="NBA"),'Offer Performance'!H2:H300)

    If you are doing lots of combinations then I recommend that you use a PivotTable approach rather than a formula based approach.

    BTW - unless you are using Excel 2007, you cannot use entire columns within an array formula or as a SUMPRODUCT argument.

    Last edited by Colin Legg; 07-15-2008 at 01:17 PM.

  4. #4
    Valued Forum Contributor mudraker's Avatar
    Join Date
    Melbourne, Australia
    eddie barzoon

    Please take a couple of minutes and read the Forum Rules

    In future use a ttle that better reflects the type of assistance you require
    Please Read Forum Rules Before Posting
    Wrap VBA code by selecting the code and clicking the # icon or Read This
    How To Cross Post politely

    Top Excel links for beginners to Experts

    If you are pleased with a member's answer then use the Scales icon to rate it
    If my reply has assisted or failed to assist you I welcome your Feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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


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