Trending December 2023 # Sum By Color In Excel (Examples) # Suggested January 2024 # Top 16 Popular

You are reading the article Sum By Color In Excel (Examples) updated in December 2023 on the website Minhminhbmm.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested January 2024 Sum By Color In Excel (Examples)

Sum by Color in Excel

In this article, we will learn about Sum By Color in Excel. In Excel, we have a function for adding numbers. But there is no direct way to add the number by their background color. By this, we don’t need to sum the numbers separating the colored cells. We can directly consider all the cells in the formula and sum them as per their background color.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

This we can do when we have many cell numbers colored, and filtering the data is not suggested there.

How to Sum by Color in Excel?

Excel Sum by Color is very simple and easy. Let’s understand how to sum by color in Excel with some examples.

You can download this Sum by Color Excel Template here – Sum by Color Excel Template

Sum by Color in Excel – Example #1

Here we have data on some product and their sale. As shown below, column C has numbers with some background color.

Now, as we need to sum the numbers, so from the drop-down of SUBTOTAL Function, select 9, which is for sum.

And for reference1, select the complete range of column C, which we need to total as shown below.

The Output will be as given below.

Now apply the filter in the top row by pressing Ctrl + Shift +L.

Go to Filter by Color from the drop-down menu of it. Select any color; we have selected YELLOW, as shown below.

Once we do that, we will get the Output cell filtered sum as 190, as shown below.

We can also check the correctness of the applied SUBTOTAL formula by filtering the different colors.

Sum by Color in Excel – Example #2

There is another way to sum the numbers by their colors. For this, we will consider the same data as shown in example-1. Now copy the column’s cells with numbers and paste them into a separate sheet or in the same sheet in a different location.

Once we do that, selected cells will convert into the table form. And another menu will add with the name Design in the menu bar. Now Check and tick the Total Row option from the Table Style Options.

Once we do that, we will get the sum of cells at the bottom end of the column with a drop-down menu. Here we are getting a sum of 786.

Now from the drop menu of the total sum, select the Sum option as shown below.

By this, we enable the table to sum the filtered data as per colored cells. Now go to the top filter drop-down of the same column and select any color to get summed up from the Filter by Color option. Select any color; we have selected YELLOW, as shown below.

Once we do that, we will get the YELLOW colored filtered and the sum of the YELLOW colored cells in the below cell.

Sum by Color in Excel – Example #3

There is another method of summing the numbers by their color. VBA Marcos will do this. For this, we will consider the same data we saw in example-1. And we will add separate cells for each product name to get the sum of their quantity sold.

Now press Alt + F11 to enter Visual Basic for the Application screen.

Now go to the Insert menu and select Module.

This will open a new Module to write code. Now in the blank Module, write the code for enabling the sum by color function in Excel, as shown below. You can also use the same code to make some changes in that.

Close the complete window of VBA. Now go to the cell reference of Mobile, where we need to see the result and type the “=” sign. Now search and select the Sum Color function we created in VBA.

And select the reference colored cell and then select the range to get summed, as shown below.

The Result will be as shown below.

Once done, drag the formula to complete respective cells to see the result as shown below.

As we can see in the above screenshot, the sum of yellow-color cells is coming at 190, which the summed value is obtained in example-1 and example-2. This means that all the formulas and functions used in all examples are correct.

Pros

Sum by color from the SUBTOTAL function is the easiest way to get the sum result by color in Excel.

The process steps shown in example-2 take a little longer than in example-1, but it is still easy to apply.

We don’t need to filter the colored cells separately to get the sum.

Cons

Sum by color shown in example-3 by VBA coding takes time, and it doesn’t show the result if we paste the data in another file because it does carry the code with it.

Things to Remember About Sum by Color in Excel

If you are summing colored cells by VBA Coding, it is always recommended to save in the Macro enabled Excel; this will save the coding for future use.

