יום חמישי, 11 בנובמבר 2021

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:


                                        Table CCDebit



 

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:

                                        Result of the formula’s first part


The second part:

OFFSET($A$1,1,1,COUNTA(CCDebit[Date]),1)



Returns an array of sums, similar in size to the first array:

                                    Result of the formula’s second part


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

                            

                                Result of  the multiplication

 

So, now that we have the final array, all that’s left to do is to SUM it.

Simple, isn’t it?






יום שבת, 6 בנובמבר 2021

Excel - How many candles are lit during the Jewish Festival of Lights

 

Excel - How many candles are lit during  the Jewish Festival of Lights

 

Hanukkah, the Jewish Festival of Lights is just around the corner, and we want to know how many candles we’re supposed to use during the eight days of the holiday.

In Hanukkah, which lasts 8 days, we begin with two candles on the first day. In each consecutive day we add one more candle, so on the eighth and last day of the holiday we light 9 candles.



 

Now, the question is: How can we know in advance how many candles are needed?



 

Actually there are (at least) four solutions:

 

 

                        Solution No.1: SUM an array of numbers

 

                        Solution No.2: Gauss’s Formula

 

 

                  Solution No.3: SUM of a virtual array (no data!!!)

 

  Solution No.4: SUM of a virtual array created by SEQUENCE