SUMIFS With merged cells

**jharvey87** · 02-21-2019, 07:15 PM

Hi everyone,

Having trouble with a formula. In my spreadsheet, data spits out from a query where a project name (Column A) is merged between 3 rows. For example, row 4/5/6 are the project name. Column B has 3 rows in this order, "Base", "Override","Working". My issue is when doing a sumifs, its totaling 0, which I'm guessing is because of column A. Looking at the formula below, any idea how i might get this to work?

Please Login or Register  to view this content.

Row N is the value I want summed when column A contains the letters "SFL" and column B is equal to "Working".

Any suggestions would be greatly appreciated.

**6StringJazzer** · 02-21-2019, 09:18 PM

This is one reason nearly every expert here recommends against using merged cells. They can look pretty but they usually make for poor data layout design.

My first recommendation is to get rid of merged cells and put the project name in every row.

However, since in this case you are always looking in the third row of each group an alternative might be this, but I haven't tested it. Note that I have shifted down the range for the sum and range to search for Working. All three ranges must be the same shape, but they don't have to be the same rows:

=SUMIFS('CF2 MP'!$N$6:$N$27002,'CF2 MP'!$B$6:$B$27002,"Working",'CF2 MP'!$A$4:$A$27000,"*SFL*")

If this does not work please attach a file with test data, with no private data.

**Melvosh** · 02-21-2019, 11:34 PM

This might work, based on a very limited test range:

Please Login or Register  to view this content.

It has to be entered as an array formula (ctrl+shift+enter). It also assumes that "Working" is always the third value in B, and that there are never more than 3 rows per project.