Glss1
Reputation: 45
Years criteria Sumifs statement excel
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 |
Upvotes: 0
Views: 63
Answers (1)
JvdV
Reputation: 75840
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)
Upvotes: 2