Hi Excel Whizzes,
I have a very frustrating problem. All I want to do is SUM the cells within the range if they are less than or equal to another cell. For example if I have
A1 = 10
B2:B10 is data.
I want to sum every cell within B2:B10 if it is less than or equal to 10.
This is what I have
=SUMIF($B$2:$B$10, "=A1", $B$2:$B$10)
I'm getting 0 for everything...
I also want to do the same thing with countif to get an average.
Am I being clear enough? Let me know if I can elaborate.
| +110 | UBS Accused of Favoring H-1B Workers While Cutting U.S.-Born Employees | 19 | 56m |
| +81 | Incompetent and annoying co-intern | 23 | 21h |
| +69 | Getting Laid in IB? | 28 | 3d |
| +66 | Intern keeps sleeping at work - what to tell them | 28 | 1d |
| +55 | Living in greenwich as an analyst? | 12 | 20h |
| +42 | NYC Associate Budget | 14 | 1d |
| +36 | Nauseous every morning for past 3 years in IB, anyone else? | 21 | 1d |
| +36 | Quick Thoughts on CVC AI Sale Process | 7 | 1d |
| +31 | PJT M&A Group Selection 2026 | 4 | 5h |
| +29 | Going out / social schedule as AN1 | 14 | 3d |
Career Resources
Get instant access to lessons taught by experienced private equity pros and bulge bracket investment bankers including financial statement modeling, DCF, M&A, LBO, Comps and Excel Modeling.
Try this: =SUMIFS($B$2:$B$10,$B$2:$B$10,">="&$A$1)
SUMIFS works in Excel 2007 and 2010. If using 2003, you need to use an array formula. Type the formula below and CTRL+SHIFT+ENTER. You'll get brackets around it if you did it correctly:
=SUM(IF($B$2:$B$10>=$A$1,$B$2:$B$10,0))
=SUMIFS($B$2:$B$10,$B$2:$B$10,"="&$A$1)
I changed the sign because it is less than or equal to, but I am returning 0...
Your formula is wrong. To get the sum you are looking for use below.
SUMIF($B$2:$B$10,"="&$A$1)
If you want average use: AVERAGEIF($B$2:$B$10,"="&$A$1)
Good luck
onthebeach's works too.
I think it's returning 0 as 'false'?
you need to use the formula OnTheBeach mentioned because your criteria is only in one cell. sumif and sumifs only work when each row as the criteria.
this is an array, so its comparing each cell in column B against the criteria, and if its true returning that cell. then it's summing the array of values.
Forget about sumifs, always go with sumprod when possible:
=SUMPROD((B2:B10)*(B2:B10>A1))
SUMIFS returns Zero (Originally Posted: 04/11/2014)
I have Excel set up like this below:
A18:A28- Which is the criteria range B18:B28- Which is the sum range
There are three criteria's in the criteria range
and the formula I have is below:
=SUMIFS(B18:B28,A18:A28,A18,A18:A28,A22,A18:A28,A26) and its returning zero.
When I do this instead
=SUMIFS(B18:B28,A18:A28,A18)+SUMIFS(B18:B28,A18:A28,A22)+SUMIFS(B18:B28,A18:A28,A26)
I get the correct answer 6.
Shouldn't the above formula return 6 as well, instead I am getting zero.
What is exactly is the issue?
Suscipit voluptatem et voluptas illum ut ratione nemo. Nihil aut rerum nobis.
Sint hic quam error doloremque quis. Est similique aut laboriosam voluptatem quisquam. Quibusdam tempore quibusdam suscipit omnis voluptas ut.
Modi mollitia odio cupiditate eius alias. Est fugiat omnis eum animi quis eos repudiandae. Ipsa eum est eaque explicabo et. Omnis dolore consequatur sit. Dolor asperiores in eligendi animi omnis. Minus optio voluptatem reprehenderit autem a laudantium vel.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...