These methods can use anywhere, irrespective of the data size. It is always recommended to use this method when we have a huge set of data, where if we filter the data to get the summed value may crash the file.

Workday In Excel (Formula, Examples)

WORKDAY Function in Excel (Table of Contents)

WORKDAY in Excel

Workday function in excel returns the Date, which is the official working day from the date which we feed into the syntax. This is quite useful for getting what would the working day date after selective day counts. As per syntax, we just need to select the date from which we need to count the number of the working day, then select how many days we need to count, and if there is any week off, we have optional. If we select today’s date with 5 days and 2 weeks off days, we will get the date of the same weekday.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

WORKDAY Formula in Excel:

Below is the WORKDAY Formula in Excel.

Explanation of WORKDAY Formula in Excel

A WORKDAY Function in Excel includes two mandatory parameters and one optional parameter.

Start_date: “Starting date of the project or any work”.

Days: The total number of days required to complete the work or project. This does not include weekends (Saturday and Sunday).

[Holidays]: This is an optional parameter. This section asks whether the days you have mentioned include any holidays. For this, you need to make a list of holidays separately.

WORKDAY Function in Excel by default excludes Saturday and Sunday as weekend days. If at all you need weekends for any other day, you can use chúng tôi function. For example: In the Middle East region, weekend days are Friday & Saturday. In these cases, we can use chúng tôi function instead of a normal WORKDAY Function in Excel.

How to Use WORKDAY Function in Excel?

You can download this WORKDAY Function Excel Template here – WORKDAY Function Excel Template

Example #1

Using the WORKDAY Function in excel, we can generate a series of dates even though we can generate by using the drag and drop option.

Step 1: Enter the one date on cell A2 as 12/Nov/2023.

Step 2: Now, in cell A3, apply the WORKDAY Function as shown in the below image.

=WORKDAY(A2,1)

The above formula takes the cell A2 as a reference and increases the date by 1.

Step 3: Drag the formula until cell A18.

Look at the formula here; 12/Nov/2023 is on Monday; we are increasing the day by 1. When we drag the formula, it will increment the date by 1 until 16/Nov/2023. If you drag one more time, it will jump to 19/Nov/2023 and excludes 17/Nov/2023 and 18/Nov/2023; those are weekends.

Similarly, in the next week, workdays are from 19/Nov/2023 to 23/Nov/2023, and weekends are 24/Nov/2023 and 25/Nov/2023.

Example #2

The project starting date and project duration date calculate the project ending date by using a WORKDAY Function in Excel.

Note: No holidays apply to these projects.

Step 1: Copy and paste the above data to an excel sheet.

Step 2: Apply the WORKDAY Function in column C starting from cell C2.

=WORKDAY(A2,B2)

Result is :

Example #3

Consider the above example data for this also. But here, the list of holidays is available to estimate the project ending date.

The list of holidays are:

Apply the same formula as shown in example 2, but here you need to add one more parameter, i.e. holidays.

=WORKDAY(A2,B2,$G$2:$G$21)

Result is :

Example 2 vs Example 3:

Now we will see the difference between the two examples.

In the second example for the first project-ending date is 8/8/2023, and there is one holiday, for example, 3, so the ending date increased by 1 day.

For the second project, the ending date is 30/01/2023, for example, 2 and example 3; there 5 holidays, so the ending date increased by 7 days because of the in-between weekend.

Example #4

Assume you are working in the Accounts Receivable team; you have a list of invoices and due dates against those invoices. You need to find the due days for those invoices.

=WORKDAY(A2,B2)

Result is :

Initially, the result looks like serial numbers. We need to change our formatting to make it correct.

Step 1: Select the entire range.

Step 2: Now press ctrl +1. It will open up a formatting dialogue box.

Step 4: Your result looks like the below one.

Things to Remember

If you want to use different weekends other than Saturday and Sunday, use chúng tôi function.

We can use only numeric values for the day’s argument.

