SUMifs formula not working

[forginganewone]

I am trying to use sumifs formula but it is giving error :

=SUMIFS($B$2:$CK$13,$A$2:$A$13,B$15,$B$1:$AX$1,$A16)

Here is example file : https://a.uguu.se/FFdzUAVNDBbN.xlsx

the only problem i think of is sum_range not being same as criteria range, if there is any other formula that will work instead please let me know.

 

[ali zaib]

There is difference in range selection
The second criteria should be B2:AX13

 

[forginganewone]

There is difference in range selection
The second criteria should be B2:AX13

Yes but i can't change the range due to how data is formatted

 

[ali zaib]

Try it
SUMPRODUCT((A2:A13=B15)*(B1:AX1=A17),B2:AX13)

 

[forginganewone]

Try it
SUMPRODUCT((A2:A13=B15)*(B1:AX1=A17),B2:AX13)

Worked like a charm, Thanks man.

 

[Aladin Akyurek]

If a value like A16 can oly occur once in B1:AX1, a SUMIFS formula will work:

=SUMIFS(INDEX($B$2:$CK$13,0,MATCH($A16,$B$1:$AX$1,0)),$A$2:$A$13,B$15)

 

[ali zaib]

If a value like A16 can oly occur once in B1:AX1, a SUMIFS formula will work:

=SUMIFS(INDEX($B$2:$CK$13,0,MATCH($A16,$B$1:$AX$1,0)),$A$2:$A$13,B$15)

Thanks Sir ,
Your guidelines are always support us