SUMIFS Units and skip such values

[Salamullah]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Units
[/TD]
[TD]Amount
[/TD]
[TD]Month
[/TD]
[/TR]
[TR]
[TD]100
[/TD]
[TD]2300
[/TD]
[TD]Jan
[/TD]
[/TR]
[TR]
[TD]100
[/TD]
[TD]0.10
[/TD]
[TD]Jan
[/TD]
[/TR]
[TR]
[TD]200
[/TD]
[TD]1000
[/TD]
[TD]Feb
[/TD]
[/TR]
[TR]
[TD]-200
[/TD]
[TD]-1000
[/TD]
[TD]Feb
[/TD]
[/TR]
[TR]
[TD]300
[/TD]
[TD]2000
[/TD]
[TD]Jan
[/TD]
[/TR]
[TR]
[TD]300
[/TD]
[TD]-0.08
[/TD]
[TD]Jan
[/TD]
[/TR]
</tbody>[/TABLE]

I want to apply formula in the column A Units and wanted to skip units if Values in column B is between -0.5 to 0.5, please help based on the above example the answer of column A should be 400 in Cell E2 whereas D2 contained Jan. with SUMIFS formula

 

[kweaver]

This is 400, but do you really mean SKIP within that range? If so, it wouldn't be 400 would it?

=SUMIFS(A2:A7,B2:B7,">="&-0.5,B2:B7,"<="&0.5)

 

[kweaver]

Maybe you mean this:

=SUMPRODUCT((A2:A7)*((B2:B7<=-0.5)+(B2:B7>=0.5)))

which is 400 which is the sum of all the values in the A column that are <=-0.5 OR >=0.5

 

[Fluff]

Or is this what you want

Book1
	A	B	C	D
1	Units	Amount	Month
2	100	2300	Jan	400
3	100	0.1	Jan
4	200	1000	Feb
5	-200	-1000	Feb
6	300	2000	Jan
7	300	-0.08	Jan
Week 28

Cell Formulas
Range	Formula
D2	=SUMPRODUCT((C2:C7=C2)*((B2:B7<=-0.5)+(B2:B7>=0.5))*(A2:A7))

 

[Salamullah]

Many thanks its working

 

[Fluff]

Glad we could help & thanks for the feedback.

For reference, which formula is correct?

 

[Salamullah]

Both