The date and days should be accurate otherwise;, we will get the error as #VALUE!

If the date includes time, then the formula considers only the date portion and ignores the time portion.

If you supply decimal numbers, a formula will round down the value. For example: if you supply 10.6 days, then the formula treats this as 10 days only.

The Difference Between Sum Vs Sumx In Power Bi

There is still a lot of confusion about the difference between SUM vs SUMX in Power BI. This is key knowledge that users have to master because both functions can be used across different scenarios, but there are cases where one is more efficient than the other. You may watch the full video of this tutorial at the bottom of this blog.

I’m going to focus on one example here that would show the distinction between the two. But before I jump into that example, it is important to understand the difference between an aggregating function and an iterating function.

When it comes to DAX, there are two types of calculation engines – the aggregators and iterators.

Aggregating functions include SUM, AVERAGE, MIN, MAX and COUNT. Iterators, on the other hand, are functions that have an X at the end, like SUMX.

Iterating functions go through every single row of a table to add logic to each of these rows.

Aggregating functions look at the entire column left over after the context is placed in a formula. From there, a single aggregation is done for the entire column at a single time.

How is SUM used as an aggregator?

In this example, I’m going to compute for the Total Revenue in the sample data given.

The context is always important here. In this case, each specific date is the context of each specific result.

If I dig deeper into this table, it will show that there is a direct relationship flowing from the Date going into the Sales table.

Then if I look at the data working underneath this model, this is how everything fits together.

So the relationship is linked to the Order Date column here. Once specific dates from this column are filtered, the corresponding results are shown under the Revenue column.

From there, the SUM would just do one big calculation of the filtered results.

Now, I’m going to use SUMX on the same sample data so that you can see the difference. I can actually calculate for that Revenue without touching the Revenue column.

When the SUMX function is used, it will always ask for a table. Note that either a physical table or a virtual table can be used here.

To come up with the Revenue, I’m going to choose the Sales table. Then, I’ll place an expression, which can be a measure or a specific column from that table into this formula so that it can start running logic on every row. The expression, as explained here, returns the sum of an expression evaluated for each row of the table.

Since the sample data includes the Order Quantity, I’m going to use that here to get the Total. I’m also going to use the Unit Price.

Once I drag that formula into the report, the results are exactly the same.

Of course, they’re both showing the same results because they are both deriving data from the same two columns – the Order Quantity and the Unit Price.

Why use the SUMX if it yields the same result as the SUM anyway?

With the SUMX, the logic is applied not just to an entire column, but to every single row within that column. In fact, I could delete the Revenue column and still be able to retrieve specific results.

So imagine that logic being applied at every row. It multiplies the Order Quantity and Unit Price for the 1st row then saves that into the memory. It does the same thing to the 2nd row and all the other rows after that, saving each individual result.

This means that at the end, what’s being used to calculate the SUMX is not the physical data on the table, but the results saved in the memory.

Hopefully I was able to explain the main difference between SUM vs SUMX in Power BI, especially to those who are still getting the hang of what Power BI can really do.

SUMX will also be useful in cases where you have thousands to millions of rows. As long as the tables and columns referenced in your measures are there, using iterating functions would make the process more efficient.

All the best,

Sam

How To Change Terminal Output Color In Linux?

Introduction

The Linux Terminal is a powerful tool that allows users to interact with the operating system through the command line. However, the terminal’s default output color can be dull and unattractive. In this article, we will discuss several ways to change the terminal output color in Linux. We’ll cover the use of different commands and tools that can be used to customize the terminal’s color scheme, as well as some examples of how to use them. This guide is intended for Linux users who want to improve their terminal experience by changing the output color.

Using the “LS” command

One of the easiest ways to change the terminal output color in Linux is by using the “ls” command. The “ls” command is used to list the contents of a directory and has several options that can be used to customize the output. Here is an example of the “ls” command −

$ ls

The output will look like this −

