Jun 03 2018
04:23 PM
- last edited on
Jul 31 2018
08:21 AM
by
TechCommunityAP
Jun 03 2018
04:23 PM
- last edited on
Jul 31 2018
08:21 AM
by
TechCommunityAP
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?
Jun 04 2018 09:29 PM
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,
Jun 05 2018 06:43 PM
flats | half | thirds | half | flats | flats | half | thirds | |||||||
totals | ||||||||||||||
flats | halves | thirds | ||||||||||||
1/1/2000 | 1 | 3 | 5 | 4 | 3 | |||||||||
1/2/2000 | 5 | 4 | 2 | 6 | 1 | 2 | ||||||||
1/3/2000 | 3 | 3 | 2 | 6 | ||||||||||
0 | 0 | 0 | ||||||||||||
Jun 05 2018 06:45 PM
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.
Jun 05 2018 11:38 PM - edited Jun 05 2018 11:45 PM
Jun 05 2018 11:38 PM - edited Jun 05 2018 11:45 PM
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?
Jun 06 2018 07:33 PM
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?
Jun 06 2018 09:30 PM
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.
Jun 10 2018 01:03 PM
Awesome Thanks that's exactly what I was looking for.