Sum with multiple conditions

Question

Hi community

i'm trying to create a formula that calculate a sum in a column based on may criteria.

here is a screenshot of the affected column

i want to calculate the "volume cumul" (sum of volume collected) based on

* Week Active should be "This Week" or "Next Week"
* same Unique ID should be calculated together
* if Unique ID is blank, no count should appears

for now, the formula calculate all Volume collected together and not per Unique ID.

here is my actual formulas:

=IF(AND(OR([Week Active]@row = "This Week", [Week Active]@row = "Next Week"), [Unique ID]@row <> ""), SUM([Volume collecte]$1:[Volume collecte]@row), "")

i've tried a SUMIFS but i can't find the way to count "per Unique ID together"

anyone can help me on this?

thank you

Julien Lopez · Answer

oh… it looks like it works, thank you very much!

DKazatsky2 · Answer

Hi @Julien Lopez,

In order to do this you will need to add 2 columns, which can be hidden if you want.

Column 1: A column set to type "# Auto Number". I called it "Auto"

Colum 2: A new column with the following column formula. I called it "row id".

=MATCH(Auto@row, Auto:Auto, 0)

Next, update the other formula to the following.

=IF([Unique ID]@row <> "", SUMIFS([Volume collecte]:[Volume collecte], [Unique ID]:[Unique ID], [Unique ID]@row, [Week Active]:[Week Active], OR(@cell  = "This Week", @cell  = "Next Week"), [row id]:[row id], <=[row id]@row))

-Dave

Julien Lopez · Answer

Hi @DKazatsky2

thanks for your help.  the formula works to regroup Unique ID together but there is a last issue.  The column should sum the values ​​associated with the same Unique ID for "this week" and "next week". Your formula does not sum the values ​​from one row to the next for the same Unique ID.

in the picture above, the column "Volume cumul" should sum the yellow cells together and the green cells together, the volume remain column should reflect volume decrease for yellow cells and green cells separately.

so values in the "volume cumul" column should be as follow:

for Unique ID 99:

* row 1: 1.0
* row 4: 2.0
* row 10: 2.5

for Unique ID 88:

* row 2: 1
* row 5: 1.5
* row 11: 1.5

is it feasible?