CODEWITHSUNDEEP
excelmergecell
Jsilva
Jsilva

Reputation: 1

Excel issue with SUMIFS in a range of columns with merge

I'm having a problem on using the SUMIFS function sum values with a range of columns having diferente columns to compare.

Example:

SUMIFS(AB5:Z5S;$AB$2:$ZS$2;"=A";$AB$4:$ZS$4;"=B")

This works for a single column. The problem is that i want to apply this function with a merged cells. The first condition is a 5 columns merged cells and the second condition is at the fifth column. Because the value for the first condition points to the first column of the five merged cells, this formula it's not working.

algorithm example:

`AB2 AND AB4 ;
 AB2 AND AC4 ;
 AB2 AND AD4 ;
 AB2 AND AE4 ;
 AB2 AND AF4 `

The AB2 - AF2 are merged cells.

Next IF test:

`AG2 AND AG4 ;
 AG2 AND AH4 ;
 AG2 AND AI4 ;
 AG2 AND AJ4 ;
 AG2 AND AK4 `

And so on... until the end of value data.

Is there any way to solve this issue without VBA?

Many thanks.

Upvotes: 0

Views: 1885

Answers (1)

Doogie
Doogie

Reputation: 11

This might work:

  1. Do one formula for the merged cells
  2. Place it in another cell, then hide that cell
  3. Use that hidden cell in the second formula

Upvotes: 1

Related Questions