# 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

## יום ראשון, 24 באוקטובר 2021

### Excel - SUM instead of….COUNTIF

Excel - SUM instead of….COUNTIF

Consider the following case:

You have a number in cell C2.

The adjacent cells: D2:F2 also contain numbers.

The challenge: you want to find how many numbers in Cells D2:F2 are greater than the number in C2.

The most obvious solution would be with the COUNTIF function:

=COUNTIF(D2:F3,”>”&C2)

Picture 1: The COUNTIF solution

However, a simpler, shorter solution would be by using the SUM function:

=SUM(1*(D2:F2>C2))

Picture 2: The SUM solution

The (D2:F2>C2) part of the formula returns a list of TRUEs and FALSEs.

In order to convert them into numbers, we multiply the comparison’s result by 1.

So, instead of the TRUE, TRUE, FALSE we have a 3-member array: 1,1,0

Now the SUM functions adds them all together: 1+1+0 which yields the result: 2.

P.s.
This solution uses Excel 365.
Since this is an array formula, if you're using an earlier version, (2013,2016,2019...) you should press: CTRL+SHIFT+ENTER instead of: ENTER, when you end typing the formula.

That’s all.

I hope you found this “trick” useful.

Please subscribe to my blog in order to receive more amazing Excel posts. Thank you.

## יום רביעי, 20 באוקטובר 2021

התפוח בשפות שונות

בשפות אירופאיות שונות, הכינוי לפירות שונים מקורו בשתי מילים, האחת יוונית והשניה לטינית.
המילה ביוונית:
µῆλο(מֶלוֹ) והמילה בלטינית  umpom= עץ פרי (Pomona היתה אלת הפירות ועצי הפרי).

באיטלקית, למשל, אפשר למצוא את הפירות הבאים שבהם מופיע השורש הלטיני או השורש היווני:

 pommodoro תרגום מילולי: תפוח זהב, אבל בעצם: עגבניה mela תפוח melagrana רימון pompelmo פומלו mela cotogna חבוש

בצרפתית

 Pomme de terre תפוח אדמה pomélo פומלו (שילוב של שתי המילים: הלטינית והיוונית

באנגלית

 melon מלון watermelon אבטיח pomelo פומלו Pumpkin מקורה ב= πέπων היווני (סוג של קישוא)

בעברית

 מלפפון המקור ביוונית עתיקה:  = µηλοπέπων µῆλο-ν (מלון) הוא תפוח ו- πέπων  הוא סוג של קישוא ןמֶלו המקור ביוונית עתיקה: µῆλο מלופו שילוב של pom ו- µῆλο-ν

באופן מפתיע, גם בעברית ישנן מילים שמקורן בשורש הלטיני או היווני:

המלפפון שלנו מקורו ביוונית עתיקה= = µηλοπέπων כאשר  ν-µῆλο (מלון) הוא תפוח ו- πέπων  הוא סוג של קישוא

גם המֶלון מקורו, כמובן, ב- µῆλο וגם הפומלו: שילוב של pom ו- µῆλο-ν

1)     בעברית:         תפוח (עץ)– apple

תפוח אדמה = potato

תפוח זהב = orange

2)     ספרדית:        פומלו = pomelo

3)     בגרמנית:        apfel = תפוח

Apfelsin = תפוח מסין = תפוח זהב

4)     המלפפון באנגלית (Cucumber) הוא באיטלקית: אבטיח (Cocomero)

5)     הדלעת באנגלית: Pumpkin גם היא מקורה ב= πέπων היווני

6)     באיטלקית: pommodoro = תרגום מילולי: תפוח זהב, אבל בעצם: עגבניה

תפוח = mela

רימון = melagrana

פומלה = pompelmo

חבוש = mela cotogna

חציל = melanzana (במקור מפרסית/ערבית:  باذنجان)

או

מיוונית ביזנטית: melitzána (μελιτζάνα) בגלל צבע הקליפה השחורה: (µέλας)

## יום שבת, 16 באוקטובר 2021

### אקסל – מציאת שלשת הערכים הגדולים ביותר ברשימת ערכים

אקסל – מציאת שלשת הערכים הגדולים ביותר ברשימת ערכים

שתי שיטות למציאת שלשת הערכים המספריים הגדולים ביותר ברשימת ערכים, הצגה אופקית והצגה אנכית.

הסבר מלא בתמונה

ואם ברשותך אופיס 365, אפשר כך:

## יום רביעי, 1 בספטמבר 2021

### חמש שיטות למציאת הרבעון לפי תאריך

חמש שיטות למציאת הרבעון לפי תאריך

