sum if

[kellshepherd]

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

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

How?

[Chris Davison]

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

[Mark W.]

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.