Posts by Matt168

    If I have properly understood what you're after your formula looks a bit upside down, comparing against the cell below when it should be the cell above - A2=A1 and so on. The entry for the false option only needs to refer to the cell above rather than a range anchored at A$2 too. You could use OFFSET for that rather than just referring to A1 etc if anyone is likely to insert rows that spoil the formula.

    I can't exactly see what you're aiming for as an end result, but the formula you're using returns C2:L11 as the width of the range defined by your formula is set according to the depth of your tables.


    =OFFSET('All Data'!$C$2,0,0,COUNT('All Data'!$C:$C)+1,5) would get the range you want, assuming there will always be 5 columns in the target range. The inclusion of rows 2:3 in the range confuses me, but presumably makes sense in the proper context.

    I think I understand your problem on this. If you use INDIRECT to define the range you're looking at it should be more resilient if you're copying it to another workbook. I have attached an example to explain. You could hide the cells that feed in to the INDIRECT part, or possibly rename the sheets in line with your branches. Apologies on the example too, but wouldn't the graph look better as stacked columns?

    The manual version is in cells E6:I10. The number of days per month per line are in E6:I9 (ignore the fact that those day numbers are calculated). Row 10 multiplies those by the relevant day rates from the table, so in January should come to 31 days at £2, February is 28 days at £2 plus ten days at £1.


    The working in E3:I10 was the pie in the sky attempt to solve the problem, but if it worked would match the results in row 10. That version is based on the idea of substituting the values in the From and To columns if they fall outside of the month in question with the last day of the previous or current month. I don't think SUMPRODUCT can be made to work that way, but it has done a lot of other things I haven't thought possible and is my best candidate for solving this.


    I can make it work with
    =SUMPRODUCT((Table1[From]<E$1)*(Table1[To]>EOMONTH(E$1,-1))*(DAY(E$1)*Table1[Daily amount]))
    -SUMPRODUCT((Table1[From]<E$1)*(Table1[From]>EOMONTH(E$1,-1))*(Table1[From]-(E$1-DAY(E$1-1)))*Table1[Daily amount])
    -SUMPRODUCT((Table1[To]<E$1)*(Table1[To]>EOMONTH(E$1,-1))*(E$1-Table1[To])*Table1[Daily amount])


    That calculates a month of days for each row that has any days in the month, less days between the first and From date, less days from the To date to the end of the month. It isn't pretty and I wonder if anyone knows a neater way to do it.

    I have a table with "from" and "to" dates, plus a daily rate. I would like a formula that calculates the total charges arising by month.


    Is there a way to calculate a SUMPRODUCT based on the maximum/minimum of each individual value in the column within the table compared to the start/end date for the month? Just using MAX/MIN within an array of the SUMPRODUCT seems to calculate by reference to the whole column in comparison, which makes sense but isn't what I need.


    Attached is an example. On row 3 is the SUMPRODUCT that I need help with. In columns E to I there are individual workings for the two example rows to show the results I am looking for. In the intended implementation of this, however, there won't be space to do it that way. The final version will also be subject to a couple of extra criteria from additional columns in the main table.


    If there are any decent solutions to this out there I would be really grateful to know.

    Files

    • Test.xlsx

      (10.98 kB, downloaded 47 times, last: )