השנה, כידוע, מחולקת ל-12 חודשים. לצרכים פיננסיים ואחרים, מקובל לחלק את השנה לרבעונים. כל חודש שייך לרבעון, למשל: חודש פברואר (02) שייך לרבעון הראשון, חודש אפריל (04) שייך לרבעון השני, חודש ספטמבר (09) שייך לרבעון השלישי וחודש דצמבר (12) שייך לרבעון הרביעי והאחרון של השנה.

אנו כמובן יודעים לחשב בעל-פה לאיזה רבעון שייך תאריך מסוים. אבל כיצד אפשר לקבוע ב"אקסל" את הרבעון שאליו משתייך התאריך?

נניח שבתא C2 מופיע התאריך: 21/07/2013
כיצד נאמר ל"אקסל" למצוא את הרבעון שאליו משתייך התאריך?

לשם כך, נציג יותר מ- 5 שיטות המשתמשות ביותר מ-5 פונקציות שונות.

שיטה מס. 1: שמוש בפונקציה CHOOSE

תמונה 1: הפונקציה CHOOSE למציאת הרבעון

הפונקציה CHOOSE - בוחרת ערך מתוך טבלת ערכים [ארגומנט 2] לפי מספר מבוקש [ארגומנט 1]
הפונקציה מקבלת 2 ארגומנטים:
ארגומנט 1: Index Num - מספר שלם בין 1 ו-254.
ארגומנט 2 :   Value1, Value2, Value3…- רשימת ערכים (לכל היותר 254 ערכים), המייצגים ערך המקביל לארגומנט 1.

נשמע מסובך, אבל זה די פשוט:
לדוגמא: נניח שיש לנו מספר כלשהו (בין 1 ל-7) ואנו רוצים לבטא את היום בשבוע שהמספר הזה מייצג. כלומר: 1 - יום ראשון, 2 - יום שני, 3 - יום שלישי וכו'.
אז הנוסחא שלנו תהיה:
תמונה 2: הפונקציה CHOOSE למציאת שם היום בשבוע (1)

מכיון שערך ארגומנט מס. 1 הוא: 2, נקבל את הערך השניבארגומנט מס. 2, כלומר: Monday

תמונה 3: הפונקציה CHOOSE למציאת שם היום בשבוע (2)

וכעת, לבעיה שלנו: רצינו, כזכור, למצוא את הרבעון שאליו משתייך התאריך: 21/07/2013.
הנוסחא שלנו (המשתמשת בפונקציה: CHOOSE), מורכבת מ:
הארגומנט הראשון: MONTH(C2)
MONTH(C2)  - נוסחא המחשבת את החודש מתוך התאריך. תוצאת הנוסחא היא: 7.
הארגומנט השני: טבלה של 12 ערכים (המקבילה ל-12 חודשי השנה) אשר קובעת לאיזה רבעון שייך כל חודש:

תמונה 4: טבלת הרבעונים לפי חודשים

במקרה שלנו, חודש יולי (7) הוא במקום השביעי בטבלת הערכים. במקום זה בטבלה נמצא הערך: 3, לכן חודש יולי משתייך לרבעון השלישי בשנה.

תמונה 5: החודש השביעי שייך לרבעון השלישי

שיטה מס. 2: שמוש בפונקציה CEILING

תמונה 6: הפונקציה CEILING למציאת הרבעון

הפונקציה: CEILING - מעגלת מספר כלפי מעלה
היא מקבלת שני ארגומנטים:

ארגומנט 1: המספר שיש לעגל
ארגומנט 2: המכפלה שאליה רוצים לעגל.

הסבר:
הארגומנט הראשון הוא תוצאת הנוסחא: MONTH(C2)/3
כלומר, אנו מחלקים ב-3 את מספר החודש (7) והתוצאה:2.333333
הארגומנט השני הוא 1: אנו מבקשים מ"אקסל" שיעגל את התוצאה של ארגומנט 1 (2.33333) למכפלה הקרובה ביותר של המספר 1 - כלומר,למספר 3.
הפונקציה: FLOOR דומה מאד לפונקציה CEILING - אבל בעוד ש: CEILING מעגלת כלפי מעלה (כפי שמרמז שמה), FLOOR מעגלת כלפי מטה ואם ניתן לה את אותם ערכים בארגומנטים - התוצאה תהיה 2.

שיטה מס. 3: שמוש בפונקציה ROUNDUP

תמונה 7: הפונקציה ROUNDUP למציאת הרבעון

הפונקציה: ROUNDUP, בדומה ל: CEILING, מעגלת מספר כלפי מעלה.היא מקבלת שני ארגומנטים:
ארגומנט 1: המספר שיש לעגל
ארגומנט 2: מספר הספרות העשרוניות שרוצים לעגל.
הסבר:
הארגומנט הראשון הוא תוצאת הנוסחא: MONTH(C2)/3
כלומר, אנו מחלקים ב-3 את מספר החודש (7) והתוצאה:2.333333
הארגומנט השני הוא 0: אנו מבקשים מ"אקסל" שיעגל את התוצאה של ארגומנט 1 (2.33333) כלפי מעלה, למספר בעל 0 ספרות עשרוניות - כלומר,למספר 3.

