Looking for a formula

Copper Contributor

I'm trying to find a formula.  

I want to add row c4:al84 through row  if row c4:al84 says flats/1/2's/1/3's but if there is nothing in the row it leaves it blank. How can I do this?

7 Replies

Hello,

 

your question is not clear. Where does the row "say" something? Can you mock up the desired result manually, then post that as a screenshot or attach a sample file and explain in context, please?

 

cheers, 

               
               
               
  flatshalfthirdshalf flatsflatshalfthirds     
               
           totals   
           flatshalvesthirds 
1/1/2000 13  5 43     
1/2/2000 5426 1 2     
               
1/3/2000 3 3 2  6     
               
               
           000 
               
               
               
               
               
               

I'm looking for a formula that will add the rows into the highlighted cells but will leave the cells blank if the row has no entries. With out having to enter the formula for each row manually.

Thanks for the additional information. I think what you want to achieve is a conditional sum across the current row, adding only the items that fall into the category listed above the highlighted cells.

 

You can do that with a Sumifs formula. In your sample file, start in cell L8 and enter the following formula

 

=SUMIFS($C8:$J8,$C$4:$J$4,L$7)

 

Note the position of the $ signs. In words: calculate the total of the cells C to J in the current row where the value in the cells C4 to J4 are equal to the cell in row 7 of the current column.

 

You can copy that formula across to cell N8 and then down. See attached file. 

 

This will still show 0 values if there is no data in the columns. There are several options for handling that. 

 

You can set an option for the whole workbook to not show 0 values, or you can format the highlighted cells to not show 0 values (using the custom format "0;-0;;" without the quotes), or you can wrap the formula into an IF statement and show a blank if the result of the Sumifs is a 0 ( the formula for that would be =IF(SUMIFS($C8:$J8,$C$4:$J$4,L$7)=0,"",SUMIFS($C8:$J8,$C$4:$J$4,L$7) -- there is repeated calculations in that one,  but if you don't notice that the workbook is getting slow to calculate, it will be fine).

 

Whichever option you select will depend on the situation and your preference.

 

 

 

Does that work for you?

 

 

 

 

 

 

 

That helps a lot.  I want the formula to calculate 0 if there are numbers in the row to calculate.  If there are no entries in the entire row I want it left blank.  Does this =if formula work for that or would a format be better?

OK, try this in L8, copy across and down

 

=IF(COUNT($C8:$J8)=0,"",SUMIFS($C8:$J8,$C$4:$J$4,L$7))

 

In words: Count the cells that have numbers in columns C to J in the current row. If that count is zero, return a blank, otherwise do the Sumifs. So you will have zero values if there are no halves in a row, but only flats and thirds. 

2018-06-07_16-28-58.png

 

Awesome Thanks that's exactly what I was looking for.