Search code examples
excelfinancesumifs

Excel Sumproduct with requirements and indefinite list (entire column)

Desired Ouput: Total expenses for each month.

Input: One or indefinately many items. For each item there are:

  1. Start month
  2. Interval in months
  3. Cost
  4. 'active-inactive' flag

I got pretty far using SUMPRODUCT, see the example below.

Example

=SUMPRODUCT(--($I$2:$I$6="x");--(MOD(MONTH(O2)-MONTH($M$2:$M$6);$L$2:$L$6)=0);$K$2:$K$6)

Where:

  • $I$2:$I$6 --> 'active-inactive' flag of the items
  • O2 --> month for which to calculate the total expenses
  • $M$2:$M$6 --> 'Start month' of the items
  • $L$2:$L$6 --> 'Interval in months' of the items
  • $K$2:$K$6 --> 'Cost of the items

Problem: The formula only works with defined ranges ($I$2:$I$6). However, I want the formula to work for any amount of items without adjusting the range ($I:$I). All my trials resulted either in errors or excessively long calculation times.

Grateful for any help.

Solution

  • You want to use the following to find the last row with data:

    =INDEX($M:$M;MATCH(1e99;$M:$M))

    This will find the last row in column M and use that to set the last cell in that column.

    So adding that to your formula we get:

    =SUMPRODUCT(($I$2:INDEX($I:$I;MATCH(1e99;$M:$M))="x")*(MOD(MONTH(O2)-MONTH($M$2:INDEX($M:$M;MATCH(1e99;$M:$M)));$L$2:INDEX($L:$L;MATCH(1e99;$M:$M)))=0)*$K$2:INDEX($K:$K;MATCH(1e99;$M:$M)))

    And now the dataset size is dynamic based on the last row in column M with dates in it.