bin@ home/ lib32@ media/ root/ sys/ vmlinuz@ boot/ chúng tôi lib64@ mnt/ run/ tmp/ vmlinuz.old@ dev/ chúng tôi libx32@ opt/ sbin@ usr/ etc/ lib@ lost+found/ proc/ srv/ var/ Using the “LS_COLORS” environment variable

Another way to change the color of terminal output on Linux is to use the “LS_COLORS” environment variable. This variable can be used to set the color scheme for the “ls” command. The variable can be set in the “.bashrc” file, located in the user’s home directory. To set the “LS_COLORS” variable, add the following line to the “.bashrc” file −

$ export LS_COLORS='rs=0:di=01;34:ln=01;36:mh=00:pi=40;33:so=01;35:do=01;35:bd=40;33;01:cd=40;33;01:or=40;31;01:mi=00:su=37;41:sg=30;43:ca=30;41:tw=30;42:ow=34;42:st=37;44:ex=01;32:*.'

The line above sets the color scheme for different file types. For example, “of” represents the directory and “1;35” represents the color code of the directory. Similarly, “ex” represents the executable and “01;32” represents the color code of the executable.

Using the “Tput” command

The “tput” command can be used to change the color of terminal output on Linux. The “tput” command can be used to set the color scheme of the terminal, as well as change the background and text color. Below is an example of how to use the “tput” command to set the text color to red −

$ tput setaf 1

The above command sets the text color to red. To change the background color, use the “setab” option instead of “setaf”. Here is an example of how to use the “tput” command to set the background color to blue −

$ tput setab 4 Using “Gnome-Terminal” ANSI escape codes

Another method to change the color of terminal output is to use ANSI escape codes. ANSI escape codes are standardized commands used to manipulate the behavior and appearance of text in a terminal or terminal emulator. For example, we can write a script that prints the red words “Hello world” on a green background using the echo command and then resets the colors to normal −

#!/bin/sh RED_ON_GREEN='33[31;42m' RESET='33[0m' echo "${RED_ON_GREEN}Hello world${RESET}"

“33” is the most platform-independent way of encoding an ESC character in the terminal, although a reference, “e”, can also be used on Linux. Also, it’s worth noting that the color settings aren’t encapsulated in any way. As a result, if we don’t restart them, they will bleed out of our script, which is undesirable in most cases. We can find the full list of color codes on Wikipedia.

#!/bin/sh RED_FG=`tput setaf 1` GREEN_BG=`tput setab 2` RESET=`tput sgr0` echo "${RED_FG}${GREEN_BG}Hello world${RESET}" tput command

The tput command is another option that can be used to change the color of terminal output on Linux. The tput command allows users to query the terminfo database and provides a convenient way to extract the escape codes we need. For example, we can recreate the script from the previous section using the tput command −

Conclusion

In this article, we have discussed various ways to change the color of terminal output in Linux. We have covered the use of several commands and tools such as the “ls” command, the “LS_COLORS” environment variable, the “tput” command, and Gnome-Terminal to customize the terminal’s color scheme. We also discussed using ANSI escape codes and the tput command to change the color of terminal output. Using these methods, you can make your terminal more visually appealing and easier to read. Remember to use commands with the correct syntax and options to change the color of terminal output on Linux.

Accounting Number Format In Excel

Excel Accounting Number Format (Table of Contents)

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Accounting Number Format in Excel Difference between Currency and Accounting Number Format in Excel

Accounting Format:

The difference between Currency and Accounting format is shown in the below screenshot.

How to Use Accounting Number Format in Excel?

In Microsoft Excel, we can find the accounting format under the number formatting group shown in the below screenshot.

Also, we can format the number in accounting format by choosing the dollar sign $ in the number group, which is also one of the shortcuts for the accounting number format shown in the below screenshot.

Example #1

Converting Number to Excel Accounting Format

You can download this Accounting Number Format Excel Template here – Accounting Number Format Excel Template

