Have you encountered creating a report in MS Excel in which
you want to total the values but only based on a certain condition? For example
you have the following worksheet:
We’re going to make a horizontal grand total of the no. of
patients of the doctor on a given day(e.g. day 1); the first instinct would be
to create a formula and manually add each cell like in the figure above, also
totaling the amount of services per doctor on a given day the same step will
have to be done. While this works, imagine we’re going to do 1 month statistic,
it would be a excruciatingly tedious, not to mention error prone (if we missed a
day or we’re going to insert another column in between) to do the same step.
But hey, it’s a point click world and everything should be automatic(wishful
thinking) and we’re talking about Excel,
programs are built on top of this, there has to be a better way.
My last resort was to manipulate it using VBA, but before
that I attempted to install a Conditional Sum Wizard add-in unfortunately I didn’t
have the MS Office CD. Resisting the urge to do it in VBA, I researched it not
on Google but the help file and got rewarded by a not so user-friendly term
called formula array.
According to the help:An array formula can perform
multiple calculations and then return either a single result or multiple
results. I haven’t touched with multiple result as my concern is the
former, let’s modify the formula previously to
After entering the formula, instead of pressing the
<ENTER> key, we press CTRL+SHIFT+ENTER to convert our formula into a
formula array otherwise the formula will be undefined (i.e. #VALUE!.) The formula array calculated only the values
with an explicit condition (ie:only columns == “# of Patients”), simple and
sweet. We didn’t have to worry about the other column being included in the
calculation and even if we add another column(e.g. the 30 day stat ) we only
have to modify our range and press CTRL+SHIFT+ENTER to recalculate. And totaling the other column, involves just
plain copy and paste of the formula, and change the condition. The Final Result:
Hope this proved helpful as it did for me! 