Sometimes, you may have multiple sheets in your Excel document that contains your dataset. To sum up or count the dataset in these sheets may be challenging. Thankfully, Excel allows users to count across multiple sheets in simple ways. This article will discuss ways of counting across multiple sheets in Excel.
To sum values in multiple Sheets
Below are the steps to follow:
1. Open the Excel application.
2. Open the Excel document that has the worksheet with your dataset.
3. Create a new sheet that will contain your total result. Click on an empty cell and name it SUM. In the next cell, type the Equal sign (=) followed by the SUM function. That is, =SUM (
4. Type the sheets that have your dataset. That is, =SUM(Sheet1:Sheet2
5. After adding the sheet names containing your dataset, enter an Exclamation mark (!). That is, =SUM(Sheet1:Sheet2!
6. Select the cell you want to sum up. That is, =SUM(Sheet1:Sheet2!B2)
7. Finally, press the Enter button. The result of the sum of selected sheets will be displayed.
Countif A specific value across multiple worksheets
The COUNTIF function counts the number of values or letters in a worksheet or workbook.
Steps:
1. Open the Excel application.
2. Open the Excel document that has the worksheet with your dataset.
3. Create a new sheet containing your total result, or click on an empty cell in one of your worksheets.
4. Type the Equal sign (=) followed by the SUMPRODUCT function. That is, =SUMPRODUCT (
5. Next, add the COUNTIF function followed by the INDIRECT function. That is, =SUMPRODUCT (COUNTIF(INDIRECT
6. Select the sheets that contain the value you need to check. That is, =SUMPRODUCT (COUNTIF(INDIRECT(“““&Sheets1:Sheet2&””!
7. Then, add the cells you need to count the values. That is, =SUMPRODUCT (COUNTIF(INDIRECT(“““&Sheets1:Sheet2&””! A2:A8”),
8. Add the value or the cell reference of the value you wish to count. That is, =SUMPRODUCT (COUNTIF(“““&Sheets1:Sheet2&””! A2:A8”, A2)). Finally, hit the Enter button.
To count or check duplicates across multiple sheets
Steps to follow:
1. Open the Excel application.
2. Open the Workbook containing your dataset or sheets you need to check for duplicates.
3. Select the dataset on the first sheet. Then, click the Home tab from the ribbon.
4. In the Styles section, click the Conditional Formatting drop-down button. From the menu, click the New Rule button.
5. Use a formula to determine which cell to format the button. Type the rule that will compare the two sheets in the Rule box. For Example, =COUNTIF(Sheet2!$A$2:$A$6,A2)
6. Click the Format button and select the formatting option to show the duplicate cells. Finally, click the OK button.