Thanks:  0
Likes:  0

1. I have 3 fields.. branch, affiliate, and a number field (mf).

i want to SUM mf IF branch=0001 AND affiliate="Balanced"

How?

2. try SUMPRODUCT

if branch is in column A starting at A2 and affiliate is in column B starting at B2 and mf is in column C starting at C2 :

=SUMPRODUCT((\$A\$2:\$A\$100="0001")*(\$B\$2:\$B\$100="Balanced"),(\$C\$2:\$C\$100))

I use this a lot now and prefer to put the actual variables in their own cells (0001 and "Balanced") so I can just link to them in case I need to change them...... rather than changing the actual formula 100 (or more) times :

=SUMPRODUCT((\$A\$2:\$A\$100=F1)*(\$B\$2:\$B\$100=G1),(\$C\$2:\$C\$100))

where F1 houses "0001" and G1 houses "Balanced"

ie

3. On 2002-03-04 10:41, kellshepherd wrote:
I have 3 fields.. branch, affiliate, and a number field (mf).

i want to SUM mf IF branch=0001 AND affiliate="Balanced"

How?
Use...

{=SUM(mf*(branch="0001")*(affiliate="Balanced"))}

...where mf, branch and affiliate are cell ranges with the same dimensions.

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•