Sumifs not working

[CoolEvil]

Hi All,

Again I am here and need some help from u guys. I have a data in the below mention format.

[TABLE="width: 832"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="class: xl63, width: 64, align: right"]Jan-14[/TD]
[TD="class: xl63, width: 64, align: right"]Feb-14[/TD]
[TD="class: xl63, width: 64, align: right"]Mar-14[/TD]
[TD="class: xl63, width: 64, align: right"]Apr-14[/TD]
[TD="class: xl63, width: 64, align: right"]May-14[/TD]
[TD="class: xl63, width: 64, align: right"]Jun-14[/TD]
[TD="class: xl63, width: 64, align: right"]Jul-14[/TD]
[TD="class: xl63, width: 64, align: right"]Aug-14[/TD]
[TD="class: xl63, width: 64, align: right"]Sep-14[/TD]
[TD="class: xl63, width: 64, align: right"]Oct-14[/TD]
[TD="class: xl63, width: 64, align: right"]Nov-14[/TD]
[TD="class: xl63, width: 64, align: right"]Dec-14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]70889[/TD]
[TD="align: right"]145435[/TD]
[TD="align: right"]146441[/TD]
[TD="align: right"]497470[/TD]
[TD="align: right"]194199[/TD]
[TD="align: right"]448098[/TD]
[TD="align: right"]432087[/TD]
[TD="align: right"]388909[/TD]
[TD="align: right"]1392[/TD]
[TD="align: right"]367637[/TD]
[TD="align: right"]254906[/TD]
[TD="align: right"]378658[/TD]
[/TR]
[TR]
[TD]SCI[/TD]
[TD="align: right"]287796[/TD]
[TD="align: right"]77934[/TD]
[TD="align: right"]194494[/TD]
[TD="align: right"]64462[/TD]
[TD="align: right"]260584[/TD]
[TD="align: right"]271881[/TD]
[TD="align: right"]24007[/TD]
[TD="align: right"]75372[/TD]
[TD="align: right"]101480[/TD]
[TD="align: right"]1523[/TD]
[TD="align: right"]459362[/TD]
[TD="align: right"]210525[/TD]
[/TR]
[TR]
[TD]AMX[/TD]
[TD="align: right"]61544[/TD]
[TD="align: right"]70679[/TD]
[TD="align: right"]276860[/TD]
[TD="align: right"]366322[/TD]
[TD="align: right"]304229[/TD]
[TD="align: right"]455125[/TD]
[TD="align: right"]32849[/TD]
[TD="align: right"]72346[/TD]
[TD="align: right"]176091[/TD]
[TD="align: right"]29332[/TD]
[TD="align: right"]308362[/TD]
[TD="align: right"]240066[/TD]
[/TR]
[TR]
[TD]VIS[/TD]
[TD="align: right"]77540[/TD]
[TD="align: right"]197710[/TD]
[TD="align: right"]362549[/TD]
[TD="align: right"]98747[/TD]
[TD="align: right"]132947[/TD]
[TD="align: right"]139926[/TD]
[TD="align: right"]421101[/TD]
[TD="align: right"]50720[/TD]
[TD="align: right"]269659[/TD]
[TD="align: right"]453259[/TD]
[TD="align: right"]51369[/TD]
[TD="align: right"]266246[/TD]
[/TR]
[TR]
[TD]WIP[/TD]
[TD="align: right"]16518[/TD]
[TD="align: right"]265587[/TD]
[TD="align: right"]214890[/TD]
[TD="align: right"]445334[/TD]
[TD="align: right"]462815[/TD]
[TD="align: right"]219318[/TD]
[TD="align: right"]445505[/TD]
[TD="align: right"]78691[/TD]
[TD="align: right"]278717[/TD]
[TD="align: right"]482123[/TD]
[TD="align: right"]290619[/TD]
[TD="align: right"]34578[/TD]
[/TR]
[TR]
[TD]BIP[/TD]
[TD="align: right"]432954[/TD]
[TD="align: right"]61999[/TD]
[TD="align: right"]468456[/TD]
[TD="align: right"]408241[/TD]
[TD="align: right"]192043[/TD]
[TD="align: right"]473812[/TD]
[TD="align: right"]59385[/TD]
[TD="align: right"]207916[/TD]
[TD="align: right"]263556[/TD]
[TD="align: right"]364356[/TD]
[TD="align: right"]428541[/TD]
[TD="align: right"]52073[/TD]
[/TR]
[TR]
[TD]SCI[/TD]
[TD="align: right"]438118[/TD]
[TD="align: right"]242965[/TD]
[TD="align: right"]300642[/TD]
[TD="align: right"]331500[/TD]
[TD="align: right"]142345[/TD]
[TD="align: right"]212211[/TD]
[TD="align: right"]467461[/TD]
[TD="align: right"]384518[/TD]
[TD="align: right"]55519[/TD]
[TD="align: right"]66144[/TD]
[TD="align: right"]20934[/TD]
[TD="align: right"]16908[/TD]
[/TR]
</tbody>[/TABLE]

Criteria-
Product Code - SCI
Date - Mar-14

Let suppose I want the total amount of SCI for the month of Mar-14

The formula which I am using is =SUMIFS(B3:M9,A4:A9,A12,B1:M1,A13) but I am getting #Value error.
If I have to explain my formula then it would be like; B3:M9 is sum_range, A4:A9 is product name [SCI,AMX,etc] , A12 is product code "SCI", B1:M1 is month range [Jan-14,Feb-14,Mar-14 etc] and A13 is Date [Mar-14]. I want my total amount should be 495136.

Would really appreciate if anyone can help me.
Thanks in advance.

 

[Aladin Akyurek]

=SUMIF(A4:A9,A12,INDEX(B4:M9,0,MATCH(A13,B1:M1,0)))

should do it.

 

[Snakehips]

CoolEvil,

You cannot combine columns and header row in that way using SUMIFS

Another alternative could be SUMPRODUCT like......

=SUMPRODUCT(OFFSET(A3:A9,0,MATCH(A13,B1:M1,0))*(A3:A9=A12))

Hope that helps.

 

[CoolEvil]

Hi
Thank you so much guyz
U rock..

 

[Aladin Akyurek]

Hi
Thank you so much guyz
U rock..

You are welcome. By the way, SumIf would suffice.