Most of the SUMIFS criteria seems to work the way I want it to work, except the years criteria does not. It seems that once 2028 is recognised in column “DO” (years) it will continue to SUM (even if the year is not 2028). Does anyone have any suggestions?
=IF(AB3=0,0,SUMIFS($I$3:$I3,$B$3:$B3,B3,$AB$3:$AB3,">0",$DO$3:$DO3,$DG$8))
The $DG$8 cell reference is: 2028
| AB (use or not to use) | I (tons) | B (group) | DO (year) | What I want |
|---|---|---|---|---|
| 1 | 200 | 2 | 2028 | 200 |
| 0 | 200 | 2 | 2028 | 0 |
| 1 | 210 | 2 | 2028 | 410 |
| 1 | 240 | 2 | 2025 | 0 |
| 1 | 50 | 2 | 2024 | 0 |
| 1 | 200 | 3 | 2028 | 200 |
| 1 | 210 | 3 | 2025 | 0 |
| 1 | 240 | 3 | 2028 | 440 |
| 1 | 50 | 3 | 2024 | 0 |
| 0 | 240 | 3 | 2028 | 0 |
CodePudding user response：
You can use AND() to check if "Year" is 2028 beforehand if you wish to keep these rows at zero:
Formula in G2:
=IF(AND(A2,D2=2028),SUMIFS(B$2:B2,A$2:A2,1,C$2:C2,C2,D$2:D2,2028),0)