Summing up data in various sheets (by criteria)

[Elucidate]

Hi guys

Data set as follows

Sheet A
Payment Date Amount
26-Sep-11 1000
26-Sep-11 2000
26-Sep-11 3000
27-Sep-11 2000
27-Sep-11 1000
27-Sep-11 2000

Sheet B
Payment Date Amount
26-Sep-11 -100
26-Sep-11 600
26-Sep-11 -300
27-Sep-11 800
27-Sep-11 -200
27-Sep-11 300


In a different sheet I want to do the following:

Column A: Sum up the amounts according to the dates and display the date in Column B
Column C: Sum up the amounts according to the dates and display the date in Column D
Column E: Sum up Column A and Column C and according to the dates and display date in Column F.

Thanks,

 

[Comfy]

I could only do it by adding an addition 2 columns in the 3rd Sheet:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Payment Date</td><td style=";">Amount</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">26/09/11</td><td style="text-align: right;;">1000</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">26/09/11</td><td style="text-align: right;;">2000</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">26/09/11</td><td style="text-align: right;;">3000</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">27/09/11</td><td style="text-align: right;;">2000</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">27/09/11</td><td style="text-align: right;;">1000</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">27/09/11</td><td style="text-align: right;;">2000</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Payment Date</td><td style=";">Amount</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">26/09/11</td><td style="text-align: right;;">-100</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">26/09/11</td><td style="text-align: right;;">600</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">26/09/11</td><td style="text-align: right;;">-300</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">27/09/11</td><td style="text-align: right;;">800</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">27/09/11</td><td style="text-align: right;;">-200</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">27/09/11</td><td style="text-align: right;;">300</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br />

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Sheet 1 Amounts</td><td style=";">Sheet1 Dates</td><td style=";">Sheet 2 Amounts</td><td style=";">Sheet 2 Dates</td><td style=";">Combined Amounts</td><td style=";">Combined Dates</td><td style=";">Total Amounts</td><td style=";">Unique Dates</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">6000</td><td style="text-align: right;;">26/09/11</td><td style="text-align: right;;">200</td><td style="text-align: right;;">26/09/11</td><td style="text-align: right;;">6000</td><td style="text-align: right;;">26/09/11</td><td style="text-align: right;;">6200</td><td style="text-align: right;;">26/09/11</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">5000</td><td style="text-align: right;;">27/09/11</td><td style="text-align: right;;">900</td><td style="text-align: right;;">27/09/11</td><td style="text-align: right;;">5000</td><td style="text-align: right;;">27/09/11</td><td style="text-align: right;;">5900</td><td style="text-align: right;;">27/09/11</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;">200</td><td style="text-align: right;;">26/09/11</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;">900</td><td style="text-align: right;;">27/09/11</td><td style="text-align: right;;">0</td><td style=";"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A2</th><td style="text-align:left">=SUMIF(<font color="Blue">Sheet1!$A$2:$A$7,B2,Sheet1!$B$2:$B$7</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=SUMIF(<font color="Blue">Sheet2!$A$2:$A$7,D2,Sheet2!$B$2:$B$7</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">$A$2:$A$3, ROWS(<font color="Green">E1:$E$1</font>)</font>), IFERROR(<font color="Red">INDEX(<font color="Green">$C$2:$C$3, ROWS(<font color="Purple">E1:$E$1</font>)-ROWS(<font color="Purple">$A$2:$A$3</font>)</font>), ""</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">$B$2:$B$3, ROWS(<font color="Green">F1:$F$1</font>)</font>), IFERROR(<font color="Red">INDEX(<font color="Green">$D$2:$D$3, ROWS(<font color="Purple">F1:$F$1</font>)-ROWS(<font color="Purple">$B$2:$B$3</font>)</font>), ""</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">=SUMIF(<font color="Blue">$F$2:$F$5,H2,$E$2:$E$5</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">{=IF(<font color="Blue">ISNA(<font color="Red">INDEX(<font color="Green">Sheet1!$A$2:$A$7,MATCH(<font color="Purple">0,COUNTIF(<font color="Teal">$B$1:B1,Sheet1!$A$2:$A$7</font>),0</font>)</font>)</font>),"",INDEX(<font color="Red">Sheet1!$A$2:$A$7,MATCH(<font color="Green">0,COUNTIF(<font color="Purple">$B$1:B1,Sheet1!$A$2:$A$7</font>),0</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">{=IF(<font color="Blue">ISNA(<font color="Red">INDEX(<font color="Green">Sheet2!$A$2:$A$7,MATCH(<font color="Purple">0,COUNTIF(<font color="Teal">$D$1:D1,Sheet2!$A$2:$A$7</font>),0</font>)</font>)</font>),"",INDEX(<font color="Red">Sheet2!$A$2:$A$7,MATCH(<font color="Green">0,COUNTIF(<font color="Purple">$D$1:D1,Sheet2!$A$2:$A$7</font>),0</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H2</th><td style="text-align:left">{=IF(<font color="Blue">ISNA(<font color="Red">INDEX(<font color="Green">$F$2:$F$5,MATCH(<font color="Purple">0,COUNTIF(<font color="Teal">$H$1:H1,$F$2:$F$5</font>),0</font>)</font>)</font>),"",INDEX(<font color="Red">$F$2:$F$5,MATCH(<font color="Green">0,COUNTIF(<font color="Purple">$H$1:H1,$F$2:$F$5</font>),0</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[Elucidate]

Thanks very much

Excel is giving me numbers instead of actual date in my Sheet 3. How do i get it to just give the actual date instead of the number ?

Also, I noticed you specified a range of values in your SUMIF formula in the sheets. How do i check for similar dates across the entire sheet if i have thousands of rows with data ? In my actual spreadsheet i have data going up to the 1600th row for example

 

[Comfy]

Excel is giving me numbers instead of actual date in my Sheet 3. How do i get it to just give the actual date instead of the number ?

Change the cell format to date. e.g. dd/mm/yyyy

Also, I noticed you specified a range of values in your SUMIF formula in the sheets. How do i check for similar dates across the entire sheet if i have thousands of rows with data ? In my actual spreadsheet i have data going up to the 1600th row for example

Change the Range to the 1600th row.

 

[Elucidate]

Thanks. The reason i am asking is because this will not be a once off thing or done manually

I generate a report let's say daily and i want the formula to detect how many rows of data are available and automatically compute the figures as per above

 

[Comfy]

We can use this:

=IF(ISERROR(MATCH(9.999999E+306,A:A)),MATCH("*",A:A,-1), 
IF(ISERROR(MATCH("*",A:A,-1)),MATCH(9.999999E+306,A:A), 
MAX(MATCH(9.999999E+306,A:A),MATCH("*",A:A,-1))))

to find the last row in a column and use INDIRECT to incorporate that into the SUMIF etc.

or we can use VBA to build the summary sheet?

 

[Elucidate]

Yes please

Would VBA require a complete rework of the sheets ? meaning we scrap the formulae used thus far ?

 

[Comfy]

Yes, but it will be easier

I need a few things first.

Will your sheets have the same name each time?

What column will the dates be in on each sheet and what column will the amount be on each sheet?

 

[Elucidate]

Yes, but it will be easier

I need a few things first.

Will your sheets have the same name each time?

What column will the dates be in on each sheet and what column will the amount be on each sheet?

1. The column names don't change and are named as follows:

Sheet 1 : 'BONDS'
Sheet 2 : 'FUTURES'

2. Column dates are on each sheet:

Dates are in column D (generally start in cell D19) on each of the sheets
Amounts are in column W (generally start in cell W19) - I know you didn't request this but just thought I would add it in for good measure

Thanks again

 

[Comfy]

OK so using your input I have hard coded the sheet names and also that the first date and amount on each sheet starts on row 19.

Please backup your workbook before running this:

Sub Sumifs()
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim drng1 As Range, drng2 As Range, drng3 As Range
Dim arng1 As Range, arng2 As Range

Set ws1 = Worksheets("BONDS")
Set ws2 = Worksheets("FUTURES")
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Over View"
Set ws3 = Worksheets("Over View")
ws3.Cells(1, 1).Value = "BONDS Amounts"
ws3.Cells(1, 2).Value = "BONDS Dates"
ws3.Cells(1, 3).Value = "FUTURES Amounts"
ws3.Cells(1, 4).Value = "FUTURES Dates"
ws3.Cells(1, 5).Value = "Total Amounts"
ws3.Cells(1, 7).Value = "All Dates"
ws1.Select
Set arng1 = ws1.Range(Cells(19, 23), Cells(ws1.Range("W65536").End(xlUp).Row, 23))
Set drng1 = ws1.Range(Cells(19, 4), Cells(ws1.Range("D65536").End(xlUp).Row, 4))
ws1.Range(Cells(19, 4), Cells(ws1.Range("D65536").End(xlUp).Row, 4)).Copy ws3.Cells(2, 2)

ws2.Select
Set arng2 = ws2.Range(Cells(19, 23), Cells(ws2.Range("W65536").End(xlUp).Row, 23))
Set drng2 = ws2.Range(Cells(19, 4), Cells(ws1.Range("D65536").End(xlUp).Row, 4))
ws2.Range(Cells(19, 4), Cells(ws2.Range("D65536").End(xlUp).Row, 4)).Copy ws3.Cells(2, 4)


ws3.Select

For i = 1 To drng1.Count
    ws3.Cells(1 + i, 1).Value = Application.WorksheetFunction.SumIf(drng1, drng1.Cells(i).Value, arng1)
Next i
For i = 1 To drng2.Count
    ws3.Cells(1 + i, 3).Value = Application.WorksheetFunction.SumIf(drng2, drng2.Cells(i).Value, arng2)
Next i

Set drng1 = ws3.Range(Cells(2, 1), Cells(ws3.Range("B65536").End(xlUp).Row, 2))
Set drng2 = ws3.Range(Cells(2, 3), Cells(ws3.Range("D65536").End(xlUp).Row, 4))

drng1.RemoveDuplicates Columns:=1
drng2.RemoveDuplicates Columns:=1

drng1.Copy ws3.Cells(2, 6)
drng2.Copy ws3.Cells(ws3.Range("F65536").End(xlUp).Row + 1, 6)

Set drng1 = ws3.Range(Cells(2, 7), Cells(ws3.Range("G65536").End(xlUp).Row, 7))
Set arng1 = ws3.Range(Cells(2, 6), Cells(ws3.Range("F65536").End(xlUp).Row, 6))

For i = 1 To drng1.Count
    ws3.Cells(1 + i, 5).Value = Application.WorksheetFunction.SumIf(drng1, drng1.Cells(i).Value, arng1)
Next i
Columns(6).Delete
Set drng3 = ws3.Range(Cells(2, 5), Cells(ws3.Range("E65536").End(xlUp).Row, 6))
drng3.RemoveDuplicates Columns:=1
End Sub