Hi all,
Time for a personal + blog related update. Bring a cup of your favorite brew, sit back, relax and read on.
2 years in New Zealand…
Wow, it feels like just yesterday we stepped out of cold & windy Wellington airport with our bags & scared selves. But that was in July 2016. Just recently we completed our 2nd anniversary in New Zealand. These two years have been exciting, happy, interesting, emotional and often puzzling. It is not an easy thing to pick yourself up when you are 35 and move to another corner of the world. Highlights of our 2 year journey in New Zealand:
- Nakshatra & Nishanth (our kids) settled well in to new country & school, made friends and took everything in stride.
- Jo (my wife) started working after an extended break of 7 years. She found a few interesting projects and enjoying herself.
- We enjoy beautiful and breathtaking outdoors of NZ by going on walks, overnight tramps, bike rides, beach camping and road trips.
- We made new friends and meaningful connections.
- We bought a house last year and now feeling more at home.
- Despite high cost of living, we haven’t lost the plot. We stuck to our frugal ways and continue to make responsible, eco choices.
Not so great things in the last two years:
- Getting friends is hard: While kiwis are friendly & kind bunch, making good friendships has been tricky. Jo & I are very shy and reserved people (introverts). This made it doubly hard. Fortunately, we have a small circle of friends and it is only going to grow.
- I have become way too lazy: The first 6 months after move took us (Jo & I) by a lot of surprises like unusually cold homes, occasional depression of missing family & friends in India, getting used to new way of living etc. In this time, I focused more on personal life rather than my mission – making people awesome in Excel. At the end of 2016, when I finally felt like going back to old rhythm of creating content, sharing and helping YOU, I landed a 3 day per week Excel + Analytics contract job in NZ government. As I was feeling lonely working from home all the time, I thought doing consulting work in an office environment would be better than working from guest bed room. I assumed that I could still blog on Thursdays & Fridays. So off I went. But this drained too much of my energy and I craved for couch time than screen time. End result – I got way too lazy.
All in all, I am happy for this new adventure in our lives. New Zealand has been kind to us and widened our perspective. I am excited to celebrate two years in NZ and looking forward to next several years in this little corner of the world.
Excel School v2.0
As you may know, I run a few online, self-paced courses. This is how I make my living. Excel School, my most popular and significant course was originally designed in 2010 and later revamped in 2013. All these years, I kept adding new videos, but the foundation material is from 2010. Time for a revamp no? So I have been busy creating Excel School 2.0 since May 2018. I am happy to tell you that new version of my flagship Excel training program is now ready. I will be announcing the details next week and enrollments for Excel School 2.0 will open on 22nd of August (Wednesday), 2018.
What is Excel School?
Excel School is an online training program for learning Advanced Excel for data analysis & reporting related work. This is the program you want if your job involves using Excel at a professional level. So if you are an accountant, analyst, manager, administrator, coordinator, business owner or planner, then this is the course for you. In this program, you will learn:
- Module 0 – Introduction to Modern Excel
- Module 1 – Working with data
- Power Query to gather & clean data
- Tables for structuring data
- Dealing with bad data problems – several case studies
- Formatting data
- Conditional Formatting
- Module 2 – Analyzing data with formulas
- Referencing styles – structural, cell, named and 3D
- Common functions & operators
- Advanced formulas
- Statistical summaries
- Text analysis and processing
- Module 3 – Analyzing data with Pivot Tables
- Data model & relationships
- Setting up pivot tables
- Slicers & filters
- Grouping data in pivots
- Answering business questions with Pivot Tables
- Using GETPIVOTDATA
- Module 4 – Visualization using charts
- Rules for picking right chart
- Anatomy of an Excel chart
- Budget vs. Actual charts
- Conditional charts
- New chart types in Excel – histogram, box plot, treemap, forecasting, waterfall etc.
- Sparklines & light weight charting
- Dynamic charts with slicers, form controls and other techniques
- Interactive charting experiences
- Tons of examples & best practices
- Module 5 – Working with Excel productively
- Customizing Excel for your line of work
- Working with shapes & pictures
- Creating and re-using templates
- Workbook protection
- Form controls
- Shortcuts
- Data validation
- Module 6 – Creating dashboards
- What is a dashboard
- Process for creating dashboards
- Full length dashboard exercises (5 of them)
- Dashboard inspiration
- Class projects on various things
If all of this sounds exciting, stay tuned for the big announcement next week about how to enroll.
Note: if you joined Excel School in the last few months, you will be automatically upgraded to v2.0 (for free of course).
Blogging Schedule…
Enough with my laziness. It’s high time I got back to my old and creative self. So I am going to publish an editorial calendar and set some goals. This is what you can expect from Chandoo.org from now.
Blog focus: I want to focus on two things. Excel & Power BI. I will be creating content on how to use OR(Excel, Power BI) for awesome results at your work. The content formats will be same as before, i.e. – short tips, longer tutorials, examples of best practice, advice, commentary, personal stories and reader experiences. The content will be OR(text, video, downloadable files).
Blogging schedule: I want to stick to a schedule. That way I have a responsibility and you have certainty. I want to publish twice a week. I am still formulating my thoughts on this, but here is what you can expect.
- Power Mondays: Hence forth, Mondays will be Power Mondays. This is when I showcase an example or tutorial on Power BI / Power Query / Power Pivot.
- Free video class last Thursday of month: Our first masterclass on HR Analytics on YouTube Live is a huge hit. I will be streaming a live class on last Thursday of every month. Next class will be about Power Query for Accountants on 30th of August (Thursday).
- Revive classic blog posts: As of writing this post, there are more than 1,000 Excel, VBA, Power BI related posts on chandoo.org. Some of the classic content is written back when I was using Excel 2007 or 2010. I want to revive such classics by updating screenshots, workbooks and wording to match what is available in Excel 2016 / 2019 / Office 365. Expect to see 1-2 such posts every month.
- Videos: I want to spend more time creating videos. This is because technologies like Power BI (or modern Excel) are tricky to explain in text + image format alone. While I will continue to produce text+image style content for the blog, I will also be adding videos to my YouTube channel regularly. Make sure you are following it.
Blogging philosophy: Chandoo.org has been around for 14 years now. Ever since I started writing about Excel, my philosophy remained unchanged. I will continue to follow below guidelines:
- Write original, interesting and creative content
- Be generous with my knowledge and ideas
- Share free, unlocked, downloadable example files
- Encourage discussion among readers thru comments
How to stay updated?
There are many ways to stay on top of your Excel / Power BI learning thru Chandoo.org
- Subscribe to Free Chandoo.org newsletter.
- Bookmark the blog page and visit often. The link is https://chandoo.org/wp/posts/
- If you like reading blogs on an RSS reader, get full feed here: https://feeds2.feedburner.com/PointyHairedDilbert
- Or follow Chandoo.org on Facebook, Twitter or YouTube to be notified when something new is up.
Thank you
Before I wrap up, let me take this opportunity to say thanks. You continue to inspire and motivate me. I am thankful that you made me a partner in your journey to awesomeness. I am glad I can share my thoughts, feelings, knowledge, passion with you. Thank you for your readership and following.
24 Responses
I’d suggest simply using the subtotal function and filtering the data using the Win/Loss column. You get the same results and the formula is more comprehensible.
@John
That is one option.
There are times however when you want to see the whole data table or a filtered subset and still want to produce summary reports against an unfiltered field.
Is there a particular reason why you are using a comma and the unary (–) operator for the second array in the SUMPRODUCT formula? It seems to work the same if you were to string the arrays together using the asterisk (*). The advantage is that SUMPRODUCT treats the entire string of arrays as a single array.
@Mathew
Your correct, There is no difference.
I thought it may have been easier to explain this method.
Is there a way to do this on a large set of data? As in ~100,000 rows? When I try I get an error because the formula becomes too long. It says the max length of a formula is 8,192 characters. Excel 2010.
How do I incorporate a specific text within a cell for the second array. For instance, – -(C7:C13=”Apple”)
when I chose a specific text the formula does not work.
@RB
I am not sure what is the issue as if I use the sample data in the post the following work fine
Count:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)), –(C7:C13=”L”))
Sum:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)),(C7:C13=”L”)*(D7:D13))
You may want to check that there are no leading or trailing spaces in your list of Apples
I should have given a better explanation. Heres my situation. I have a column with cells filled with names like Column 1, Column 2, Pier 1, Pier 2, etc. If the cell just contained Pier and searched for that it works. But because it has other characters in the cell its not recognizing the pier. So how can I extract specific characters of a string of text in this formula?
Hopefully this was a better explanation
Hello-
This formula works pretty well for me except that it slow down excel and prevents some of my macros from working. I was wondering if there was a way to program this in VBA so that excel isn’t always trying to recalculate it. I would like to use a push of a button to get it to run then paste in a cell.
Thanks!
I am trying to sum filtered data in a column, but would want to ignore the negative values in the column. How to go about doing this?
@Akshay
Why not just add a filter to that column to only show the values greater than zero?
The negative values are required for reporting purposes, but their effect on the total is distorting the required output. Please advise.
@Akshay
I’d suggest making a post in the Chandoo.org Forums
http://forum.chandoo.org/
Attach a sample file to simplify the task
I have this working for counting and summing, however, I have a list and for the second array, I need a criteria. That is, I’m looking for b13:b200=”01.??.??” or =left((a1,2) or something like that. These types of criteria matches do not appear to work as I get a blank as a result.
Thanks!
@Bob
As your formula b13:b200=”01.??.??” looks like you are trying to check the first day of the month of the range
What about trying Day(B13:B200)=1
Hai Experts,
i understood this formula well and working fine in MS Excel 2013
but when the same am trying to place in google Spreadsheet it shows error as
“SUMPRODUCT has mismatched range sizes. Expected row count: 1. column count: 1. Actual row count: 2014, column count: 1.” and as a result #VALUE! Appears in cell.
Can anyone please help me how would i get it done in Google Spread sheet
or is there any other formula as a substitute for this.
Thank you very much.
thanks for providing this.. but why does excel keeps on prompting Circular referencing in cell D3?
@Vivek
I don’t know
I just downloaded the file and it is working fine and not showing that error
Goto the Formulas, Calculation Options Tab and check that Calculation is set to Automatic
What version of Excel and Windows are you using ?
I know that this forum is for MS Excel, but I am trying to help someone who is working in Google Sheets. The below formula works in Excel but Google Sheets returns:
“SUMPRODUCT has mismatched range sizes. Expected row count: 1. column count: 1. Actual row count: 39000, column count: 1.” and as a result #VALUE! Appears in cell.
This is the same problem asked by Srichirin above. Does anyone know if there is a formula for Google Sheets that will replicate what MS Excel does?
=SUMPRODUCT(SUBTOTAL(3,OFFSET($C$6:$C$39500,ROW($C$6:$C$39500)-MIN(ROW($C$6:$C$39500)),,1)),- -($C$6:$C$39500=H1),($D$6:$D$39500))
Trying to find a SUMPRODUCT formula that counts the word Closed by date for the last 7 days in a filtered list.
=COUNTIF(M:M,”>”&TODAY()-7) works ok for unfiltered count Column M contains Closure dates (blank if open) and Column L is Status Open or Closed
@ Terry
Please ask the question at the Chandoo.org Forums
https://chandoo.org/forum/
Please attach a sample file to ensure a quicker more accurate answer
I used this formula and worked like a charm! But, now I’ve been requested to use it but adding not one but two criteria in the same formula. For instance the sum I was doing added negative and positive numbers. I’ve been asked to use the exact same formula but adding that only positive numbers were considered… any idea on how to do this?
How exactly do you do sum filtered cells when two criteria are need not just one?
Thank you so much brother literally I have been struggling since morning to get the sum of the filtered category, however, after reading your blog attentively i got my solution, so thanks a lot once again.