SUMIFS with or in condition

0
votes

I'm trying to use a SUMIFS formula with conditions that include an or which relies on a cell reference.

I've solved every partial solution to this I can think of but still can't get something that works for the final result.

The expected solution would be:

=sumifs(A:A,B:B,{"x","y"})

where A:A is summed if the value in B is either "x" or "y" (this is an MCVE, the real situation requires multiple conditions including a date comparison, see below)

For literal values, this is a solution:

=ArrayFormula(sumifs(A:A,regexmatch(B:B,"x|y"),TRUE))

Using SUMIF (but not SUMIFS), this works:

=sum(arrayformula(sumif(B:B,{E2,"y"},A:A)))

Multiple SUMIFS work but the number of addends grow exponentially with additional conditions:

=sumifs(A:A,B:B,E2)+sumifs(A:A,B:B,E3)

The following two formulae work in Excel but not Google Sheets:

=SUM(SUMIFS(A:A,B:B,{"x","y"}))

=SUM(SUMIFS(A:A,B:B,E2:E3))

MCVE sheet: https://docs.google.com/spreadsheets/d/11q9RXEr84WuyuAXXmGwkGSdwaKznXD0IyGj187XYp1I/edit

This is the current state of the real formula in cell C78 on the current sheet

=(sumifs('Sheet2'!$C:$C,'Sheet2'!$B:$B,$A78,'Sheet2'!$D:$D,">="&C$1,'Sheet2'!$D:$D,"<"&D$1)
+ArrayFormula(sumifs('Sheet2'!$I:$I,regexmatch('Sheet2'!$J:$J,"CAD|^$"),TRUE,'Sheet2'!$H:$H,$A78,'Sheet2'!$K:$K,"<"&D$1,'Sheet2'!$L:$L, ">"&C$1))
+ArrayFormula(sumifs('Sheet2'!$I:$I,regexmatch('Sheet2'!$J:$J,"CAD|^$"),TRUE,'Sheet2'!$H:$H,$A78,'Sheet2'!$K:$K,"<"&D$1,'Sheet2'!$L:$L, "="))
+sumifs('Sheet2'!$I:$I,'Sheet2'!$J:$J,"USD",'Sheet2'!$H:$H,$A78,'Sheet2'!$K:$K,"<"&D$1,'Sheet2'!$L:$L, ">"&C$1)*C$110
+sumifs('Sheet2'!$I:$I,'Sheet2'!$J:$J,"USD",'Sheet2'!$H:$H,$A78,'Sheet2'!$K:$K,"<"&D$1,'Sheet2'!$L:$L, "=")*C$110)
*if(C$1 > TODAY(), 0, 1)

where the current sheet has monthly dates in row 1, categories in col A, and monthly exchange rate in row 110.

Explanation: Add the following 5 items

  1. One time expenses in the current month
  2. Recurring expenses in CAD or blank currency that existed in this month and haven't ended yet
  3. Recurring expenses in CAD or blank currency that existed in this month and are perpetual
  4. Recurring expenses in USD currency that existed in this month and haven't ended yet (multiplied by FX rate)
  5. Recurring expenses in USD currency that existed in this month and are perpetual (multiplied by FX rate)

I'm trying to at least combine items 2+3 and 4+5 (if not 2-5 completely)

Sheet2 has 2 parts:

  1. One-Time items with categories in col B, values in col C, dates in col D
  2. Recurring items with categories in col H, values in col I, currency in col J (which represents CAD when blank), start date in col K, and end date in col L (which represents perpetual when blank)
2
google-sheetsgoogle-sheets-formula

2 Answers

1
votes

Use SUMPRODUCT:

=SUMPRODUCT((B2:B4={"x","y"})*A2:A4)

or ARRAYFORMULA/SUM:

=ArrayFormula(SUM((B2:B4={"x","y"})*A2:A4))

with cell reference use TRANSPOSE:

=SUMPRODUCT((B2:B4=TRANSPOSE(E2:E3))*A2:A4)

=ArrayFormula(SUM((B2:B4=TRANSPOSE(E2:E3))*A2:A4))

enter image description here

0
votes

I was able to find a solution with sumproduct

=sumproduct(A2:A,(((B2:B=E2)+(B2:B=E3))*((C2:C=F2)+(C2:C=F3))>=1))

This reduces the real formula to the following which allows for more modularity and flexibility:

=(sumifs('Sheet2'!$C:$C,'Sheet2'!$B:$B,$A78,'Sheet2'!$D:$D,">="&C$1,'Sheet2'!$D:$D,"<"&D$1)
+sumproduct('Sheet2'!$I$3:$I,((('Sheet2'!$J$3:$J="CAD")+('Sheet2'!$J$3:$J=""))*('Sheet2'!$H$3:$H=$A78)*('Sheet2'!$K$3:$K<D$1)*(('Sheet2'!$L$3:$L>C$1)+('Sheet2'!$L$3:$L=""))>=1))
+sumproduct('Sheet2'!$I$3:$I,(('Sheet2'!$J$3:$J="USD")*('Sheet2'!$H$3:$H=$A78)*('Sheet2'!$K$3:$K<D$1)*(('Sheet2'!$L$3:$L>C$1)+('Sheet2'!$L$3:$L=""))>=1))*C$110
)*if(C$1 > TODAY(), 0, 1)

One caveat I see is that the range has to be precisely defined and the cell type must be valid throughout (must explicitly exclude header rows)