In this example, we will learn how to convert the normal number to accounting format. Consider the below example, which shows MRP, Selling Price of the individual product with local, national and zonal prices.

As we can notice that all the numbers are in general format by default, Assume that we need to convert the “Selling Price” to Accounting Number format along with Local, Zonal, and National selling prices.

In order to convert the number to Accounting format, follow the below procedure step by step.

First, select the column from E to H, where it contains the product’s selling price, which is shown in the below screenshot.

Once we choose the Accounting number format, we will get the output as ###, which is shown below.

We can notice that once we convert the number to accounting number format, excel will align the dollar sign at the left edge of the cell and display with two decimal points that we are getting the ### hash symbols.

Enlarge all the columns so that we can see the exact accounting format output, which is shown below.

In the below result, we can see that all the numbers are converted where we can see the Dollar sign$ in each left edge of the cell separated by commas and with two decimal numbers.

Example #2

To apply accounting number formatting, follow the below step by step procedure as follows.

First, select the column from E to H, where it contains the product’s selling price, which is shown in the below screenshot.

In the above screenshot, we can see the list of number formatting options.

Select the Accounting option so that it will display the accounting format, which is shown below.

As we can see, on the right-hand side, we can see decimal places where we can increase and decrease the decimal points, and next to that, we can see the symbol drop-down box, which allows us to select which symbol needs to be displayed. (By default, accounting format will select the Dollar Sign $)

Once we increase the decimal places, the sample column will display the number with selected decimal numbers which are shown below.

Example #3

This example shows how to sum the accounting number format by following the below steps.

Consider the example which shows sales data for the month of OCT-18.

As we can see that there are normal sales figures in the General number format. Now we will convert the above sales figure to accounting format for accounting purposes.

First, copy the same B column sales figure next to the C column, which is shown below.

Now select the C column and go to the number formatting group and choose Accounting, shown below.

As we can see, the difference that C column has been converted to accounting format with a Dollar sign with two decimal places and at the last column for negative numbers accounting format has shown the number inside the parenthesis.

Put the SUM formula in the C13 column, which will show the SUM in accounting format.

In the below result, we can see that the accounting format which automatically uses the dollar sign, decimal places, and comma to separate a thousand figures where we cannot see those in General number format.

Things to Remember

The accounting number format is normally used for financial and accounting purposes.

The accounting number format is the best way to configure the values.

For negative values accounting format will automatically insert the parenthesis.

Index Match Functions In Excel

In this tutorial, we’ll dive into the powerful Excel INDEX and MATCH functions, which are essential for manipulating and analyzing large sets of data.

We’ll start by exploring what these functions do and how they retrieve specific information from a table, and then we’ll write INDEX and MATCH formulas together as an alternative to the VLOOKUP formula.

We’ll also cover some practical use cases for INDEX and MATCH formulas.

Note: if you have Excel 2023 or later, or Microsoft 365 you should use the XLOOKUP function as this is easier and potentially more efficient.

Watch the INDEX and MATCH Video

Download the Excel File

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

Download the workbook. Note: This is a .xlsx file. Please ensure your browser doesn’t change the file extension on download.

How the INDEX function works:

The INDEX function returns the value at the intersection of a column and a row.

The syntax for the INDEX function is:

=INDEX(

reference

row_num

[column_num]

, [area_num])

In English:

=INDEX( the range of your table, the row number of the table that your data is in, the column number of the table that your data is in, and if your reference specifies two or more ranges (areas) then specify which area*)

*Typically only one area is specified so the area_num argument can be omitted. The examples below don’t require area_num.

INDEX will return the value that is in the cell at the intersection of the row and column you specify.

For example, looking at the table below in the range B17:F24 we can use INDEX to return the number of program views for Bat Man in the North region with a formula as follows:

=INDEX(

B17:F24

)

The result returned is 91.

On its own the INDEX function is pretty inflexible because you have to hard key the row and column number, and that’s why it works better with the MATCH function.

