Originally Posted by
Pete_UK
It's going to be a bit awkward as you have data on the first sheet occupying two rows per company and your data on the second sheet is just on a single row. It will be easier if you insert a blank row between each company on your second sheet, and then the formulae can be copied to each alternate row - afterwards, those blank rows can be deleted again (or hidden).
Another awkward thing is that you have your years in a merged cell covering 4 columns, but as you need to specify the dates for each quarter this means that you basically need 4 formulae which can then be copied as a block. I've put these formulae in the cells stated below:
C4: =SUMIFS('Dividend Payouts'!$C4:$DC4,'Dividend Payouts'!$C3:$DC3,">="&DATE(C$2,10,1),'Dividend Payouts'!$C3:$DC3,"<="&DATE(C$2,12,31))
D4: =SUMIFS('Dividend Payouts'!$C4:$DC4,'Dividend Payouts'!$C3:$DC3,">="&DATE(C$2,7,1),'Dividend Payouts'!$C3:$DC3,"<="&DATE(C$2,10,31))
E4: =SUMIFS('Dividend Payouts'!$C4:$DC4,'Dividend Payouts'!$C3:$DC3,">="&DATE(C$2,4,1),'Dividend Payouts'!$C3:$DC3,"<="&DATE(C$2,6,30))
F4: =SUMIFS('Dividend Payouts'!$C4:$DC4,'Dividend Payouts'!$C3:$DC3,">="&DATE(C$2,1,1),'Dividend Payouts'!$C3:$DC3,"<="&DATE(C$2,3,31))
Hopefully you can spot the date ranges in the formulae. Then this block of 4 cells can be copied and pasted into cells G4, K4, O4, S4 etc across that row (I've only done it up to S4 - it's YOUR research project, after all !! <bg>).
I've then inserted a new row 5, and copied the formulae from row 4 directly into row 6. You will need to continue to do this, both across the columns and then into alternate rows (with blank rows inserted) for all of your companies. This assumes that your companies on the second sheet are in exactly the same order as those on the first sheet.
I'm not sure why you had save the example file as a macro-enabled workbook (.xlsm) when there wasn't a macro in there, so I've made the attachment an .xlsx file.
Hope this helps.
Pete
Bookmarks