Excel pull data based upon one specific word between two date

[klmiura]

I have the following worksheet lists:
Insurance Company
Amount Paid
Date Paid

On the second worksheet I have an A/R schedule and I need to pull the total
amount paid from above based upon the insurance company name between two
dates ie...60-90 day category 10/01/2006-08/01/2006. I have been trying to
gather this formula all day and I cannot seem to get it.

 

[Gary Brown]

Chip Pearson's site:
http://www.cpearson.com/excel/array.htm
shows how to use multiple criteria to get counts and sums.
--
HTH,
Gary Brown
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.

 

[klmiura]

I tried to follow what it was saying and came up with several different
scenarios and none of them work. Here are the formulas I have gathered, I
have all of these set up as an array as suggested:

=SUM('rapidata$'!$A$2:$A$5968="'Adhoc
UBH'")*('rapidata$'!$O$2:$O$5968<="09/25/2006")*('rapidata$'!$O$2:$O$5968>="08/27/2006")*('rapidata$'!$M$2:$M$5968)

=IF($B6='rapidata$'!$A$2:$A$5968,SUMPRODUCT(('rapidata$'!$O$2:$O$5968<='Accounts
Receivable Aging'!S4)*('rapidata$'!$O$2:$O$5968>='Accounts Receivable
Aging'!T4)*('rapidata$'!$M$2:$M$5968)),0)


I have the same workbook with two seperate sheets and I am trying to look up
the insurance company name from one sheet to the data table and return the
amount on the data table that corresponds with the two dates from the sheet
where I want the consolidated amounts to be. This data table is quite large
and has the same number of rows (5698).

Any more assistance would be greatly appreciated.

Karen

 

[Gary Brown]

Not sure of exactly what you want to do BUT I can give you some things to
think about concerning the 2 formulas you have created.

1st formula:
- if your worksheet contains dates then "09/25/2006" which is a STRING will
not work. Change "09/25/2006" and "08/27/2006" to DATEVALUE("09/25/2006")
and DATEVALUE("08/27/2006").

2nd formula:
- I think SUMPRODUCT is causing issues but have not tested this.
- $B6 is neither totally absolute nor totally relative and MAY be causing
issues.

--
HTH,
Gary Brown
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.

 

[klmiura]

No this still does not work. Let me give you more information:

worksheet 1

Payor Code: Name 10/26/2006 9/26/2006(Hidden)
9/25/2006
0-30
31-60
AD HOC AETNA - EL PASO $0.00 $0.00
Trying to lookup matching data from worksheet 2(below) that has the above
name (ad hoc aetna - el paso) from worksheet 1 to Column A in worksheet 2
and then anything that matches that name, I need it to pull all claim amounts
from the column B (worksheet 2) that are <=10/26/2006 and >=09/26/2006 and
put them in the appropriate bucket (worksheet 1) ie above ..0-30, 31-60
etc...this is the amount that is still due within the date ranges for this
one payer as a total sum since their are over 5,000 records and the dates,
amounts and payers vary.

Worksheet 2

Payor Code : Name date of service
claim amount
AD HOC AETNA - EL PASO 10/20/2006
$20
BLUE CROSS 07/18/2006
$30
MEDICAID 06/01/2006
$10
AD HOC AETNA - EL PASO 1/20/2006
$50

 

[Gary Brown]

Hi Karen,
When you gave your example, the columns ended up all over the place. I
THINK I understand your format.
To explain the formula, let's start with a new workbook.
Rename 'Sheet 1' as 'AR AGING'
Rename 'Sheet 2' as 'RapiData'

Go to the 'RapiData' worksheet.
Put the following in the indicated cells...
A1 - Payor Code:
B1 - Name
C1 - Date of Service
D1 - Claim Amount
B2 - AD HOC AETNA - EL PASO
C2 - 10/20/2006
D2 - $20.00
B3 - BLUE CROSS
C3 - 09/18/2006 <== I changed this to put within 60 days
D3 - $30.00
B4 - MEDICAID
C4 - 06/01/2006
D4 - $10.00
B5 - AD HOC AETNA - EL PASO
C5 - 01/20/2006
D5 - 50.00


Go to the 'AR AGING' worksheet.
Put the following in the indicated cells...
A1 - Payor Code:
C1 - 10/26/2006
D1 - 09/26/2006
E1 - 09/25/2006
B2 - Name
C2 - 0-30
E2 - 31-60
B3 - AD HOC AETNA - EL PASO
B4 - BLUE CROSS
B5 - MEDICAID

In Cell C3, put the following formula (all one line).
=SUM((RapiData!$B$2:$B$5='AR Aging'!$B3)*(RapiData!$C$2:$C$5<='AR
Aging'!C$1)*(RapiData!$C$2:$C$5>='AR Aging'!D$1)*(RapiData!$D$2:$D$5))

-----------------------
REMEMBER to hit CTRL-SHIFT-ENTER instead of just ENTER so that you create an
array.
If you do it correctly, the formula will have '{' and '}' around it.
ie:
{=SUM((RapiData!$B$2:$B$5='AR Aging'!$B3)*(RapiData!$C$2:$C$5<='AR
Aging'!C$1)*(RapiData!$C$2:$C$5>='AR Aging'!D$1)*(RapiData!$D$2:$D$5))}
-----------------------
BE CAREFUL WITH THE ABSOLUTES '$'
-----------------------

Copy Cell C3 down to C4 and C5.

Copy Cells C3:C5 to E3:E5.

C3 will correctly show $20.00 is 0-30 days old for AD HOC AETNA - EL PASO.
C4 will correctly show $ 0.00 is 0-30 days old for BLUE CROSS.
C5 will correctly show $ 0.00 is 0-30 days old for MEDICAID.

E3 will correctly show $ 0.00 is 31-60 days old for AD HOC AETNA - EL PASO.
E4 will correctly show $30.00 is 31-60 days old for BLUE CROSS.
E5 will correctly show $ 0.00 is 31-60 days old for MEDICAID.

Hope this clears things up.
--
Gary Brown
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.

 

[Gary Brown]

Oh Forgot..
Cell F1 of worksheet 'AR AGING' should contain the date 08/26/2006 or
something close to it to mark off the 60 day period.
--
HTH,
Gary Brown
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.

 

[klmiura]

Thank you so much Gary this was great...it worked like a charm.