SUMifs returns the total sum of previous cells if the current row is empty

[Gabrielle_erre]

Hey, I'm new here and looking to resolve the next problem.

=SUMIFS(Tabelle2!I:I, Tabelle2!A:A,"*"&A2&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*")


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]SKU[/TD]
[TD]Result of SUMIFS[/TD]
[TD]applied FORMULA for B[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Black[/TD]
[TD]100[/TD]
[TD]=SUMIFS(Tabelle2!I:I,Tabelle2!A:A,"*"&A2&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*")[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]White[/TD]
[TD]100[/TD]
[TD]=SUMIFS(Tabelle2!I:I,Tabelle2!A:A,"*"&A3&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*")[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]200[/TD]
[TD]=SUMIFS(Tabelle2!I:I,Tabelle2!A:A,"*"&A4&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*")[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]200[/TD]
[TD]=SUMIFS(Tabelle2!I:I,Tabelle2!A:A,"*"&A5&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*")[/TD]
[/TR]
</tbody>[/TABLE]

How can I modify the formula in order to have 0 or emty cell in B4 and B5 if in A4 and A5 is nothing shown?

Column A from the table is result of formula =IFERROR(INDEX(Tabelle2[SKU], MATCH(0,INDEX(COUNTIF($A$1:A25,Tabelle2[SKU]),0),0)),"")

Thanks in advance!

 

[AlanY]

try this in B2, and copy down

=IF(AND(ISFORMULA(Tabelle2!A2),LEN(Tabelle2!A2)=0,ISFORMULA(Tabelle2!A3),LEN(Tabelle2!A3)=0),"",SUMIFS(Tabelle2!I:I,Tabelle2!A:A,"*"&A2&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*"))

 

[Gabrielle_erre]

I try but the result is the same. In Tabelle2 column A I have a list of different products-colours where I have the same name as in column A in current table.

 

[AlanY]

are you ok to share the file on online storage, e.g google drive?

 

[Gabrielle_erre]

It works now! Thank you so much! I used:

=IF(AND(ISFORMULA(A2),LEN(A2)=0,ISFORMULA(A3),LEN(A3)=0),"",SUMIFS(Tabelle2!I:I,Tabelle2!A:A,"*"&A2&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*"))
What can I do for last row if on B6 I have the Totalsum again of B1:B5? this part :

=IF(AND(ISFORMULA(A5),LEN(A5)=0,ISFORMULA(A6),LEN(A6)=0),"",SUMIFS(Tabelle2!I:I,Tabelle2!A:A,"*"&A5&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*"))

Now the result of B5 is again the value of the total sum from B1:B5 to the last row. I can modify something?

 

[AlanY]

can you explain how are the 200 in B4 & B5 calculated?

Hey, I'm new here and looking to resolve the next problem.

=SUMIFS(Tabelle2!I:I, Tabelle2!A:A,"*"&A2&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*")


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]SKU[/TD]
[TD]Result of SUMIFS[/TD]
[TD]applied FORMULA for B[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Black[/TD]
[TD]100[/TD]
[TD]=SUMIFS(Tabelle2!I:I,Tabelle2!A:A,"*"&A2&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*")[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]White[/TD]
[TD]100[/TD]
[TD]=SUMIFS(Tabelle2!I:I,Tabelle2!A:A,"*"&A3&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*")[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]200[/TD]
[TD]=SUMIFS(Tabelle2!I:I,Tabelle2!A:A,"*"&A4&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*")[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]200[/TD]
[TD]=SUMIFS(Tabelle2!I:I,Tabelle2!A:A,"*"&A5&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*")[/TD]
[/TR]
</tbody>[/TABLE]

How can I modify the formula in order to have 0 or emty cell in B4 and B5 if in A4 and A5 is nothing shown?

Column A from the table is result of formula =IFERROR(INDEX(Tabelle2[SKU], MATCH(0,INDEX(COUNTIF($A$1:A25,Tabelle2[SKU]),0),0)),"")

Thanks in advance!

 

[Gabrielle_erre]

The number 200 from column B should be the extracted values from column I (tab2) that match the criterias. In B4 and 5 the result was the sum of B2 and B3. The frormula is working fine until the emty cells from A4 and A5, when the extraction it's not possible because I do not have other cells to be counted and no criterias.

My problem was that I wanted B4 and B5 to be 0 or not shown.

=SUMIFS(Tabelle2!I:I,Tabelle2!A:A,"*"&A5&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*")

after using your proposal, the result is:


[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]SKU[/TD]
[TD]Result of SUMIFS[/TD]
[TD]applied FORMULA for B[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Black[/TD]
[TD]100[/TD]
[TD]=IF(AND(ISFORMULA(A2),LEN(A2)=0,ISFORMULA(A3),LEN(A3)=0),"",SUMIFS(Tabelle2!I:I,Tabelle2!A:A,"*"&A2&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*"))[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]White[/TD]
[TD]100[/TD]
[TD]=IF(AND(ISFORMULA(A3),LEN(A3)=0,ISFORMULA(A4),LEN(A4)=0),"",SUMIFS(Tabelle2!I:I,Tabelle2!A:A,"*"&A3&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*"))[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(AND(ISFORMULA(A4),LEN(A4)=0,ISFORMULA(A5),LEN(A5)=0),"",SUMIFS(Tabelle2!I:I,Tabelle2!A:A,"*"&A4&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*"))[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]200[/TD]
[TD]=IF(AND(ISFORMULA(A5),LEN(A5)=0,ISFORMULA(A6),LEN(A6)=0),"",SUMIFS(Tabelle2!I:I,Tabelle2!A:A,"*"&A5&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*"))[/TD]
[/TR]
</tbody>[/TABLE]

I would like that B5 result to be like B4, even if is the last one...

 

[Gabrielle_erre]

[TABLE="class: cms_table_cms_table_grid"]
<tbody>[TR]
[TD]=IF(AND(ISFORMULA(A2),LEN(A2)=0,ISFORMULA(A2),LEN(A2)=0),"",SUMIFS(Tabelle2!I:I,Tabelle2!A:A,"*"&A2&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*"))[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
Looks like this is working just perfect... now in column C I do not have the total sum or error values where B is ampty... Thank you AlanY!

 

[AlanY]

you're welcome