Sumif?

[ha95148]

Hi, my data set is below, with different dates in column A, and there are multiple rows with the same dates
I would like to get the total $amount, for 9/22/17, for example, where Type is not = services
could you help me with the formula for this?

[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"] B
[/TD]
[TD="align: center"] C
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]dates
[/TD]
[TD]$amount
[/TD]
[TD]type
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]8/23/17
[/TD]
[TD]$1250
[/TD]
[TD]product
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]9/22/17
[/TD]
[TD]$1000
[/TD]
[TD]services
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]9/22/17
[/TD]
[TD]$2000
[/TD]
[TD]support
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]10/25/17
[/TD]
[TD]$3000
[/TD]
[TD]product
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 244"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks so much!

 

[SyedUsman]

=SUMIF(A2:C5,E3,B2:B5)-SUMIFS(B2:B5,A2:A5,E3,C2:C5,"services")

 

[DHayes]

Hi,
Here is the formula that you require.

=SUMIFS($B$2:$B$5,$A$2:$A$5,DATE(2017,9,22),$C$2:$C$5," < > "&"services")

 

[SyedUsman]

=SUMIF(A2:C5,E3,B2:B5)-SUMIFS(B2:B5,A2:A5,E3,C2:C5,"services")

E3 being the Date Criteria

 

[ha95148]

I am curious what E3 in the formula is for?

 

[ha95148]

E3 being the Date Criteria

I am curious what E3 in the formula is for?

 

[ha95148]

E3 being the Date Criteria

you mean A3?

 

[SyedUsman]

no E3 is for date if you would like it dynamic , otherwise you can also Hardcode it in the formula like DHayes did

 

[ha95148]

no E3 is for date if you would like it dynamic , otherwise you can also Hardcode it in the formula like DHayes did

I see. I do want the dates to be dynamic. Thank you