# Googlel Sheets formula to SUMIFS with multiple AND and OR criteria

I have a dataset similar to the image below. I am trying to calculate the sum in column A if:

• Region (G) = NorAm AND Team (H) = Corporate AND Stage 7 (F) <= 2/1/24 where either of these conditions are met:
• Stage 3 (B) OR Stage 4 (C) OR Stage 5 (D) OR Stage 6 (E) <=2/1/24

I want to be able to change Team (H) between Corporate / Enterprise and Region (G) between NorA / EMEA.

• Your question isn't clear. edit to provide some examples of initial data and expected result so we better understand what you mean. Feb 22 at 3:18
• this resource shows how to use `SUMIFS`with multiple criteria using `AND` logic Feb 22 at 10:40

# Formula

``````=LET(rng, A2:I, dt, "2/1/24", rg, "NorAm", tm, "Corporate",
dif, LAMBDA(c,
ARRAYFORMULA(IFERROR(1/(1/INDEX(rng,,c)) <= dt))),
SUMPRODUCT(INDEX(rng,,1), INDEX(rng,,8)=rg, INDEX(rng,,9)=tm,
dif(7), (dif(2) + dif(3) + dif(4) + dif(5) + dif(6))>0))
``````
• Hardcoded values for date `dt`, region `rg`, and team `tm` can be replaced by cell references.

# Explanation

1. The data range and the lookup values for date, region, and team are respectively stored in `rng`, `dt`, `rg`, and `tm`.
2. A LAMBDA function is used for the date difference test and stored in `dif`. This is done to reuse the code rather than duplicating it for each test:
1. The LAMBDA expects a column number to be passed in which is represented in the formula by the variable named `c`.
2. The LAMBDA's formula returns an array of values consisting of `TRUE`, `FALSE`, or blank:
• A test is performed to ensure there is a non-zero number in column `c`. This ensures that blank cells are not interpreted as dates: 1 is divided by the inverse of each value in column `c` of `rng` which will not change non-zero numbers but will trigger an error for anything else:
• IFERROR replaces any errors by empty values
• non-zero numbers are compared to `dt` and `TRUE` or `FALSE` is returned as appropriate, which are later interpreted by SUMPRODUCT as `1`s and `0`s.
3. SUMPRODUCT is used to return the sum of the values in Column 1 where all the following conditions are met:
1. Col 1 contains a number, Col 8 contains `rg`, and Col 9 contains `tm`. INDEX is used to specify the correct column.
2. Col 7 is `<=dt`. Tested with `dif(7)`
3. At least one of Col 2 through Col 6 `<=dt`. This is tested by performing `dif(n)` for those columns and then, for each row, checking if the sum of the results is greater than zero, meaning that for that row, one or more of those columns returned `TRUE`.

You would need more than one cell/column depending on how you implement.

I'm assuming you want both bulletpoints as requirnments, as well as the ability to switch what they are searching for in the middle.

I don't think it's possible to do the OR part in the same cell/column as your results with only formulas (you would need a helper column)

One note; your text says Region is in column G, and the picture says column H. I'll use the picture columns (I.E. Region = H, Team = I, Stage 7 = F, etc.)

I would insert into J1 "<=2/1/24" and the rest of column J "=COUNTIF(\$C2:\$F2,\$J\$1)" (this would go into J2 for example). If you don't want the <=2/1/24 criteria changeable just stick it directly into the countif.

then into column K I would set the rest of the changeable criteria (dropdown btw Corporate / Enterprise in K2 and dropdown btw NorA / EMEA in K3) and "<= 2/1/24" in K4. if you don't want the <=2/1/24 changeable, stick it directly into the sumif

In K1 "=SUMIF(A:A,J:J,J1, I:I,K2,H:H,K3,G:G,K4)"

you probably should make a note as to what the different values (J1, K2, K3, K4) change.