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

#### Gabrielle_erre

##### New Member
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! ### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
try this in B2, and copy down

Code:
``=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.

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

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?

Last edited:
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! 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...

Last edited:
 =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!

you're welcome

Last edited:

Replies
9
Views
332
Replies
0
Views
214
Replies
3
Views
505
Replies
5
Views
410
Replies
2
Views
79

### Forum statistics

1,207,255
Messages
6,077,313
Members
446,278
Latest member
hoangquan2310 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back