Add up values only if 2 criterias correspond


Posted by selderfield on January 31, 2002 5:53 PM

I need a formula to do the following :-
look in column J for text "ET"
look in column R (same line) for text "POLH", then,
only if the 2 criterias corrospond add values shown in (same line) column T

ie If ET was in col J but "POP" was in column R (same line) the value in column T (same line)would not be included in the total returned.

Best I can come up with (sadly!)
=SUM(IF($J$5:$J$5000="ET",ANDIF($R$5:$R$5000="POLH"),$T$5:$T$5000))

...which returns the total value of column T - and if I make it a CSE formula the cell shows "#name?"

Hopefully one of you kind people will interpret the above and help me out !


Posted by Tom Urtis on January 31, 2002 9:50 PM

See if this array formula works for you. Enter it by pressing Ctrl+Shift+Enter.

=SUM((J5:J5000="ET")*(R5:R5000="POLH")*(T5:T5000))

Tom Urtis




Posted by selderfield on February 01, 2002 3:11 AM


Thanks Tom , it works a treat - and this formula will be really useful.
I'm on a learning curve (with crampons) !
Best Regards
Sandy