sumifs not working for text containing ">"

[chingg1011]

Hi Expert,

I am encountering below problem from sumifs not working from the text containing ">"

Why F4 cannot return amount 1,000 ? can you help to solve it ? Thanks

Dennis

 

[Eric W]

That's what I get when I try it.

Book1
	A	B	C	D	E	F
1
2			Amount			> 4 weeks up to 6 weeks
3			100	> 4 weeks up to 6 weeks
4			200	> 4 weeks up to 6 weeks		1000
5			300	> 4 weeks up to 6 weeks
6			400	> 4 weeks up to 6 weeks
7			500	> 7 days up to 2 weeks
8			600	> 7 days up to 2 weeks
9			700	> 7 days up to 2 weeks
10			800	> 7 days up to 2 weeks
11			900	> 7 days up to 2 weeks
12		TOTAL	4500
Sheet3

Cell Formulas
Range	Formula
F4	F4	=SUMIFS(C3:C11,D3:D11,F2)
C12	C12	=SUM(C3:C11)

Usually when you don't get the value you expect in SUMIFS, it's because the values don't match exactly what you expect. Check to see if there are extra spaces anywhere in the values. If you can, please present your data using xl2bb (like I did) instead of a picture. We can't see exactly what is in your sheet from a picture.

 

[Michael M]

Also

=SUMIFS(C3:C11,D3:D11,"="&F2)

 

[Phuoc]

Try this:

=SUMPRODUCT(C3:C11,--(D3:D11=F2))