Sum Conditional Items by date across workbooks

[mijoh20]

Hello

I have a workbook that contains 2 worksheets. Worksheet 1, calle
Client Log contains a list of client names, the county where the
live, 2 different amount columns, and the date entered.

The second worksheet, called Billing Details needs to total the
amount columns in the Client Log into one column for the curren
month, for the current year, and for life to date, by county.

Any assistance with this would be greatly appreciated..

 

[Max]

Here's one set-up to try ..

Assume the sample data below is in sheet: Client Log
in B1:E8, data from row2 down

County Amt1 Amt2 Date
County1 419 381 01-Jan-04
County2 212 161 24-Jan-05
County1 339 331 02-Feb-05
County3 467 403 15-Feb-05
County3 499 204 23-Mar-05
County2 487 329 24-Mar-05
County1 123 131 25-Mar-05
etc

In an empty col to the right, say col G,

Put in G2: =IF(B2="","",IF(COUNTIF($B$2:B2,B2)>1,"",ROW()))

Copy G2 down to say G1000 to cover the max expected data range
in cols B to E (can copy down ahead of expected data input)

In sheet: Billing Details
---------------------
Put in A11 the 4 labels:
County, CurrMth, CurrYr, ToDate

Put:

in A2:
=IF(ISERROR(SMALL('Client Log'!$G:$G,ROWS($A$1:A1))),"",INDEX('Client
Log'!B:B,MATCH(SMALL('Client Log'!$G:$G,ROWS($A$1:A1)),'Client
Log'!$G:$G,0)))

in B2:
=IF(A2="","",SUMPRODUCT(('Client Log'!$B$2:$B$1000=A2)*(MONTH('Client
Log'!$E$2:$E$1000)=MONTH(TODAY())),('Client Log'!$C$2:$C$1000)+'Client
Log'!$D$2:$D$1000))

in C2:
=IF(A2="","",SUMPRODUCT(('Client Log'!$B$2:$B$1000=A2)*(YEAR('Client
Log'!$E$2:$E$1000)=YEAR(TODAY())),('Client Log'!$C$2:$C$1000)+'Client
Log'!$D$2:$D$1000))

in D2:
=IF(A2="","",SUMIF('Client Log'!B:B,A2,'Client Log'!C:C)+SUMIF('Client
Log'!B:B,A2,'Client Log'!D))

Select A22, fill down to D1000
(cover the same range as in sheet: Client Log)

The above should return what you're after:

Col A will produce a list of the unique counties in Client Log

Col B will return the sum of the amounts from the 2 cols: Amt1 & Amt2 in
Client Log for the current month by county

Col C will return the sum of the amounts from the 2 cols: Amt1 & Amt2 in
Client Log for the current year by county

Col D will return the sum of the amounts from the 2 cols: Amt1 & Amt2 in
Client Log for "life to-date" by county, i.e. irrespective of the date
entered

For the sample data in Client Log,
you'll get in Billing Details:

County CurrMth CurrYr ToDate
County1 254 924 1724
County2 816 1189 1189
County3 703 1573 1573
(rest are blank [""] rows)