0

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.

enter image description here

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

2 Answers 2

1

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 1s and 0s.
  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.
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.