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.
| +328 | UBS Tech MD hires Son (from no-name college) as an Intern | 57 | 20h |
| +269 | Evercore Intern Seizure | 38 | 7h |
| +91 | [Official] 2026 IB Analyst Bonus Megathread (with 2025 Consolidated Pay and Perks/Benefits) | 13 | 43m |
| +55 | JPM M&A is Gone??? Purely Coverage Banking??? | 22 | 10h |
| +54 | Is DCM actually underrated ? | 21 | 19h |
| +46 | Are all Tech / TMT groups sweaty? | 33 | 1d |
| +43 | Losing my personality in Banking | 7 | 3h |
| +39 | Am I behind? 31 Year Old Analyst | 9 | 1d |
| +39 | Associate & Above IB exits | 16 | 2d |
| +31 | Incoming IB Analyst: Best Ways to Prepare? | 8 | 2d |
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?
Possimus possimus adipisci at eos sed assumenda sunt. Odio doloremque enim quo consequatur et. Officia consequuntur ratione illum et autem sed suscipit. Ratione rem facilis nisi est. Consectetur est et cum quidem nihil iste non.
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...