I recently went to Sydney to conduct some training programs on Advanced Excel and Power BI. While I was there, I met my good friend Danielle, who runs Plum Solutions, a financial modeling consultancy & training company. We got talking about various things and the topic eventually turned to “finance people and Power BI”. We debated whether finance professionals (analysts, reporting people, financial modelers and controllers) should bother learning Power BI or stick to Excel.
As this is an interesting topic, I am sharing my thoughts in this post along with a video of our debate at the end of it. Feel free to chip in and share your views to.
Let’s examine both sides of the argument. We start with why you need to learn Power BI and then look at why you don’t want to.
Why finance people should learn Power BI?
- Power BI is visual & easy: You can quickly take a P&L or complex model outputs and turn them in to a bunch of interactive visuals with Power BI. We all know how much decision makers and customers love visuals. So this is a huge win.
- Power BI makes it easy to share stuff: We all know about the 70Mb workbooks that calculate NPV of a project or compare few different scenarios. Unfortunately that hunk of a file can’t be emailed CFO as she is globe-trotting and can’t run array formulas on her iPhone. On the other-hand Power BI files can be shared easily. You just publish and send the link or notification to the people. They can check it out on phone / tablet / computer and interact too.
- Consolidate data with Power Query: Ok, this is not just Power BI thing. With Power Query, we can consolidate files, stitch together tables and merge different sets of data. This means, no more manual tasks or laborious lookups.
- Visual scenarios with what-if parameters: You can use what-if parameters, slicers in Power BI to make interactive scenarios. This is so much better than using either scenario manager or changing cell values in Excel.
- Work on larger sets of data easily: Excel has physical limit of 1mn rows per spreadsheet. So if you are forecasting operations of a large company, chances are you have more than a million rows and hit the limit in Excel. Power BI has no such limits, so you can happily work on larger data-sets.
- Power BI is regularly improving: Almost every month Microsoft releases a new version of Power BI. This is good for people who are building things as you have new capabilities to rely on and improve user experiences.
Why finance people don’t need to learn Power BI?
- Power BI is not Excel: This is the big one. You can’t create everything you do in Excel with Power BI. Many things that finance people do in Excel like making statutory statements (balance sheets, P&L etc.) or creating scenarios or running monte-carlo simulations on forecasts are ridiculously hard to reproduce in Power BI. Power BI good for visuals, but all the heavy lifting must be done in Excel.
- Power BI doesn’t yet have many basic finance functions: Simple things like calculating NPV or Future value of a cash flow become a laborious mathematical exercise in Power BI. This is because the calculation engine of Power BI – Power Pivot, yet doesn’t have many finance functions.
- Power BI is very technical: Most finance people would learn how to work on finance & accounts in Excel. So Excel becomes a second language for them. But Power BI is not an extension of Excel. It is a deeply technical and complex software. So to get most of out it, finance people need to pick up technical skills like DAX, M and data modeling.
- Power BI is regularly changing: Let’s admit it. Nobody like change. As Power BI rolls out new version every month, you will be forced to stay on top to feel at home. New features come, screens change, buttons move around and options add every month. Knowing all this and keeping up can be hard.
My take – Learn Power BI anyway
I think Power BI is a valuable skill to acquire nevertheless. The reasons:
- Reuse the skills in Excel: Core components of Power BI – Power Query & Power Pivot can be used in Excel too.
- Learning new things is fun: Just as squats make your legs fit, learning new things keeps your brain fit.
- Power BI can open new doors for you: With each new skill you acquire, you will find new opportunities. It can help you find that exciting job or start a business.
- Power BI is free: Power BI Desktop software is free to download and install. There is heaps of free tutorials, examples and videos online (including on chandoo.org) to help you learn it. Even Excel is not free. So why not give it a try.
- Data skills are hot: Skills like how databases work, how to analyze data and how to tell stories thru visuals are very hot now. Power BI (and Excel too) makes it very easy to practice these skills.
Watch the debate with Danielle
I recorded a video with Danielle where we debated pros & cons of Power BI for finance people. Watch it below or see it on my YouTube channel.
What is your take on this?
Do you work in finance? What are your thoughts about Power BI? Share them below using comments. I want to hear what you think.
Want to learn Power BI? See these tutorials & articles
If the jargon of Power BI world is spinning your head, start with this simple explanation of what is Power BI.
For more examples and tutorials, check out the Power BI category on Chandoo.org or see this epic introduction video.
27 Responses to “Sum of Values Between 2 Dates [Excel Formulas]”
I would apply a filter and use function subtotal, with option 9. This way you can see multiple views based on the filter.
hey Chandoo, the solutions you proposed are very efficient, but if I wanted to be fancy I would do it this way .. the references are as your example workbook.
=SUM(INDIRECT("C"&(MATCH(F5,B5:B95)+4)):INDIRECT("C"&(MATCH(F6,B5:B95)+4)))
I like things simple:
=SUMIF(B5:B95,">="&F5,C5:C95)-SUMIF(B5:B95,">"&F6,C5:C95)
use something like: =SUM(OFFSET(B1,0,0,DATEDIF(A1,D1,"d")))
and have D1 be the date that I want to sum to.
In Excel 2003 (and earlier) I'd use an array formula to calculate either with nested if statements (as shown here) or with AND.
{=SUM(IF(B5:B95>F5,IF(B5:B95<F6,C5:C95,0),0))}
Note that I truly made this for BETWEEN the dates, not including the dates
I turned the data set into a table named Dailies.
I named the two limits StartDate and EndDate.
And used an array formula:
{=SUM((Dailies[Date]>=StartDate)*(Dailies[Date]<=EndDate)*Dailies[Sales])}
If I would still be using the old Excel I would do it as follows:
SUMIF($B$5:$B$95,"<="&H6,$C$5:$C$95)-SUMIF($B$5:$B$95,"<"&H5,$C$5:$C$95)
Works as simple as it is.
Regards
=sum(index(c:c,match(startdate,c:c,1)+1):index(c:c,match(enddate,c:c,1))
=sum(index(c:c,match(startdate,b:b,1)+1):index(c:c,match(enddate,b:b,1))
Great examples and thanks to Chandoo. You have simplified my work.
Hi! great tips I have found in your page, have you seen this
http://runakay.blogspot.com/2011/10/searching-in-multiple-excel-tabs.html
[...] I'm not sure I understand your question fully, but have a look at this: Sum of Values Between 2 Dates [Excel Formulas] | Chandoo.org - Learn Microsoft Excel Online [...]
Thank you! Thank you! Thank you!
=SUMIF(A2:A11;">="&B13;B2:B11)-SUMIF(A2:A11;"<"&A11;B2:B11)
awesome... thank yoo Chandoo!
which is most efficient and fast, if all are efficient ?
Thank you for this formula, I've just spent ages trying to find something to work on my data, I knew it would be possible! Don't care if others think there are easier/other ways to do it, you explained it so I understood it and could apply it to what I was doing so I'm happy!
The above said example is awesome for calculating values between dates,
can you pls let know how to calculate sale values if we have 10 sales boys for
ex: 1,rama
2,krishna
3,ashwin
4,naga
5,suresh
how much rama sale value between 1/jan/2015 to 10/jun/15
how much krishna sale value between 10/jan/2015 to 15/july/2015
i think you understood can you pls let me know the formula for how to calculate the sale between diffrent sale man sale value from master data file
Thanks,
Nagaraju
Hi
I have a list of people's names in column A, I have a list of dates in column B which records the dates they have been off sick, in column C I have either 1 if it is a full sick day or 0.5 if it is a half day.
What I would like to do is to add up the number of dates a specific person has been off within two dates.
For example, I want to look at my list of names and to find Joe Bloggs (column A), then add up all his sick days (column C). The start date will be in cell E1 and the end date will be in F1.
If this possible using SUMIFS?
List of names are in range A2:A100
List of dates in B2:B100
List of sick days (either 0.5 or 1 in C2:C100
The start date is in cell E2
The end date is in cell F2
Your help would be greatly appreciated.
Yes, with the help of SUMIFS you can have the solution.
Note: you need have an extra col. D2 where you will input Name of the person.
=SUMIFS(C2:C100,A2:A100,D2,C2:C100,">="&E2,C2:C100,"<"&F2)
Col. A Col. B Col. C Col.D Col. E Col. F
Name Date Sales
ABC 28-Jun-11 1 MNO 28-Jun-11 25-Sep-11
XYZ 29-Jun-11 0.5
MNO 30-Jun-11 1
PQR 1-Jul-11 1
Typo ERROR / Correction in formula:
Yes, with the help of SUMIFS you can have the solution.
Note: you need have an extra col. D2 where you will input Name of the person.
=SUMIFS(C2:C100,A2:A100,D2,B2:B100,">="&E2,B2:B100,"<"&F2)
Hi
I have a list of people's names in column A, I have a list of dates in column B which records the dates they have been off sick, in column C I have either 1 if it is a full sick day or 0.5 if it is a half day.
What I would like to do is to add up the number of dates a specific person has been off within two dates.
For example, I want to look at my list of names and to find Joe Bloggs (column A), then add up all his sick days (column C). The start date will be in cell E1 and the end date will be in F1.
If this possible using SUMIFS?
List of names are in range A2:A100
List of dates in B2:B100
List of sick days (either 0.5 or 1 in C2:C100
The start date is in cell E2
The end date is in cell F2
Your help would be greatly appreciated.
Viv
@Viv
Can you please post the question in the Chandoo.org Forums
http://forum.chandoo.org/
Please attach a file so that a specific answer can be delivered.
Thanks for this - it solved the problem that I was having. However can someone please explain to me why the "" needs to be around >= and <= as well as why we need to add & in order for the formula to work? Thanks in advance!
This formula works perfectly as well. Any ideas?: =SUM(INDEX(C5:C95,MATCH(H5,B5:B95,1)):INDEX(C5:C95,MATCH(H6,B5:B95,1)))
ikkeman had posted the same thing.
I am trying to sum total a range of cells between date ranges ie column n has $ amounts column d has the transaction dates ie 1/3/2015 or 25/3/2015 or 25/4/2015 column b has the text saying drp or distribution - reinv
In another cell I am trying to sum or total (in column n) with the value of a range of different dates (column d) that contain different text (column b) ie cell n48 is 50, n65 is 85, n165 is 36
with the dates ie cell d48 is 1/3/2015, d65 is 25/3/2015 and d165 is 25/4/2015
with different text that says drp or distribution - reinv ie cell b48 is drp, b65 is distribution - reinv, b165 is drp
If I wanted to sum the amounts between 1/3/2015 to 31/3/2015 with drp then the total would be 50. Also if I wanted to sum the amounts between 1/4/2015 to 30/4/2015 with drp the sum total would be 36 If I wanted to sum the amounts between 1/3/2015 to 31/3/2015 with drp and distribution - reinv the sum would be 115
What would the formula be for these different questions
hope you can help, it has been driving me nuts and cant work it out