CODEWITHSUNDEEP
excelexcel-formula
Luca Colombo
Luca Colombo

Reputation: 33

Sumifs with date difference in range

I have a spreadsheet with:

The estimation is calculated with the following criteria (in AND logic):

  1. date is between "since" and "to" (if since is empty, it is always valid, and the same for "to")
  2. the month is a multiple of "every month" starting from "since", i.e. the voice must be included if "data" = "since" + "every months" * x, where x is an integer number

I wanted to create a sumifs like the following (Italian Excel, sorry): =SOMMA.PIÙ.SE('Uscite'!$E$2:$E$1048576;'Uscite'!$C$2:$C$1048576;"<="&'v3'!$A2;'Uscite'!$D$2:$D$1048576;">="&'v3'!$A2;?????)

where ????? is the missing piece: I considered using a DATEDIF with "m", in order to get the difference in months between cell Ax and 'Uscite'!Since, then verifying if this difference is a perfect multiple of "every months", but no clue how to do it. Any suggestion?

Thanks for your time!

Upvotes: 1

Views: 152

Answers (1)

mark fitzpatrick
mark fitzpatrick

Reputation: 3322

In english formulation, you could do:

=SUM( 'Entrate ricorrenti'!$E$2:$E$100 *
       ( ('Entrate ricorrenti'!$D$2:$D$100 >= 'v3'!A2) + ISBLANK('Entrate ricorrenti'!$D$2:$D$100) ) *
       IFERROR( MOD( DATEDIF( 'Entrate ricorrenti'!$C$2:$C$100, 'v3'!A2, "M" ), 'Entrate ricorrenti'!$B$2:$B$100 ) = 0,
                0 ) )

In Italian:

=SOMMA( 'Entrate ricorrenti'!$E$2:$E$100 *
       ( ('Entrate ricorrenti'!$D$2:$D$100 >= 'v3'!A2) + VAL.VUOTO('Entrate ricorrenti'!$D$2:$D$100) ) *
       SE.ERRORE( RESTO( DATA.DIFF( 'Entrate ricorrenti'!$C$2:$C$100; 'v3'!A2; "M" ); 'Entrate ricorrenti'!$B$2:$B$100 ) = 0;
                  0 ) )

So, this uses the very nice dinosaur function that you taught me about today. I have never seen this except for DAX.

It simply filters out the payments that stopped before the target date, ('v3'!A2) by multiplying the payment values, ('Entrate ricorrenti'!$E$2:$E$100), times the boolean expression

(('Entrate ricorrenti'!$D$2:$D$100 >= 'v3'!A2) + ISBLANK('Entrate ricorrenti'!$D$2:$D$100))

that also checks to see if the To Date is blank. Then it multiplies that times the modulo of the DATEDIF in months by the Frequency in months. If that is zero, then a payment lands in that month. DATEDIF has the nice property that if the date difference is negative, it generates an N/A error. This allows us to wrap it in an IFERROR to replace that with zero - this prevents the formula from including payments that have not yet started.

Upvotes: 1

Related Questions