I’ve been working on a secret project.
I call it the the Farmer Spreadsheet Academy and it just went public!
Step one of this new venture is a series of weekly farmer spreadsheet tip videos.
The first video is about the formula SUMPRODUCT.
Tip#1 SUMPRODUCT
I chose SUMPRODUCT for the first video because it was the first formula that blew my mind.
I had been doing all kinds of spreadsheet acrobatics to turn our weekly harvest projections into sales projections. Though they worked, they were bulky formulas that left room for errors to creep in.
Then I discovered SUMPRODUCT.
One simple formula cured one huge headache.
In this video, I illustrate how you can use SUMPRODUCT to easily calculate the value of a CSA vegetable basket or projected sales at a farmers market.
You can download the sample spreadsheets if you want to follow along at home!
If you enjoy the video please hit the thumbs up on the youtube page.
I’d love to hear whether you’re already using SUMPRODUCT or if it’s a new tool for your spreadsheets!
What spreadsheet tips would you love to see?
Leave you answers in the comments to this blog post or in the comments on the video’s youtube page.
Nice tutorial, thanks for making that. I noticed you used a couple of shortcuts (locking the cells and extending the formula to the end of the sheet); those little details would also be great to know.
Cheers
Thanks! I am planning on covering both those topics in future videos. I’ll probably be doing anchoring shortly, though I’ll tell you the secret right now!
In your formula, click on the cell reference or range that you want to anchor. Press F4, and it will reference the anchor by adding $ before each part of the reference. For example, B2:D2 would become $B$2:$D$2. Press F4v again and it only anchors rows (B$2:D$2). Press F4 again and it only anchors columns ($B2:$D2). Press once more, and nothing is anchored (B:D). Enjoy!
On a Mac, you need to push the key at the same time as you push the key.
Hey Dan,
The links to the sample spreadsheets are no longer available. Would you be able to make them available again?
I fixed the link!
You can also follow it here: http://bit.ly/FSA001
Thanks for pointing this out and happy planning!
Dan