Excel - 4 methods to SUM daily transactions
Your credit card company sends you each month your monthly statement balance.
you can see how much you spent each day.
But sometimes you have more than one transaction per day.
Is there a simple method in Excel by which you can find the answer to this
question?
In this post I’m going to demonstrate 4(!) methods. The first three ones are
quite common and well-known by the average Excel user. However, the fourth and
last is my original invention.
Since I wanted to show a dynamic solution, I am using a Table instead of the more
commonly used cell ranges.
The table’s name is CCDebit, and it has only 2 columns:
Date and Sum, as can be seen in the following picture:
OK, so let’s get down to business.
The first method uses only one function: SUMIF
Method 1 – SUMIF
The second method is a “variation” on the first one. Instead
of SUMIF we use SUM and IF:
Method 2 – SUM+IF
Method 3 uses the SUMPRODUCT function
Method 3 – SUMPRODUCT
The fourth and last formula is the most complicated one (My
own invention). It combines 5(!) functions as can be seen in the picture:
Method 4 –
Five Functions
Explanation:
The formula consists of 2 parts. Each part creates an array, and then both arrays are multiplied to create a single array. Then this array is SUMmed by the SUM
function which gives us the final result of the formula.
The first part:
ISNUMBER(MATCH(CCDebit[Date],$G$1,0))
returns an array of TRUEs and FALSEs:
TRUE if the argument’s date ($G$1) was found
in the [Date] column of the table,
and FALSE if that date was not found:
The second part:
OFFSET($A$1,1,1,COUNTA(CCDebit[Date]),1)
Returns an array of sums, similar in size to the first array:
Now, the multiplication of both arrays yields a new array in
memory which holds only the sums which belong the parameter date:
multiplication of FALSE (=0) by any number yields 0, of course. And the result
of multiplication of TRUE (=1) by any number, is the number itself
So, now that we have the final array, all that’s left to do
is to SUM it.
Simple, isn’t it?
