# Sumif for merged Collums

#### Henry_paul_616

##### New Member
Hey guys. I hope you're doing well. I'm Brazilian, intern and I'm having a problem using the SUMIF function to add the cost value of a metal alloy. I would like the function to add the values of the costs that are comprised by the date that is compressed by merged columns. The SUMIF function only returns the first cost and does not add the second cost which would total 144000 dollars. My formula has to add the cost if the league and date are what I want. How can I resolve this issue?
Attached is the excel file.
Can you help me? #### Attachments

• 12.1 KB Views: 4

#### John Jairo V

##### Well-Known Member
Hi, Henry!

Two options for you.

1► With helper row.
PHP:
``[B2] : =IF(B3,B3,A2)``
Drag it to the right until H2
PHP:
``[F11] : =SUMIFS(B6:H6,B4:H4,C8,B2:H2,E11)``
2►Without helper row.
PHP:
``[F11] : =SUMPRODUCT(--(B4:H4=C8),--(LOOKUP(COLUMN(B3:H3),COLUMN(B3:H3)/(B3:H3<>""),B3:H3)=E11),B6:H6)``
Check file. Blessings!

#### Attachments

• 13 KB Views: 7
• • Henry_paul_616 and shili12

#### Peter Bartholomew

##### Well-Known Member
I attempted this problem with 365, not because it is easy but rather because it is surprisingly challenging.
The rational solution is to use a helper row, as shown by @John Jairo V .
I chose to write a Lambda function to return the result for a particular date and alloy.
Code:
``````Costλ
LET(
runDate,      SCAN(0, date, LAMBDA(p, d, IF(d > 0, d, p))),
selectedCost, IF((runDate = aDate) * (alloy = anAlloy), cost),
SUM(selectedCost)
)
));``````
The variable 'runDate' holds the dates with the merged cells filled across.
The fact that the result is not a range prevents the use of SUMIFS, so 'selectedCost holds costs that match the criteria.
The result is summed.
The worksheet formula uses the Lambda function but with the added complexity of performing the calculation for an array of dates.
Code:
``````WorksheetFormula
= MAP(selectedDate, Costλ(selectedAlloy))`````` #### Attachments

• 18.1 KB Views: 3
• • Henry_paul_616 and John Jairo V

#### m9vukyem

##### Member
=IF(B3,B3,A2)
how to use in formula

#### Peter Bartholomew

##### Well-Known Member
That is all very well but, apparently, more than half of the spreadsheets ever written contain merged cells. Mind you, the majority of workbooks do not contain formulas, so perhaps it doesn't matter!
In 365, the SCAN helper function allows fill down, with or without merged cells.
Code:
``````WorksheetFormula
= SCAN(0, date, Fillλ)

Fillλ
= LAMBDA(p, d, IF(d > 0, d, p));``````
so all is not lost.

#### Henry_paul_616

##### New Member
Hi, Henry!

Two options for you.

1► With helper row.
PHP:
``[B2] : =IF(B3,B3,A2)``
Drag it to the right until H2
PHP:
``[F11] : =SUMIFS(B6:H6,B4:H4,C8,B2:H2,E11)``
2►Without helper row.
PHP:
``[F11] : =SUMPRODUCT(--(B4:H4=C8),--(LOOKUP(COLUMN(B3:H3),COLUMN(B3:H3)/(B3:H3<>""),B3:H3)=E11),B6:H6)``
Check file. Blessings!
Thank You!!!!

#### Henry_paul_616

##### New Member
I attempted this problem with 365, not because it is easy but rather because it is surprisingly challenging.
The rational solution is to use a helper row, as shown by @John Jairo V .
I chose to write a Lambda function to return the result for a particular date and alloy.
Code:
``````Costλ
LET(
runDate,      SCAN(0, date, LAMBDA(p, d, IF(d > 0, d, p))),
selectedCost, IF((runDate = aDate) * (alloy = anAlloy), cost),
SUM(selectedCost)
)
));``````
The variable 'runDate' holds the dates with the merged cells filled across.
The fact that the result is not a range prevents the use of SUMIFS, so 'selectedCost holds costs that match the criteria.
The result is summed.
The worksheet formula uses the Lambda function but with the added complexity of performing the calculation for an array of dates.
Code:
``````WorksheetFormula
= MAP(selectedDate, Costλ(selectedAlloy))``````
View attachment 80176
Thank You!!!