IF(SUM) help please
 

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

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)


"Steve B" <Steve wrote in message
...
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

Steve,

You can try this

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

Will sum based on the two criteria simultaneously.

Regards
Robert

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




In article ,
"Steve B" <Steve wrote:

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

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.

--
HTH

Bob Phillips

"Bob Phillips" wrote in message
...
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)


"Steve B" <Steve wrote in message
...
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