Posted by: bwoof | January 4, 2010

Excel and SUMIF statements

Excel is an amazing tool…if you know what you’re doing.

Tonight Laura and I tried to figure out budget sheets and she managed to find a sample file, a workbook with lots of worksheets. The guy who designed this one is crazy for details and all the sheets talk to each other and auto-update. Very nice.

However, it took a while for me to reverse-engineer one column in a table that seems to aggregate all the budget entries of a certain category (i.e., entertainment, or clothing, or food), and then adds them up, eventually for the purpose of comparing actual expenses to budgeted expenses.

Like I said, this guy likes details.

So, not to be outdone, I snooped in and out of various cells until I finally saw a pattern. It was a SUMIF formula, and having never before tried that function I went to the Help files, explored in there, and eventually found the answers.

Here’s what I learned:

Step 1: You’ll need standard names for your categories so that later on you can go and collect info in categories, or buckets at I call them.

Step 2: Enter your budget expenditures

Step 3: Make a table into which you’ll enter formulae that will collect and sort the info that you entered. Here’s how ‘Neil’ does it [Neil is the faceless fellow whose example file Laura scooped from the internet via a google search]

Step 4: Enter the magic formulae and then watch as Excel collects budget entries as per category, adds them up, and then totals them. The trick is the SUMIF function.

While this may appear confusing to some, it’s now perfectly clear to me. A new learning for the day!

Grateful for:

  • help files
  • a tenacious spirit which thrives in the face of a challenge, especially a pesky Excel thing
  • West Jet, only this time I’m sad that it took a daughter away…in the wrong direction
  • a wonderful Christmas season in every way possible
  • a teacher’s generous and positive attitude which just goes to show that it pays to think of others first and not yourself…and when people do that the blessings abound
  • Dr. Smilie and his amazing work on J’s teeth. Thank you God for a quick thinking receptionist who knew how to turn a wonky week of appointments into something surprisingly good
  • a great quote from this site

Curious about:

  • 7 new applicants coming to school…will we do a great job helping them transition? Will they be successful?
  • where will the donated snowboard jacket find a home?
  • what is a teen thinking when he says he needs to leave the classroom to go to the bathroom but then takes a circuitous route from there to the variety store a city block east, and then to the Dollar Store three city blocks west (I kid you not!!!), and then back to the school via a back entrance, then up the stairs, then down the stairs, and then he still didn’t make it to the bathroom or back to the class. Tell me please….what’s the logic? “I was thirsty” is the apparent answer…but I still don’t get it.
Advertisements

Responses

  1. Been looking for some ideas for getting good results in google with my blog and have found a few new ideas to try.

    Finding the right theme and plugins seems to be hard as there are so many options available.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: