0

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,

LincM
  • 17
  • 9
  • You don't say what results you get, is it zero, an error or the wrong number? Your formulas look OK to me, are you sure the dates are true dates? Test using `COUNT` function, e.g. `=COUNT('[Agent Sheet.xlsx]Sheet1'!$B$6:$B$2501)` -if you have true dates that formula will count them – barry houdini Dec 03 '14 at 22:21
  • My data only starts from September, but I'm yielding the number 3 in January report. I can't see in any way why that's the number I'm getting. And when I use the above operation for September, where I've got a few dozen, I'm yielding zero. Using COUNT doesn't work, because it's across SharePoint. – LincM Dec 03 '14 at 22:29
  • maybe time to post a sample file. Dropbox, OneDrive, whatever works for you. Keep the sample small. Mask names and confidential data. – teylyn Dec 04 '14 at 07:44
  • Thanks for your pointers. I actually kept on fiddling around and eventually got it working. I'm not really sure what I was doing wrong before, but it's all working now. Thanks! – LincM Dec 08 '14 at 03:38

0 Answers0