Example can be downloaded here.
Soon Excel 2007 will be released either as a standalone program or as part of
Office 2007. I had a taste in September of 2005 when the Excel developers
showed us MVPs a pre-release version that was buggy but seemed very interesting.
Later when I installed the beta I was less enthusiastic and I felt that
the lack of customization was not good. Having used it quite extensively during
the summer with a pretty stable beta release I once like it a bit more.
The grievances are still there with regards to the "ribbon" but there are so
many improvements that I quite like it.
There are some new functions and they are very usable, SUMIFS and COUNTIFS
let you sum and count using multiple criteria.
I thought I would show how to use an advanced 3D formula with SUMIFS
The basics is that we a have a workbook with monthly sales for 4 product
lines (Product A, B, C and D) in 4 different regions (USA, CANADA, EU and ASIA),
A2:A17 holds the regions, B2:B17 the products and C2:C17 the sales, that is
identical in all sheets. The sheets are named after each month of the year as
in Jan, Feb etc. There is also a Summarize sheet that holds all the formulas and
named ranges.
I want to be able to show the sales quarterly so we will start by using the new
very clever Name Manager to create 4 defined names called Quarter1, 2, 3 and 4.
To keep it off view for design reasons I put them in T1:W3 so T1:T3 is called
Quarter1, U1:U3 is called Quarter2 and so on.
Here's a screen capture of the name manager
The syntax of SUMIFS is
SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2…)
So if we did it for region USA, Product_A and worksheet Jan it would look like
=SUMIFS(Jan! C2:C17,Jan!A2:A17,"USA",Jan!B2:B17,"Product_A")
The problem is that we can’t use a regular 3D expression like this
=SUMIFS(Jan:Dec!C2:C17,Jan:Dec!A2:A17,"USA",Jan:Dec!B2:B17,"Product_A")
Since it will return a #VALUE! Error. This is why we
use the other 2 functions as well, INDIRECT will return the arrays that we’ll
need and
SUMPRODUCT will sum the different arrays.
Now to the formula, it will basically look like this when it is done:
=SUMPRODUCT(SUMIFS(INDIRECT("'"&{"Jan";"Feb";"Mar"}&"'!C2:C17"),INDIRECT("'"&{"Jan";"Feb";"Mar"}&"'!A2:A17"),
"USA",INDIRECT("'"&{"Jan";"Feb";"Mar"}&"'!B2:B17"),"Product_A"))
A good tip when creating complicated formula is to build
them like using LEGOS.
You start with one part, make sure it works before you move to the next part
Since that is not a very resourceful way of using multiple
conditions we would be better off using cell references where we would put the
criteria instead.
Since I would like to get something similar to a small table showing the
different sales for different quarters like this
So instead the formula will look like this:
=SUMPRODUCT(SUMIFS(INDIRECT("'"&INDIRECT($A5)&"'!C2:C17"),INDIRECT("'"&INDIRECT($A5)&"'!A2:A17"),
$A$2,INDIRECT("'"&INDIRECT($A5)&"'!B2:B17"),B$4))
Where A5 holds Quarter1, A2 holds USA and B4 holds Product_A. When done for
region USA with all 4 products it will look like this:
Anyone who has worked with long complicated formulas will know that when using
the formula bar it used to be very annoying with the
formula swelling over and covering the top rows making it very hard to edit any
cell in the top rows. Excel 2007 has fixed that, it will not
fill over into the sheet area and you can instead change the size of the formula
bar.
I have also used the new and improved Conditional
Formatting where you can add chart like colored data bars to show the individual
values compared to each other.
Finally, this is how it looked in the end
