IF(SUM) help please

[Steve B]

I need to sum values in a column only if criteria are met in two other
columns. For example - sum column $AV:$AV if $A:$A="Level 2" and
$E:$E="02-7121-45"

=if(($A:$A="Level 2" and $E:$E="02-7121-45", (sum($AV:$AV), "No Value"))

What is wrong with this syntax?

TIA, Steve

 

[Bob Phillips]

That particular formula needs reversing

=SUM(IF(AND($A:$A="Level 2",$E:$E="02-7121-45"), $AV:$AV,0))

which is an array formula, so commit with Ctrl-Shift-Enter, or alternatively
use

=SUMPRODUCT(--($A1:$A1000="Level 2"),--($E1:$E1000="02-7121-45"),
$AV1:$AV1000)

which is not an array formula. Note SUMPRODUCT doees not work on a whole
column



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[cmart02]

Steve,

You can try this

=SUMPRODUCT(--((IntervalA="Level2")*(IntervalB="02-7121-45")),SumInterval)

Will sum based on the two criteria simultaneously.

Regards
Robert

 

[JE McGimpsey]

What is wrong with this syntax?

Quite a lot, actually.

For one thing, you can't use entire columns in array formulae. For
another there's no "and" operator. The AND() function syntax is

AND(condition1, condition2)

Third, your parentheses are wrong. Fourth, Summing AV:AV within the IF
will return the sum of AV:AV, irrespective of the conditions.


So try something like:

=SUMPRODUCT(--(A1:A1000="Level 2"),--(E1:E1000="02-7121-45"),
AV1:AV1000)


For an explanation of the "--", see

http://www.mcgimpsey.com/excel/doubleneg.html

 

[Bob Phillips]

That sum formula should have been

=SUM(IF(A1:A1000="Level2",IF(E1:E1000="02-7121-45",AV1:AV1000,0),0))

still an array formula.