MS Excel Archives - Excel Point Nepal Pvt. Ltd.

This Excel formula uses the SUMPRODUCT function to calculate the sum of sales from a table, SalesTbl, where the month of the date matches "Jan". Let’s break it down step by step: Formula: excelCopy code=SUMPRODUCT(SalesTbl[Sales],--(TEXT(SalesTbl[Date],"mmm")="Jan")) Explanation: SalesTbl[Sales]: This refers to the column in the SalesTbl table that contains the sales figures. It acts as an array of numbers. SalesTbl[Date]: This refers to the column in the SalesTbl table that contains the dates associated with each sale. TEXT(SalesTbl[Date],"mmm"): The TEXT function converts the date values into a text string representing the month in a three-letter format (e.g., "Jan" for January, "Feb" for February). TEXT(SalesTbl[Date],"mmm")="Jan": This compares each converted month to the string "Jan". The result is an array of TRUE or FALSE values, where TRUE corresponds to dates in January and FALSE to other months. --(TEXT(SalesTbl[Date],"mmm")="Jan"): The double negative -- converts the TRUE and FALSE values into 1 and 0, respectively. This is necessary because SUMPRODUCT works with numeric arrays, not logical values. SUMPRODUCT(SalesTbl[Sales],--(TEXT(SalesTbl[Date],"mmm")="Jan")): SUMPRODUCT multiplies corresponding elements of the two arrays: The first array is the sales data: SalesTbl[Sales]. The second array is the numeric representation of whether the month is January: 1 for "Jan" and 0 for other months. The multiplication results in: The sales value when the month is January (SalesTbl[Sales] * 1). 0 when the month is not January (SalesTbl[Sales] * 0). Finally, SUMPRODUCT adds up all these results to give the total sales for January. Example: DateSalesTEXT(Date,"mmm")TEXT(Date,"mmm")="Jan"--(TEXT(Date,"mmm")="Jan")Product (Sales * 1/0)01/01/2024100JanTRUE110002/01/2024200JanTRUE120001/02/2024150FebFALSE00 Result: 100+200=300100 + 200 = 300100+200=300. Summary: The formula calculates the sum of SalesTbl[Sales] for rows where the month in SalesTbl[Date] is January.

Category: MS Excel

SUMPRODUCT Function in Excel: A Step-by-Step Guide for Beginners

Excel Data Cleaning & Visualization Course

Advanced Excel Single Class (Online)

Quick Links

Quick Links

Contact Us

Map

Our Visitors