Sumif for merged Collums

[Henry_paul_616]

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?

 

[John Jairo V]

Hi, Henry!

Two options for you.

1► With helper row.

[B2] : =IF(B3,B3,A2)

Drag it to the right until H2

[F11] : =SUMIFS(B6:H6,B4:H4,C8,B2:H2,E11)

2►Without helper row.

[F11] : =SUMPRODUCT(--(B4:H4=C8),--(LOOKUP(COLUMN(B3:H3),COLUMN(B3:H3)/(B3:H3<>""),B3:H3)=E11),B6:H6)

Check file. Blessings!

 

[pecoflyer]

How to use merged cells effectively

 

[Peter Bartholomew]

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.

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.

WorksheetFormula
= MAP(selectedDate, Costλ(selectedAlloy))

 

[m9vukyem]

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

 

[Peter Bartholomew]

How to use merged cells effectively

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.

WorksheetFormula
= SCAN(0, date, Fillλ)
 
Fillλ
= LAMBDA(p, d, IF(d > 0, d, p));

so all is not lost.

 

[Henry_paul_616]

Hi, Henry!

Two options for you.

1► With helper row.

[B2] : =IF(B3,B3,A2)

Drag it to the right until H2

[F11] : =SUMIFS(B6:H6,B4:H4,C8,B2:H2,E11)

2►Without helper row.

[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]

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.

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.

WorksheetFormula
= MAP(selectedDate, Costλ(selectedAlloy))

View attachment 80176

Thank You!!!