CODEWITHSUNDEEP
excel
Glss1
Glss1

Reputation: 45

sumifs formula with date criteria not working

I posted this yesterday, although realised the equation still doesn’t do quite what I want it to do.

(For some reasons it still SUMs values of dates outside the range specified. IE it still sums values that have years 2029, 2027 even though I want it to group values 2028 and sum them.

Would love any recommendation… Pretty sure the only way to solve this issue is incorporating the dates into the SUMIF equation. I still cannot seem to figure this one out!

This is my previous post...

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(OR(AB3=0,DO3<>$DG$8),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: 238

Answers (1)

Ike
Ike

Reputation: 13024

I am a big fan of the new LET formula - as you can read it much better than the classic formulas.

Using a table (Insert > table) adds another level of "readability".

This is my setting:

enter image description here

And this is the formula:

=LET(isRelevant,IF(AND([@Use]>0,[@year]=year),TRUE,FALSE),
data,IF(isRelevant, FILTER([tons],
              ([group]=[@group])*([Use]>0)*([year]=year)*
              (ROW([Use])<=ROW()),0 ),0),
SUM(data))

The first step isRelevant checks if we need to calculate data for this row.

Second step data applies all your IFs to the FILTER function - referencing the columns by there names - and by that making it understandable.

Multiple conditions are concatenated by a * to become an AND.

ROW([Use])<=ROW() makes sure that only values including the current row plus the above rows are returned.

Upvotes: 0

Related Questions