# Excel Formula Help

1. ## 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.

2. 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. 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

4. eddie barzoon

In future use a ttle that better reflects the type of assistance you require

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