How to enter the same data/formula in multiple cells at once in Excel?
While using Microsoft Excel, you may need to enter same values such as text, data in multiple cells, or enter same formula into an entire column at once in case of avoiding typing them one by one. In this tutorial, you will learn how to quickly enter same data or formula in cells at once.
Enter same data in multiple cells at once with Ctrl + Enter in Excel
Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!
Save 50% of your time, and reduce thousands of mouse clicks for you every day!
For entering same data in cells, please do as follows.
1. To select multiple cells which you want to enter same data by holding the Ctrl key.
2. After selecting, please press the Space key, and the last selected cell is in the editing mode.
3. Type the dada or text in the cell, and then press Ctrl + Enter keys simultaneously.
Now all selected cells are entering with same content. See screenshot:
Note: If there are contents in the selected cells, it will replace all contents of selected cells with the same data after press the Ctrl + Enter keys.
Enter same formula into an entire column with enter key in a table in Excel
Supposing you have data in column A and B, and you want to sum these data in column C with the Enter key. Please do as follows.
1. Select cell C2 and press Ctrl + T keys at the same time.
2. In the popping up Create Table dialog box, select the data range you want to sum, and click the OK button. See screenshot:
Note: If there is data header in your range, check the My table has headers box.
3. Select the column B and right click it. Then select Insert > Table Column to the Right in the right-clicking menu.
4. You can change the header name of the new column by selecting it and entering a new name in the Formula Bar, and then press the Enter key.
5. Select the first cell of the new column, here I select the cell C2. Enter the formula in this cell, and then press the Enter key.
After pressing the Enter key, all cells in this specified column are populated with same formulas at once.
If you’ve already entered a number in a cell, or a group of cells, what’s a quick way to add something to that amount? Here’s how you can add number to multiple cells in Excel.
Daily To Do List
In this example, I keep track of my To Do list in a workbook, and one of my items is “Daily Admin tasks”. Sometimes, I start the day by answering client emails, posting links to my latest blog post, and doing the accounting for the previous day’s sales.
So, I enter the time spent – 0.75 hours – and move on to the next task.
Later in the day, I might spend another 45 minutes on Admin tasks, so I want to add that to the previous amount, in cell D5.
If it’s late in the day, it might be difficult to add 0.75 + 0.75 in my head (or early in the day, if I haven’t had my coffee yet!)
Use Paste Special
One way to do this, and avoid basic mistakes in arithmetic, is to use Paste Special – Add.
- Type the number in a cell, and copy that cell.
- Then, use Paste Special – Add, to paste that amount into another cell.
That technique works well, but it takes a few steps – and that adds more time to my Admin tasks!
Use a Macro to Add Amounts
To make the job easier, I created a couple of macros that add numbers to selected cells. There is a link to the download page, at the end of this article.
- Macro 1 adds a set number to the selected cells.
- Macro 2 asks you to enter a number, then adds that number to the selected cells.
In the macro code, I use 7 as the set number, and the default number, because I have several weekly tasks. Once they’re completed, I add 7 to the date, to move them to next week’s schedule.
You could change the code, so the set/default number is something that you use frequently.
Video: Add Numbers to Multiple Cells in Excel
Watch this video to see how to use the Paste Special command, and see how to modify the macro code, to change the numbers.
Download the Sample File
To see how the macros work, and get the code to use in your own files, you can visit my Contextures website. You’ll find the instructions and sample file on the Add Number to Multiple Cells page.
The zipped file is in xlsm format, and contains macros. Enable macros when you open the file, if you want to test the code.
5 thoughts on “Add Number to Multiple Cells in Excel”
thank u, easily i have solve my quire through you video.
Can you help me to define formula for Excel?
ex. my cell has 10 8 9 7 3 5 6 9 4 condition x
I need formula to add how many number from the left based on condition.
if condition is 3 then add 10+8+0
if condition is 5 then add 10+8+9+7+3 etc
Thank you for help
hay thanks it worked for me……
I have used your macro to add 1 to selected cells, but it does not work if there is the numbers are filtered, also Ctrl+Z does not work after running the macro.
Can you please have a look and assist.
Dim ws As Worksheet
Dim rngSel As Range
Dim rng As Range
Dim Num As Double
Dim i As Long
Dim j As Long
Dim lAreas As Long
Dim lRows As Long
Dim lCols As Long
Dim Arr() As Variant
Set rngSel = Selection
For Each rng In rngSel.Areas
If rng.Count = 1 Then
rng = rng + Num
lRows = rng.Rows.Count
lCols = rng.Columns.Count
Arr = rng
For i = 1 To lRows
For j = 1 To lCols
Arr(i, j) = Arr(i, j) + Num
rng.Value = Arr
Thank you, I looked all over to find this solution. Thank you!
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Copying cells and pasting in multiple cells is very easy in Microsoft Excel. We can copy a single cell and select the target range of cells to paste. We can do this manually, using built in commands available in excel or we can also do it using shortcut keys.
Copy Same Value in Multiple Cells
You can enter same value in multiple cells simultaneously, it is very useful to fill range of cells at the same time.
- Select the range of Cells
- And enter required text to have in the Cells (this will be keyed in active cell)
- Press Ctrl+ Enter keys to repeat the same text in multiple cells in Excel
Select the Range
Enter Cell Value
Copy using built in commands
- Select the Cell which you would like to copy
- Right click on it and select Copy from command from Cells contextual menu (or, in Home Tab in Ribbon menu)
- Now select the range of cells which would like to repeat the same value
- Again, right click on it and select Paste command from contextual menu
- Press ESC key to disable copy cut mode
Copy Using Standard Shortcut Keys in Excel
- Select the cell which would like to copy
- Press Ctrl+ C keys to copy the Cell
- Select multiple cells, which is your target range of cells
- Now press Ctrl+ V keys to paste
Copy using Auto fill Method
If you want to copy the cell into the cells which are adjacent to the source cell. Then you can use this method.
- Select the Cell which you would like to duplicate
- Hover your mouse cursor at the bottom right corner of the cell
- You can see a small plus symbol
- Hold that with your mouse and drag down on to the destination/target range
Share This Story, Choose Your Platform!
Leave A Comment Cancel reply
You must be logged in to post a comment.
Excelx.com is a Excel Blog by PNRao to provide latest tips of Excel and help you to Quickly Learn and up-to date with Excel Concepts. We help you to know – how to do things in Excel with Clean and Easy Excel Tips, Shortcuts, Reference, Tutorials and New and Quick Tricks and Methods.
We Provide Free Online Tutorials, Formulas, Templates, Dashboards and Macros to excel in Microsoft Excel. We cover basic to advanced concepts and share creative ideas, Excel tips and Shortcuts with Related Example Files. You Can refer verity of examples covered on latest tools and techniques in Microsoft Excel Applications.
I was wondering how to add the same drop-down value (e.g. Yes or No) to multiple cells in the column at the same time? I tried selecting individual cells that I need to fill with the same value using Ctrl, and then selecting the value from the drop-down menu, but it only fills the single highlighted cell.
Is there a quick way to select cells and then add the same drop-down value simultaneously to all of them?
I’m using Excel 2013.
5 Answers 5
Place the drop-down in the top cell. Then select the block of cells (including the top cell) with either the ARROW keys or the mouse and touch Ctrl + d
i also found no answers for this online so came up with my own solution.
It is using macros but only a basic knowledge of how to record is needed so its very easy.
Once your drop down lists are set up, you would start with Sheet 1 and simply record a macro where you are copying the value you selected in sheet 1 and then pasting into sheet 2. then assign this macro to a button on sheet 1. every time you click the button, it will copy and paste the selected value in your sheet 1 drop down into sheet 2.
you would then do the same thing in sheet 2, except that you are copying the value into sheet 1.
you can do this for any number of sheets.
I tried it as follows and it worked: Select a cell and select the value for it (yes or no for example). Then select the group of cells you wan to have that same value – include the original cells with the value (yes or no)as the top cell in the range. Hit ctrl and D and all the cells will fill with the value in the top cell.
I accomplished this task in Excel 2013 by doing the following:
- Select the cell with the drop down response
- Scroll down to the last cell in the row that you want to have the same answer
- Press the shift key
- Select the last cell in the row that you want to have the same response
- Release shift key and scroll back up to the first cell that you highlighted
- Select your response in that cell
- Hit Ctrl+D (to duplicate response).
I’ll try and explain a little more clearly for you.
Select, with your mouse, the top most cell in the column you want to make all YES, scroll down if you need to and then, before clicking anything hold the SHIFT (not CTRL) key while you click the bottom cell in the row you wish to update.
This should highlight all of the cells in the column from the first, to the last you clicked. Now, release the SHIFT key and select YES (or no) in the top cell. The top cell (only) will change to YES (or No if you selected that). Now, without clicking doing anything else, hold down CTRL and while holding it press D.
If done correctly, all of the highlighted cells will now match the top cell.
Not the answer you’re looking for? Browse other questions tagged excel drop-down-menu or ask your own question.
Hot Network Questions
To subscribe to this RSS feed, copy and paste this URL into your RSS reader.
site design / logo © 2021 Stack Exchange Inc; user contributions licensed under cc by-sa. rev 2021.10.15.40479
We often want to copy and paste data into other worksheet cells. The three sections of this tutorial cover autofilling adjacent cells, selected cells that are not adjacent, or a cell range. For related tutorials, see the Excel Autofill Information Center.
How to Autofill a Column or Row (Adjacent Cells)
There are two fast ways that an Excel user can fill adjacent cells in a column or row with the same numerical or text data. This section shows both methods.
Method #1: Ctrl + D
- Type the data into the a worksheet cell and Enter.
- Click in the cell with the data and, keeping the left mouse button pressed, drag to select the rest of the cells in the row or column that you would like autofilled.
- Release the mouse button.
- Press Ctrl + D (the Ctrl key is held while the D key is pressed) and the cells are filled.
Method #2: Using the Fill Handle
The Fill Handle is a powerful Excel tool for autofilling a linear series, a growth series, and many other types of data.
The Fill Handle can also be used to autofill the same value AS LONG AS the value isn’t a series starter. If so, Method #1 must be used. Examples of series include days of the week, month names, series involving dates, and time.
- Type the data into a spreadsheet cell.
- Place the cursor in the bottom right corner of the cell you just typed in until you see a plus sign. With the left mouse button, press and drag the Fill Handle (plus sign) to highlight all of the cells you want filled.
- Release the mouse button and the cells are filled with the value typed in the first cell.
Autofill Selected Cells that are Non-Adjacent
Sometimes we want to autofill non-contiguous cells in a row or column. For example, we may have an empty row between each data row for readability, but now want to autofill part of a column. Use the technique below to autofill individually selected worksheet cells.
- Select the individual cells you want autofilled by pressing and holding the Ctrl key as you click inside the cells. Figure 1 shows that we have selected cells A1, A3, A5, and A7.
- After clicking in the last cell, type the number or text value you want autofilled in the last cell (figure 2).
- Press Ctrl + Enter key and the selected cells will be autofilled (figure 3).
How to Autofill a Cell Range with the Same Data
The methods in the first section of this tutorial do not work if you wish to autofill a range of cells that covers multiple columns and rows (2-dimensional). Use the technique described below.
- Select the range of cells you wish to autofill by pressing and holding the left mouse button while dragging the cursor (figure 1).
- Type the numerical or text value you wish to autofill (figure 2).
- Press Ctrl + Enter key and the selected cells will be autofilled with the value you typed (figure 3).
We hope you have found our tutorial on autofilling various Excel worksheet cells with the same data to be helpful. Cheers!
It is very common when working with spreadsheets in Microsoft Excel to have one value that repeats among a high number of cells. Whether it is a price that is common to a high number of products, or simply putting the number “0” into a lot of cells that do not have a value, you may find yourself typing the same value repeatedly.
This can often be avoided, however, by taking advantage of one of several methods for quickly filling multiple cells with the same value. So check out our guide below to learn about a few ways that you can type a value once, then have excel automatically fill a group of cells with that same value.
Insert the Same Value Into Multiple Cells in Excel 2010
This article will assume that you want to enter the same value into multiple cells, and that you want to do it as quickly as possible. The methods described below will have you enter that value into a cell one time, then you can use one of several options in order to put that same value into a group of other cells.
Method 1 (Fill a Row or Column with the Same Value)
- Step 1: Open your Excel worksheet, then type the value into one of the cells.
Method 2 (Fill Any Group of Selected Cells with the Same Value – Keyboard Shortcut)
Step 1: Select the group of cells into which you wish to insert a value.
Method 3 (Copy and Paste)
Step 1: Type your value into a cell, then right-click the cell and select the Copy option. Alternatively, you can press Ctrl + C on your keyboard to copy the cell value.
Do you need to print some data from Excel, but you only want to print some of the data in a worksheet? Click here and learn how to print a selection in Excel 2010.
Matthew Burleigh has been writing tech tutorials since 2008. His writing has appeared on dozens of different websites and been read over 50 million times.
After receiving his Bachelor’s and Master’s degrees in Computer Science he spent several years working in IT management for small businesses. However, he now works full time writing content online and creating websites.
His main writing topics include iPhones, Microsoft Office, Google Apps, Android, and Photoshop, but he has also written about many other tech topics as well.
This tutorial shows how to sum across multiple worksheets with a similar format—the values to sum are in the same cell address in each worksheet. If you want to sum across multiple worksheets with the data in different cell references, see that link instead.
Quick Overview of Linking Data
Let’s quickly review the basics of linking worksheet data. The worksheets that provides the data are the source worksheets. The sheet that has the formula to sum data from the source sheets is called the destination worksheet. And a cell in a different worksheet is referenced in this format: SheetName ! CellReference .
Throughout this tutorial we refer to worksheet names. They are the names on the tabs at the bottom of the workbook window—not the title or heading of the worksheet.
Sum Across All Worksheets in the Workbook
We have a destination sheet, Totals, and 3 city worksheets. As shown below, the 3 city sheets all have the same format. The formula in cell D4 of the Totals worksheet sums “Units” in cell B3 (item Blue) across all the city (source) worksheets.
To sum cell B3 from all worksheets in the workbook, enter: =SUM (‘*’ ! b3 )
The syntax is =SUM (‘*’ ! CellReference ) . The SUM function is used, and an asterisk, wrapped in single quotes, tells Excel to sum across ALL worksheets in the workbook.
After pressing enter, =SUM (Milan:Toronto ! B3 ) displays in the formula bar. This is the formula Excel actually stores. Excel specifies a cell range: first and last worksheet names separated by a colon, and capitalizes the “b”.
The formula could also be written the long way—listing each worksheet and cell:
=SUM( Milan!B3 , London!B3 , Toronto!B3 )
=SUM( Milan!B3 , London!B3 ,
Sum Across Select Worksheets in the Workbook
How do we write the formula if we want to sum across just some of the worksheets? Look at the 3 examples below. [If you dislike writing code, review these methods but also check out a mouse and keyboard method we explain in a related tutorial.]
1. We can specify individual worksheets as discussed above. If we want to sum only Milan and Toronto. our formula would be: =SUM( Milan!B3 , Toronto!B3 )
2. We can sum across a range. Let’s suppose our workbook also has a Tampa sheet.
To sum Tampa through Toronto and exclude Milan, type: =SUM (Tampa:Toronto ! B3 )
3. We can combine the above methods and mix ranges with individual worksheets, such as: =SUM( Sheet1:Sheet3!B3 , Sheet6!B3 , Sheet8!B3 )
Inserting a Worksheet with Existing SUM Link Formulas
Be careful when inserting new worksheets. If you used the easy formula, e.g. =SUM (‘*’ ! B3 ), to sum across all the sheets in the workbook, Excel will include the new worksheet data in the sum formula. This may or may not be what you want.
Likewise, if you specified a range in a formula, e.g. =SUM (Milan:Toronto ! B3 ), and insert a sheet inside the range, Excel will include the sheet in the formula.
Insert a new sheet before or after the range in the sum formula as shown above if you don’t want the new worksheet’s data included in the formula.
Position of the Destination Sheet in the Workbook
What if the destination worksheet is not the first or last worksheet in the order, but somewhere in the middle? Will the link formula work? Yes. Study the image below.
The Totals worksheet is in the middle of 6 other sheets. When we entered =SUM (‘*’ ! b3 ) , Excel created the formula seen in the formula bar, and shown below, specifying two worksheet ranges: Milan to London, and Paris to Toronto:
=SUM( Milan:London!B3 , Paris:Toronto!B3 )
=SUM( Milan:London!B3 ,
To learn how linking formulas are affected if worksheets reside in different workbooks, or if worksheets and workbooks are moved, see How to Link Excel Spreadsheet Data.
In that tutorial we also discuss how to ensure that automatic calculation is turned on so the destination sheet will automatically update if a source worksheet is modified, plus other linking issues.
How to apply the same formula to multiple cells in Excel
- Post author: Admin
- Post published: January 8, 2020
- Post category: Formula
For excel, nothing saves time than the use of formulas. As a matter of fact, this is the bread and butter of this Microsoft Office application. However, crafting a useful formula is the hardest of all things to do with excel. Besides that, you need to know how to apply that specific formula to multiple cells depending on the quantity of data.
For that reason, we know you may not be able to work on them without some information. We are here to help you learn how to apply the same formula to multiple cells in Excel and eventually share some tips on how to save time using excel with formulas.
There are several ways you can apply the same formula to multiple cells in Excel. At this point in time, we are going to discuss some of them especially those that are simple and very effective.
You can always use AutoFill to apply a formula in multiple cells. To do this, follow the below process;
Select a Blank cell and type the formula you need
Select one of the cells in the sheet and eventually input the formula you want to add. Let’s assume it’s (A1*4)/2+110. After this, you can drag the autofill handle to the right so that you can fill the formula into all the rows.
Now drag the autofill handle down to the range you want. This will fill the formula to all the cells in the column.
The other way to do this simply is to select the range you want the formula applied and eventually click Home>Fill>Down and fill> Right.
VBA is another method you can use to apply the same formula to multiple cells. To use VBA, then follow the below procedure;
- Press Alt and F11 at the same time. This will open the Microsoft Visual Basic for the application window.
- After this, simply click Module>Insert and eventually it will be inserted in the Module window. After this, you will be needed to copy the following VBA in the window.
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = “KutoolsforExcel”
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox(“Range”, xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
For Each Rng In WorkRng
Rng.Value = (Rng.Value * 3) / 2 + 100
Application.ScreenUpdating = True
- After this, click apply and a pop up will come up which you can later click to select the range.
- Now click ok and the entire selected margin will get the formula.
Those are the two main ways you can apply the same formula on multiple cells.
To save time with formulas, use the below tips
Copy the formula and keep references from changing
It’s always advisable to copy the formula on the clipboard because it will be very easy and simple for you to paste it in a new location. If you have multiple formulas, then it will be a good idea to use find and paste. What this means is that you should start with selecting the formulas you want to copy and eventually replace the equal sign in the formula with the hash sign.
Double click the fill handle to copy formulas
When you have formulas on copy formats, it will be quite simple to paste them whenever you want. This is likely to save you the time that you could otherwise use to rewrite the formula.
Use Autocomplete+tab to enter functions
The good thing is that Excel usually saves most of the functions whenever you are inputting them. This is simple terms that means that as you type, they are likely to be matching the text with the functions available. In this case, use AutoComplete and the tab to enter them.
Use control+ click to enter the argument
Most Excel users don’t want to put up with typing commas in between arguments. Therefore, in this case, you don’t have to go through so much hassle because you can use the Control + Click to enter that in the arguments.
Select all the formulas in a worksheet all at once
This is another way that you can save a lot of your time when you are selecting formulas. It’s that simple because when you are going to have all the formulas on the fingers as you continue.
Microsoft Excel provides the ability to sum across multiple worksheets even if the cell references in these sheets differ. We’ll look at 2 methods in this tutorial. To sum across multiple worksheets when the cell references are the same, see this tutorial for a great shortcut formula.
If new to linking data in Excel, see our overview tutorial. As a review, the worksheet that brings in data from other sheets is the destination worksheet and the sheets that provide the data are the source worksheets. A cell from a different worksheet is referenced: SheetName ! CellReference .
Link and Sum Example
In our example, we have a Totals worksheet and 3 city sheets. We are writing the formula to sum the “Units” sold for Item #400. As each store sells a different mix of products, Item #400 resides in different cells in their worksheets.
We show two different ways of creating the sum link formula. If you’d rather write code than click and jump between worksheets, use Method #1. If you do not like typing formulas, use Method #2.
- 1. Method #1: Writing the Formula Manually
- 2. Method #2: Using the Mouse and Keyboard
As each store sells a different product mix, the item’s data resides in a different cells in each sheet: B5 for Vienna, B4 for Toronto, and B6 for London. We’ll show two different ways of creating a link formula to sum the data in these disparate cells.
Note: Link formulas reference actual worksheet names; not the titles of a worksheet. Often they are the same, so be aware.
✦ Method One: Write Formulas Manually ✦
This method uses the SUM function of Excel. The SUM function adds the arguments listed inside the parentheses; e.g. =SUM( number1,number2. ) . Study the image below.
- Open the workbook containing the source and destination worksheets, and format any cell that will contain a link formula.
- In a text editor like Notepad, create a SUM function: =SUM( ) . Inside the parentheses type the cell references separated by commas (see example below). Recall that the format is SheetName ! CellReference .
- Copy and paste the SUM function into the destination cell and press Enter.
The link formula in our example is:
=SUM( Vienna ! B5 , London ! B6 , Toronto ! B4 )
The link formula in our example is
=SUM( Vienna ! B5 , London ! B6 ,
Toronto ! B4 ) .
To create link formulas to total data from other columns, copy the formula you just created, change the cell addresses, and paste into the destination cell.
If the source worksheet name has a space or special character, it must be wrapped in single quotes. For example: =’New York’!B7 . (Note: Excel automatically wraps any worksheet named C or R in single quotes for internal reasons.)
✦ Method Two: Use the Mouse and Keyboard ✦
You can use the mouse and keyboard to create the link formulas as detailed in the instructions below. Always format the cell containing the link formula before beginning.
- Open the Excel workbook containing the worksheets.
- In the destination worksheet, click in the cell that will contain the link formula and type an equal sign , but do NOT press Enter (figure 1 below).
- Go to the first source worksheet (Vienna), click in the cell that contains the data to link (B5) and squiggly lines will surround it (figure 2). Press Enter.
- Excel returns to the destination sheet (Totals) and highlights the cell below the link formula we just created in B6 (figure 3). The formula’s value, thus far, displays.
- Click in the cell holding the link formula (B6). In the formula bar, place the cursor at the end of the formula and type a + sign, but do NOT press Enter (figure 4).
- Go to the next source worksheet (London), click in the Units cell (figure 5), and press Enter. Excel returns to the destination sheet, having updated the formula value (24) and the formula itself: =Vienna!B5+London!B6 .
- Repeat steps 5 and 6 for the rest of the worksheets to be included in the link formula.
To learn how linking formulas are affected when the worksheets reside in different workbooks, or if worksheets and workbooks are moved, see our overview tutorial How to Link Excel Spreadsheet Data. That tutorial also discusses steps to take to insure that automatic calculation is turned on so the destination worksheet is always updated if the data in any source worksheet changes.
You need to use SUMIFS function that is by default designed to sum numbers with multiple criteria, based on AND logic.
- You can also use SUMIFS function to sum number with multiple criteria, based on OR logic, with an array constant.
In this article, you will learn how to sum numbers based on multiple criteria by using three of these options. Let’s assume you have data set of sales orders for various products, and you want to sum order amounts with multiple criteria.
SUMIF function with multiple criteria based on OR logic
If you want to add numbers that meet either of the criteria (OR logic) from multiple criteria then you need to sum up two or more SUMIF functions in a single formula. Suppose you want to sum order amounts for “Beans” and “Broccoli” products using OR logic then you need to sum up two SUMIF functions in a single formula using the following pattern;
=SUMIF(range, criteria1, sum_range) + SUMIF(range, criteria2, sum_range)
SUMIFS function with multiple criteria based on AND logic
If you want to sum numbers from a range when all of the specified criteria are met, based on AND logic, then you need to use SUMIFS function. It is important to know that all of the criteria must be met on single or multiple ranges to sum up numbers from sum_range.
The syntax of SUMIFS is;
SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2. )
Suppose, you want to sum the orders’ amounts that are delivered between two dates then you will use SUMIFS function. Here you need to supply two criteria on the same range and where both of these criteria are met SUMIFS function sums those orders’ amounts.
SUMIFS function with multiple criteria based on OR logic
As SUMIFS function by default entertains multiple criteria based on AND logic, but to sum numbers based on multiple criteria using OR logic, you need to SUMIFS function within an array constant.
An array constant is a set of multiple criteria provided in curly braces <> in a formula, like
Array constant using OR logic forces SUMIFS function to sum numbers based on either of the multiple criteria in an array result and finally SUMfunction add up those array results, like;
Suppose you want to sum orders’ amounts for either of the products “Orange” and “Apple” supplied as criteria in array constant then you need to provide multiple criteria in SUMIFS function as follows;
Remember, you cannot use an expression or cell reference an array constant.
Here, array constant forces SUMIFS function to generate the result in an array, like the following;
Finally, SUM function adds up these array results to come up with total figure as shown below;
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.