Note: You may have noticed that the INDEX function works in a similar way to the OFFSET function, in fact you can often interchange them and achieve the same results.

How the MATCH function works:

The MATCH function finds the position of a value in a list. The list can either be in a row or a column.

The syntax for the MATCH function is:

=MATCH(

lookup_value

lookup_array

[match_type]

)

Now I don’t want to go all syntaxy (real word 🙂 ) on you, but I’d like to point out some important features of the [match_type] argument:

The match_type argument specifies how Excel matches the lookup_value with values in lookup_array. You can choose from -1, 0 or 1 (1 is the default)

[match_type] is an optional argument, hence the square brackets. If you leave it out Excel will use the default of 1, which means it will find the largest value that is

0 will find the first value that is exactly equal to the lookup_value. The values in the lookup_array can be in any order.

Ok, that’s enough of the syntax.

In English and using the previous example:

=MATCH(

find what row Bat Man is on

in the column range B17:B24

match it exactly (for this we'll use 0 as our argument)

)

The result is row 2.

We can also use MATCH to find the column number like this:

=MATCH(

find what column North is in

in the row range B17:F17

match it exactly (again we'll use 0 as our argument)

)

The result is column 3.

So in summary, the INDEX function returns the value in the cell you specify, and the MATCH function tells you the column or row number for the value you are looking up.

INDEX MATCH Together:

The INDEX and MATCH functions are a popular alternative to the VLOOKUP. Even though I still prefer VLOOKUP as it’s more straight forward to use, there are certain things the INDEX + MATCH functions can do that VLOOKUP can’t. More on that later.

Using the above example data we’ll use the INDEX and MATCH functions to find the program views for Bat Man in the East region.

=INDEX(

the range of your table

replace this with a MATCH function to find the row number for Bat Man

replace this with a MATCH function to find the column number for East

)

The formula will read like this:

=INDEX(

return the value in the table range B17:F24 in the cell that is at the intersection of

, MATCH(

the row Bat Man is on

) and, MATCH(

the column East is in

)

The formula looks like this:

=INDEX(

$B$18:$F$24

,MATCH(

"Bat Man",$B$18:$B$24,0

), MATCH(

“East”,$B$17:$F$17,0

))

So why would you put yourself through all that rigmarole when VLOOKUP can do the same job.

Reasons to use INDEX and MATCH rather than VLOOKUP

1) VLOOKUP can’t go left

Taking the table below, let’s say you wanted to find out what program was on the Krafty Kids channel.

VLOOKUP can’t do this because you’d be asking it to find Krafty Kids and then return the value in column B to the left, and VLOOKUP can only look to the right.

In comes INDEX and MATCH with a formula like this:

=INDEX($B$33:$B$40,MATCH("Krafty Kids",$C$33:$C$40,0))

And you get the answer; ‘Mr Maker’.

Notice only the Programs column (B) was referenced in INDEX’s array argument? This means we can omit INDEX’s column number argument as there’s only one column in the INDEX array.

2) Two way lookup

The table below has a drop down list in B1 that enables me to choose the Sales Person from the table, and a drop down list in A2 for the region. In B2 I’ve got an INDEX + MATCH formula that returns the sales that match my two criteria.

=INDEX(A4:J10,MATCH(A2,A4:A10,0),MATCH(B1,A4:J4,0))

Note: An alternative is to use a VLOOKUP and replace the hard keyed column number with a MATCH formula like this:

Ways to improve these formulas:

1) Use named ranges instead of $C$33:$C$40 etc. to make formulas more intuitive and quicker to create.

2) An alternative to using a named range is to convert the data to an Excel Table whereby Excel automatically gives the table a named range.

3) If there is nothing else in the columns other than your table you could use column references like this C:C which will search the whole column.

Update the detailed information about Sum By Color In Excel (Examples) on the Minhminhbmm.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!