Need help regarding sum formula
- Thread starter RAFI
- Start date
Hi,
You can do this through a sum-if formula on a list of your unique dates. Alternatively, you can do this without formulas by combining the data and then applying a pivot table.
Using Sum-If
1. First, you need to create a list of all your unique dates. The easiest way to do this is to combine the data and then create a pivot table, entering the dates as row labels.
Then once you've done this, use sumif functions. For example if your first date from step 1 is in cell F5 then:
2. The sum of income would be =SUMIF(Sheet1!B:B, F5, Sheet1!C:C)+SUMIF(Sheet2!B:B, F5, Sheet2!C:C)
3. The sum of sales would be =SUMIF(Sheet1!B:B, F5, Sheet1!D:D)+SUMIF(Sheet2!B:B, F5, Sheet2!D:D)
4. Copy and paste these formulas down the entire list of your dates to add up for all the dates
I've added a GIFCoach on this to help you, using your data:
https://www.gifcoach.com/how-to/how-add-numbers-using-sumif-function/
Alternative: Use a Pivot Table
(First, create a combined block of data by copying and pasting to a new sheet)
1. Left-click anywhere within your block of data
2. Click the 'Insert' tab, and then the 'PivotTable' Button within the 'Tables' Group.
3. The source data should automatically populate (provided you have done step #1). Choose where you want the Pivot Table to be placed, then click 'OK'
4. Drag your dates into the 'Rows' box, and your values fields ('Income' & Sales') into the 'Values Box'. Make sure your values are summing up (not counting, etc.) by by right-clicking on the values then selecting 'Value Fields Settings', then 'Summarize Values By' -> 'Sum'
5. Optionally, you can change the number format of each of these by right-clicking on the values then selecting 'Value Fields Settings' -> 'Number Format' -> 'Currency'
I've also added a GIFCoach on this to help you, using your data:
https://www.gifcoach.com/how-to/add-numbers-using-pivot-table-microsoft-excel/
You can do this through a sum-if formula on a list of your unique dates. Alternatively, you can do this without formulas by combining the data and then applying a pivot table.
Using Sum-If
1. First, you need to create a list of all your unique dates. The easiest way to do this is to combine the data and then create a pivot table, entering the dates as row labels.
Then once you've done this, use sumif functions. For example if your first date from step 1 is in cell F5 then:
2. The sum of income would be =SUMIF(Sheet1!B:B, F5, Sheet1!C:C)+SUMIF(Sheet2!B:B, F5, Sheet2!C:C)
3. The sum of sales would be =SUMIF(Sheet1!B:B, F5, Sheet1!D:D)+SUMIF(Sheet2!B:B, F5, Sheet2!D:D)
4. Copy and paste these formulas down the entire list of your dates to add up for all the dates
I've added a GIFCoach on this to help you, using your data:
https://www.gifcoach.com/how-to/how-add-numbers-using-sumif-function/
Alternative: Use a Pivot Table
(First, create a combined block of data by copying and pasting to a new sheet)
1. Left-click anywhere within your block of data
2. Click the 'Insert' tab, and then the 'PivotTable' Button within the 'Tables' Group.
3. The source data should automatically populate (provided you have done step #1). Choose where you want the Pivot Table to be placed, then click 'OK'
4. Drag your dates into the 'Rows' box, and your values fields ('Income' & Sales') into the 'Values Box'. Make sure your values are summing up (not counting, etc.) by by right-clicking on the values then selecting 'Value Fields Settings', then 'Summarize Values By' -> 'Sum'
5. Optionally, you can change the number format of each of these by right-clicking on the values then selecting 'Value Fields Settings' -> 'Number Format' -> 'Currency'
I've also added a GIFCoach on this to help you, using your data:
https://www.gifcoach.com/how-to/add-numbers-using-pivot-table-microsoft-excel/