Sumifs Formula Problem.

[ARC]

Hi.

When I use the sum(sumifs) array formula below with "Coiled Plate" and "Sheared Plate", it works fine. But, when I attempt to use cell references to replace these, the formula does not accept the cell references as inputs. Please help.

Thank you.

=SUM((SUMIFS(Table3[Ordered Mass],Table3[Cust Inv Sales Region],$C$3,Table3[Prod Strm Sub Desc],{"Coiled Plate","Sheared Plate"},Table3[Alloy],$C$6,Table3[ReqINCOMnth],$B19,Table3[Delivery Term Name],$C$7))/1000)

 

[NARAYANK991]

Hi ,

How are you using cell references ? Can you post the formula that you tried entering ?

Narayan

 

[ARC]

Hi,

I'm using the same formula above, but I have "Coiled Plate" and "Sheared Plate" in cells $C$4 and $C$5 respectively. If I try to replace "Coiled Plate" in the formula with $C$4, the formula will not accept this? Is this because this is an array formula and $C$4 needs to be entered differently?

ARC

 

[NARAYANK991]

Hi ,

With cell references , you cannot use the array symbols { and } ; did you try by entering :

=SUM((SUMIFS(Table3[Ordered Mass],Table3[Cust Inv Sales Region],$C$3,Table3[Prod Strm Sub Desc],$C$4:$C$5,Table3[Alloy],$C$6,Table3[ReqINCOMnth],$B19,Table3[Delivery Term Name],$C$7))/1000)

Narayan

 

[bosco_yip]

Or, try to replace this :

=SUM((SUMIFS(…...,{"Coiled Plate","Sheared Plate"},…….))/1000)

Into this :

=SUM(SUMIFS(…...,TRANSPOSE($C$4:$C$5),…….)/1000)

Regards
Bosco

 

[ARC]

Hi,

I tried both options and both worked.

Thank you very much for your help.

Regards

ARC