sumifs with two criterias in two columns

[go2bobby]

[TABLE="width: 681"]
<colgroup><col span="3"><col><col></colgroup><tbody>[TR]
[TD]ColumnA[/TD]
[TD]ColumnB[/TD]
[TD]ColumnC[/TD]
[TD]ColumnD[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Code[/TD]
[TD]User[/TD]
[TD]Role[/TD]
[TD]Hours[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]PM[/TD]
[TD]Ram[/TD]
[TD]Service[/TD]
[TD="align: right"]5[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Dev[/TD]
[TD]Satish[/TD]
[TD]DBA[/TD]
[TD="align: right"]9[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Internal[/TD]
[TD]John[/TD]
[TD]Service[/TD]
[TD="align: right"]99[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Arch[/TD]
[TD]Mary[/TD]
[TD]SR Cons[/TD]
[TD="align: right"]22[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Internal[/TD]
[TD]Mary[/TD]
[TD]SR Cons[/TD]
[TD="align: right"]8[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Arch[/TD]
[TD]xxx[/TD]
[TD]SR Cons[/TD]
[TD="align: right"]9[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Arch[/TD]
[TD]yyy[/TD]
[TD]Cons[/TD]
[TD="align: right"]8[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]My criteria is sumif should ignore if column A is "Internal" along with Column C is "Service".[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 5"]The formula I used in Result column is =SUMIFS($D$2:$D$8,$B$2:$B$8,$B12,$A$2:$A$8,"<>Internal",$C$2:$C$8,"<>Service"). I am not getting the expected Result.[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]User[/TD]
[TD]Result[/TD]
[TD]Expected Result[/TD]
[TD]Remark[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Ram[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]Since A2 is not Internal eventhough C2 is Service it should sum 5[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Satish[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]John[/TD]
[TD]0[/TD]
[TD="align: right"]0[/TD]
[TD]Since A4 is internal and C4 is Service it should not sum[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Mary[/TD]
[TD]22[/TD]
[TD]30[/TD]
[TD]Eventhough A6 is Internal C6 is no Service it should sum 8 (8+22 = 30)[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]xxx[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]yyy[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

So the only ones you dont want summing are when column A shows Internal and column C shows Service?? If so:

=SUMPRODUCT($D$2:$D$8,--($B$2:$B$8=A12),--(($A$2:$A$8<>"internal")+($C$2:$C$8<>"service")>0))

 

[go2bobby]

So the only ones you dont want summing are when column A shows Internal and column C shows Service?? If so:

=SUMPRODUCT($D$2:$D$8,--($B$2:$B$8=A12),--(($A$2:$A$8<>"internal")+($C$2:$C$8<>"service")>0))

Thank you very much Steve. It worked.