UDF #VALUE! Error On Non-Active Sheet

I have data that is laid out as follows: Date, amount1, amount2 and a value in $
There are many rows, and there will be more than one row for each date. see attached worksheet for an example.

I want to create a table that will provide me totals of the amounts and the value for each date. I have managed to do this, using subtotals, but this gets quite slow, and in any case I want to be able to reference each of the subtotals with another sheet, so I tried to use pivot tables. I managed to do the above by using three pivot tables Date/Amount1 then Date/Amount2 and finally Date/Value, and hiding the extra two date columns. However my next table of data contains 10,000 rows and over 20 columns, so I am wondering if the above can be done within one pivot table. I know very little about pivot tables, and when I try to drop a second data item in, it always puts it below the first rather than in a separate column, and I get a table looking like the one in the attached worksheet. Am I doing something very basic, wrong? Is it possible to get a single pivot table, to look like my first summary chart in the attached worksheet. If I can do it with three columns, then I should be able to extend it to my much larger table of data.