Nested functions (2002 SP3)

[Forbaty]

Hi,

I have to do a conditional sum which has several conditions.

For the complete situation see the attached file.

The evaluation what should be done is as follows:

If count of a specific sofi number > 1
then
if count "ind. act" = J for that same sofi number > 1
then
Fixed text: "Manual check"
else
sum column C for the selected sofi number where "ind act" = J
else
sum column C for the selected sofi number regardless of the value of "ind act"

My problems lies in the second IF. I think I have to do something with AND but I cannot think of a way to do a count on "ind act" where sofi number has a specific value.
Also I need to find a way to select the correct row if there is a J and N value in "ind act".

Can anyone help me?

Regards Marcel

[HansV]

Enter the following formula in B10:

=IF(COUNTIF($A$2:$A$6,A10)>1,IF(SUMPRODUCT(($A$2:$ A$6=A10)*($B$2:$B$6="J"))>1,"Manual check",SUMPRODUCT(($A$2:$A$6=A10)*($B$2:$B$6="J")* $C$2:$D$6)),SUMPRODUCT(($A$2:$A$6=A10)*$C$2:$D$6))

and fill down to B12.

[Forbaty]

Hi Hans,

Thanks for the answer. I was pulled from my project for a view days because of busy times. So sorry I did not reply sooner.

Regards Marcel

[HansV]

No problem. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>