+ Reply to Thread
Results 1 to 4 of 4

Excel Formula Help

  1. #1
    Registered User
    Join Date
    10-06-2006
    Posts
    15

    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
    04-16-2008
    Location
    Vietnam
    MS-Off Ver
    Office 2003 and 2007
    Posts
    129
    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
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252
    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.

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

  4. #4
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    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)

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