sumifs with two criterias in two columns

[go2bobby]

ColumnA	ColumnB	ColumnC	ColumnD
Code	User	Role	Hours
PM	Ram	Service	5
Dev	Satish	DBA	9
Internal	John	Service	99
Arch	Mary	SR Cons	22
Internal	Mary	SR Cons	8
Arch	xxx	SR Cons	9
Arch	yyy	Cons	8
My criteria is sumif should ignore if column A is "Internal" along with Column C is "Service".
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.
	User	Result	Expected Result	Remark
	Ram	0	5	Since A2 is not Internal eventhough C2 is Service it should sum 5
	Satish	9	9
	John	0	0	Since A4 is internal and C4 is Service it should not sum
	Mary	22	30	Eventhough A6 is Internal C6 is no Service it should sum 8 (8+22 = 30)
	xxx	9	9
	yyy	8	8

<colgroup><col span="3"><col><col></colgroup><tbody>
</tbody>

 

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