• 469K Members
• 4,308 Online
• 567K Conversations

Looking for a formula

Occasional Contributor

Looking for a formula

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

Re: Looking for a formula

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,

Re: Looking for a formula

 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

Re: Looking for a formula

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.

Re: Looking for a formula

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?

Re: Looking for a formula

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?

Re: Looking for a formula

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.

Re: Looking for a formula

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies