excelexcel-formulasumifs

Sumifs with criteria checking a substring of a string

Does someone know how to add a condition in SUMIFS where we check if the column contain a substring, which depends on the string of a cell. I attach a screenshot (in it, SOMME.IF.ENS is equivalent to SUMIFS, different language sorry).

Basically, in K14 I want to:

  1. subtract a value from K13
  2. this value must be subtracted if row J is the same as cell K12
  3. and if raw L contains the string in cell J12 (2024 in this case)

I can do 1 and 2 but for 3 I cannot find a way to make it work. Any idea? Thx

enter image description here

Solution

  • Here is what you could try using SUMIFS():

    enter image description here

    =K13-SUMIFS($K17:$K19,
    $J17:$J19,K12,
    $L17:$L19,">="&DATE($J12,1,1),
    $L17:$L19,"<="&EDATE(DATE($J12,1,1)-1,12))

    Or, Using SUM() or SUMPRODUCT()

    =K13-SUM((J17:J19=K12)*(YEAR(L17:L19)=J12)*K17:K19)

    For Google Sheets the second option would need a wrap up of ARRAYFORMULA()

    =K13-ARRAYFORMULA(SUM((J17:J19=K12)*(YEAR(L17:L19)=J12)*K17:K19))