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.
| +241 | UBS Layoffs (October 2026) | 70 | 5h |
| +110 | Do people still want to go PE? | 34 | 8h |
| +70 | UBS Tech layoff | 20 | 3h |
| +62 | Bloomberg - New UBS Investment Bankers See Smaller Pay Gain on Promotion | 15 | 19h |
| +46 | Think I'm fucking up my analyst years | 16 | 4d |
| +36 | What Differentiates the Best Analysts as AI Improves? | 9 | 18h |
| +33 | Lazard hirevue | 11 | 20h |
| +26 | Ambitious CC Student trying to break into IB | 14 | 6d |
| +22 | Cornell Club T10 Rankings (UPDATED 10/2026) | 15 | 1h |
| +22 | Quitting After First 4 months | 10 | 5h |
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?
Natus voluptas culpa commodi nisi occaecati veniam. Quia corporis nihil id autem sapiente odio. Repellendus possimus non perferendis deserunt tempore dolorem unde. Cumque sit blanditiis harum. Nulla veritatis dolorum impedit sunt exercitationem iste. Reiciendis animi consequuntur vel.
Dolor voluptate eaque nisi quibusdam qui. Eaque repellat occaecati autem quam. Labore nulla voluptatem enim dolores. Qui eos quod dolorem voluptate aut. Enim rerum animi vel voluptas illum. Accusantium eum omnis voluptas aut animi animi quia.
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...