Results 1 to 3 of 3

Thread: Rows are making errors in formula

  1. #1
    Neophyte momofhim's Avatar
    Join Date
    Jan 2021
    Posts
    1
    Articles
    0
    Excel Version
    Office Professional Plus 2016

    Rows are making errors in formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello, I have the following formula: SUMPRODUCT((YEAR(Breakdown1!$N$2:$N$155)<YEAR('Capacity v Demand'!C$1))*(Breakdown1!$O$2:$O$155='Capacity v Demand'!$A2)*(Breakdown1!$P$2:$P$155))

    On the Breakdown sheet the number of rows is constantly changing. With the number of rows constantly changing I am getting #VALUE errors. in the capacity v demand work sheet. I'm not sure how to change my formula to accommodate for the changing number of rows.

    Thanks in advance

  2. #2
    Admin Alumnus Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,747
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Hi
    the easiest way is to use Excel Tables
    Thank you Ken for this secure forum.

  3. #3
    Seeker RET's Avatar
    Join Date
    Nov 2020
    Location
    Spain
    Posts
    10
    Articles
    0
    Excel Version
    2019
    You could also use INDIRECT to build the target range. Count the number of rows with data and concatenate the target address into INDIRECT

Posting Permissions

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