• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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?
80169
 

Attachments

  • sumif_merged_collum.xlsx
    12.1 KB · Views: 4
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

  • sumif_merged_collum.xlsx
    13 KB · Views: 8
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λ
= LAMBDA(anAlloy, LAMBDA(aDate,
     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))

80176
 

Attachments

  • sumif_merged_collum.xlsx
    18.1 KB · Views: 3
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.
 
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!!!!
 
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λ
= LAMBDA(anAlloy, LAMBDA(aDate,
     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!!!
 
Back
Top