Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Nested functions (2002 SP3)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Nested functions (2002 SP3)

    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.

  3. #3
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Nested functions (2002 SP3)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Nested functions (2002 SP3)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •