## Doing the “total spend this year”, “total spend this month” shuffle using SumIFS

Published on September 23, 2014 by in Excel | Case Studies, Excel | Excel for Small business, Excel | General

Crunchin’ da numbers

Recently I went in to do some training with a new manager who had been tearing his hair out with his Excel sheet.

The spreadsheet had already been set up for him and the previous incumbent had done some interesting array formula variations using If and Sum – and it worked fine actually. The only problem was that this new user had no idea how to amend or fix this formula. He need a-rray of sum(ifs)shine…sorry.

I decided to show him how to use the SUMIFS function to do exactly the same thing – only easier and no fancy CSE (Ctrl-Shift-Enter) moves as required to create an array formula….

The only data he really required was to show the spend to date for the year (and you can see that formula in the Spent Year to Date sheet. I could have set it up as a table but I wanted to make sure he understood what we had done here so 5000 rows allows lots of room for expansion.

Couple of things to note:

1. All the criteria ranges have to be the same “height” i.e. 4:5000. Because alas, the SUMIFS won’t work otherwise…
2. All the criteria ranges are fixed in this question (i.e. I used F4 to fix them) but the criteria1 is not because I needed that to change as I copied it down.
3. You can check the answers by using the Filter option (click in list – Data – Filter)

Of course I got home and found myself idly wondering – well what if he needed the months as well. So I thought I’d experiment with that – as you do…

So I found I had to add another column to extract the month from the date which I added to the Month column

=TEXT(A4,”mmmm”)

Then in the Spent Month by Month sheet, I put the months across the top and entered the following formula:

=SUMIFS(‘Procurement list’!\$D\$4:\$D\$5000,’Procurement list’!\$B\$4:\$B\$5000,\$A4,’Procurement list’!\$E\$4:\$E\$5000,’Spent month by month’!B\$3)

A quick way to enter the formula is to:

1. Highlight the entire sheet (B4:M24 in the Spent Month by Month sheet)
2. Enter the formula in the white cell (because I started highlighting at the top, this is where I had a white cell)
3. Press Ctrl and Enter
4. This will enter the formula in the entire highlighted area…

## Excel Products for Sale

Here are products from some of my favourite Excel authors and bloggers. Please note these are affiliate links so I do earn a small commission from every sale.

## MrExcel.com

Mr. Excel (aka Bill Jelen) is one of the Excel gurus. I love his stuff. He also has a slightly bonkers delivery (What! Excel! Fun!)  that I thoroughly enjoy. And of course anyone who calls his Excel publishing book company Holy Macro! gets my vote.

## MyExcelOnline.com

### Xtreme Pivot Tables course

This is a very comprehensive course on pivot tables. It includes videos and workbooks. There are over 200 hours of videos on it and it takes you from having no knowledge of pivot tables to being a power user. If you know everything in this course, you know an awful lot about pivot tables. John explains the concepts in bite size chunks so they are easy to follow and even provides a tutorial spreadsheet so you can track your progress. This would be money well spent. If you would like to become the go-to person in your organisation for pivot tables, this would be an excellent starting point.

He also has a useful Pivot Table webinar…

This is also a useful chart helper.

## Debra Dalgleish – Contextures.com

Master 30 Excel functions in 30 days –This is a rather brilliant e-book which covers 30 functions including Vlookup,Match, offset. It includes an Excel file, e-book and links to videos that shows you how to do it – all for the princely sum of \$10. What I particularly like about it are her descriptions, explanations and what the limitation of each function is. Highly recommended. If you want to upgrade your Excel skills this is a very worthwhile investment.

#### Excel UserForms for Data Entry

The Excel UserForms for Data Entry ebook kit will take you through the steps to build a UserForm that stores data on a hidden worksheet. No programming skills are required — everything is explained in simple steps, with written instructions, screen shots, videos and workbooks.