I have a tracking sheet. It has information which spans months, so I'm creating a reporting sheet to summarise based on month to month, using sumproduct, because I can't use countifs across closed workbooks in Sharepoint.
I have used the following formula to sum up monetary values and other specific values, yet now I'm trying to simply achieve a total number of entries which fall between the two dates, and I'm falling flat.
The B column is the dates
=SUMPRODUCT(--('[Agent Sheet.xlsx]Sheet1'!$B$6:$B$2501>=B3),--('[Agent Sheet.xlsx]Sheet1'!$B$6:$B$2501<=EOMONTH(B3,0)),'[Agent Sheet.xlsx]Sheet1'!$C$6:$C$2501)
I've tried a number of other incarnations, such as converting the third array into boolean:
=SUMPRODUCT(--('[Agent Sheet.xlsx]Sheet1'!$B$6:$B$2501>=B3),--('[Agent Sheet.xlsx]Sheet1'!$B$6:$B$2501<=EOMONTH(B3,0)),--'[Agent Sheet.xlsx]Sheet1'!$C$6:$C$2501)
Or making it summarise ifnot and if:
=SUMPRODUCT(--('[Agent Sheet.xlsx]Sheet1'!$B$6:$B$2501>=B3),--('[Agent Sheet.xlsx]Sheet1'!$B$6:$B$2501<=EOMONTH(B3,0)),--('[Agent Sheet.xlsx]Sheet1'!$C$6:$C$2501="")*--('[Agent Sheet.xlsx]Sheet1'!$C$6:$C$2501<>""))
I'm clearly missing something huge here.
BTW: I'm aware much of this can be achieved with PivotTables, but because I need a lot more data than can be achieved from multiple worksheet consolidations than can be accommodated, I have no choice but to create my own.
Thanks,