SUMIFS Units and skip such values

[Salamullah]

Units	Amount	Month
100	2300	Jan
100	0.10	Jan
200	1000	Feb
-200	-1000	Feb
300	2000	Jan
300	-0.08	Jan

<tbody>
</tbody>

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