Excel if Running Total Matches Criteria Start Running Total Again
How to sum values based on criteria in another cavalcade in Excel?
Sometimes you want to sum the values based on criteria in another column, for instance, here I only desire to sum up the "Auction Volume" column where the corresponding "Product" column equals "A" every bit testify as beneath, how can you do it? Of course, you lot tin can sum them i past one, just here I introduce some uncomplicated methods for y'all to sum the values in Excel.
Sum values based on criteria in another column with formula in Excel
Sum values based on criteria in another column with Pivot table in Excel
Sum values based on criteria in another column with Kutools for Excel
Split information to new sheets by criteria column, and then sum
Sum values based on criteria in another column with formula in Excel
In Excel, you can utilise formulas to quickly sum the values based on certain criteria in an adjacent column.
1. Re-create the column you volition sum based on, so pasted into another column. In our instance, we copy the Fruit cavalcade and paste in Cavalcade Eastward. See screenshot left.
ii. Keep the pasted cavalcade selected, click Information > Remove Duplicates. And in the popping up Remove Duplicates dialog box, delight but check the pasted cavalcade, and click the OK push button.
three. Now merely unique values are remained in the pasted column. Select a blank prison cell likewise the pasted column, type the formula =SUMIF($A$two:$A$24, D2, $B$2:$B$24) into it, and so drag its AutoFill Handle downward the range as you need.
And and so nosotros have summed based on the specified column. Come across screenshot:
Note: In higher up formula , A2:A24 is the cavalcade whose values you will sum based on, D2 is one value in the pasted column, and B2:B24 is the column you will sum.
Easily sum/count/average values based on criteria in another column in Excel
Kutools for Excel's Advanced Combine Rows utility can help Excel users to batch sum, count, average, max, min the values in one column based on the criteria in some other column easily. Full Feature Free Trial 30-day!
Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-mean solar day, no credit card required! Get It At present
Sum values based on criteria in another column with Pivot table in Excel
Besides using formula, you also can sum the values based on criteria in another column by inserting a Pivot table.
one. Select the range you demand, and click Insert > PivotTable or Insert > PivotTable > PivotTable to open the Create PivotTable dialog box.
2. In the Create PivotTable dialog box, specify the destination rang you will place the new PivotTable at, and click the OK button.
3. And so in the PivotTable Fields pane, elevate the criteria column name to the Rows section, elevate the column you volition sum and move to the Values section. Run across screenshot:
And then you can come across the to a higher place pivot tabular array , information technology has summed the Amount column based on each item in the criteria cavalcade. Encounter screenshot to a higher place:
Sum values and combine based on criteria in another column with Kutools for Excel
Sometimes, you may need to sum values based on criteria in another column, and so replace original data with the sum values direct. You tin use Kutools for Excel's Avant-garde Combine Rows utility.
Kutools for Excel - Includes more than than 300 handy tools for Excel. Full feature gratis trial xxx-day, no credit carte required! Free Trial Now!
ane. Select the range that you volition sum values based on criteria in another cavalcade, and click Kutools > Content > Avant-garde Combine Rows.
Please annotation that the range should contain both the column yous will sum based on and the column yous volition sum.
ii. In the opening Combine Rows Based on Cavalcade dialog box, you need to:
(1) Select the column name that you volition sum based on, and then click the Primary Key push;
(ii) Select the column name that you volition sum, so click the Summate > Sum.
(3) Click the Ok push button.
Now yous volition meet the values in the specified cavalcade are summed based on the criteria in the other column. See screenshot to a higher place:
Kutools for Excel - Includes more 300 handy tools for Excel. Full feature free trial 30-day, no credit carte du jour required! Become Information technology At present
Demo: Sum values based on criteria in another column with Kutools for Excel
Kutools for Excel includes more than 300 handy tools for Excel, costless to attempt without limitation in 30 days. Download and Free Trial Now!
Easily dissever a range to multiple sheets based on criteria in a column in Excel
Kutools for Excel's Split up Data utility can help Excel users easily divide a range to multiple worksheets based on criteria in one column of original range. Full Feature Free Trial thirty-twenty-four hours!
Kutools for Excel - Includes more than 300 handy tools for Excel. Total feature gratis trial 30-solar day, no credit card required! Get Information technology At present
Relative Articles:
- Sum entire column except header
- Sum values between two dates range in Excel
The All-time Function Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert circuitous formulas, charts and anything that y'all accept used before; Encrypt Cells with password; Create Mailing Listing and transport emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit big numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Indistinguishable Rows/Columns... Prevent Indistinguishable Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Bare Rows (all cells are empty); Super Find and Fuzzy Observe in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without irresolute formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove past Position, Remove Space; Create and Print Paging Subtotals; Catechumen Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/calendar week/mean solar day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on central columns; Carve up Information into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or system. Full features 30-day free trial. 60-day money dorsum guarantee.
Function Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint , Publisher, Admission, Visio and Project.
- Open and create multiple documents in new tabs of the aforementioned window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every twenty-four hours!
Source: https://www.extendoffice.com/documents/excel/1630-excel-sum-based-on-another-column.html
0 Response to "Excel if Running Total Matches Criteria Start Running Total Again"
Post a Comment