כשם שהפונקציה FLOOR היא "אחותה התאומה" של הפונקציה CEILING, כך אפשר לומר שהפונקציה ROUNDDOWN  היא "אחותה התאומה" של ROUNDUP

שיטה מס. 4: שמוש בפונקציה INT

תמונה 8: הפונקציה INT למציאת הרבעון

הפונקציה INT מקבלת מספר ומעגלת אותו כלפי מטה למספר השלם הקרוב ביותר
הפונקציה מקבלת רק ארגומנט 1 : המספר שיש לעגל
הסבר על הנוסחא:
=INT((MONTH(C2)-1)/3)+1
למעשה, יש כאן חמשה שלבים:

1.      מוצאים את  החודש MONTH(C2) - והתוצאה: 7
2.      מחסירים 1 ומקבלים: 7-1=6
3.      מחלקים ב- 3 והתוצאה: 6/3= 2
4.      מהפונקציה INT מקבלים את המספר השלם הקרוב ביותר: 2
5.      מוסיפים 1 והתוצאה הסופית: 3  -  כלומר הרבעון השלישי
נשתמש ב-3 החודשים הראשונים, כדי להסביר את שלבי הנוסחא:
שלושת החודשים הראשונים (ינואר, פברואר, מרץ) מספרם הוא: 1, 2, 3
1.      אם נחסיר 1 – נקבל את המספרים: 0, 1, 2
2.      תוצאת חילוק המספרים: 0, 1, 2 ב- 3 היא: 0,  0.333, 0.666
3.      הפונקציה INT משלימה למספר השלם הקרוב ביותר (כלפי מטה), ולכן נקבל: 0, 0, 0
4.      כעת נוסיף 1 ונקבל: 1, 1, 1 - כלומר: שלושת החודשים הראשונים שייכים לרבעון הראשון.

שיטה מס. 5: שמוש בפונקציות MROUND ו- CEILING

תמונה 9: הפונקציות MROUND ו-CEILING  למציאת הרבעון

שיטה זו משלבת שתי פונקציות: CEILING  ו- MROUND
בפונקציה CEILING כבר השתמשנו בשיטה מס. 2. שם עִגַּלנו את המספר כלפי מעלה, למספר השלם הקרוב ביותר, כאשר הארגומנט השני היה: 1 - בקשנו מ"אקסל" לעגל את המספר כלפי מעלה למכפלה הקרובה ביותר של המספר 1.
כעת, נבקש מ"אקסל" לעגל למכפלה הקרובה ביותר של 0.5.
אם תוצאת הנוסחא MONTH(C2)/3 היא: 7/3 = 2.3333,
אזי "אקסל" יעגל את התוצאה ל: 2.50

הפונקציה MROUND(שגם היא משמשת לעִגּוּל מספרים) מקבלת 2 ארגומנטים:
ארגומנט 1: המספר שרוצים לעגל - 2.50 (תוצאת הפונקציה CEILING)
ארגומנט 2: המכפלה שאליה רוצים לעגל. אנו מבקשים מ"אקסל" שיעגל את התוצאה של ארגומנט 1 (2.50) למכפלה הקרובה ביותר של המספר 1 - כלומר,למספר 3.
יש לשים לב שהפונקציה איננה מעגלת כלפי מעלה, אם השבר קטן מ- 0.50 (!). לכן, השתמשנו בפונקציה CEILINGכדי להפוך את: 2.333 (7/3) למספר: 2.50 ובפונקציה: MROUND כדי לעגל את 2.50 ל- 3.

☼☼☼

לשיטה זו, שבה אנו משתמשים ב-2 פונקציות עגול, יש כמובן וריאציות נוספות, המניבות תוצאות זהות.
למשל:
שלוב של הפונקציות: ROUNDו- CEILING

תמונה 10: שלוב הפונקציות: ROUND ו-CEILING

שלוב של הפונקציות: ROUNDו- ROUNDUP

תמונה 11: שלוב הפונקציות: ROUND ו-ROUNDUP

ואפילו שלוב של הפונקציות "התאומות": ROUNDUP ו- ROUNDDOWN

תמונה 12: שלוב הפונקציות: ROUNDUP ו-ROUNDDOWN

לסכום, הדגמנו כאן מספר שיטות למציאת הרבעון בשנה לפי חודש. השתמשנו במגוון גדול של פונקציות ב"אקסל" בלווי הסברים.
מקוה שתדעו לנצל את הידע לשמושכם...

♣♣♣