Consulting

Results 1 to 2 of 2

Thread: Array formula - need formula to pick from 2 ranges to use in calculation

  1. #1

    Array formula - need formula to pick from 2 ranges to use in calculation

    I have the following formula
    =SUMPRODUCT((AI$252:AI$100142=TRANSPOSE($AF$164:$AF$168))*$U$252:$U$100142*TRANSPOSE($AG$164:$AG$168))

    The range that is in bold is what needs to change. For each row, I need to check the value in columns 'I' & 'S'.
    Values in column 'S' are either '0.2' or '0.25'. If 'S' = '0.2' then if value in 'I' column is greater than or equal to cell 'AG187' use value from column 'V' in the above formula. If 'S' = '0.25' then if value in 'I' column is greater than or equal to cell 'AG186' use value from column 'V' in the above formula. if the value in 'I' is not greater than or equal to those cells then I need to use the value from column 'U' in the above formula.

    Is this possible? i was thinking maybe another Transpose with 0.2 & 0.25 in cells AF186 & AF187?

    Thanks in advance

  2. #2
    I pulled together a mock sheet of my issue if anyone has any suggestions
    Attached Files Attached Files

Posting Permissions

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