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&"*")

	A	B	C
1	SKU	Result of SUMIFS	applied FORMULA for B
2	Black	100	=SUMIFS(Tabelle2!I:I,Tabelle2!A:A,"*"&A2&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*")
3	White	100	=SUMIFS(Tabelle2!I:I,Tabelle2!A:A,"*"&A3&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*")
4		200	=SUMIFS(Tabelle2!I:I,Tabelle2!A:A,"*"&A4&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*")
5		200	=SUMIFS(Tabelle2!I:I,Tabelle2!A:A,"*"&A5&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*")

<tbody>
</tbody>

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&"*")

	A	B	C
1	SKU	Result of SUMIFS	applied FORMULA for B
2	Black	100	=SUMIFS(Tabelle2!I:I,Tabelle2!A:A,"*"&A2&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*")
3	White	100	=SUMIFS(Tabelle2!I:I,Tabelle2!A:A,"*"&A3&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*")
4		200	=SUMIFS(Tabelle2!I:I,Tabelle2!A:A,"*"&A4&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*")
5		200	=SUMIFS(Tabelle2!I:I,Tabelle2!A:A,"*"&A5&"*",Tabelle2!C:C,"*"&List_daily!$L$35&"*")

<tbody>
</tbody>

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:

A	B	C
1	SKU	Result of SUMIFS	applied FORMULA for B
2	Black	100	=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&"*"))
3	White	100	=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&"*"))
4			=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&"*"))
5		200	=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&"*"))

<tbody>
</tbody>

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

 

[Gabrielle_erre]

=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&"*"))

<tbody>
</tbody>

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