Working with data from several worksheets

[Guest]

I have a file containing several woksheets. Each worksheet pertains to a
specific aspect of healthcare (Doctor, Insurance, Pharmacy, etc.). There is
one final worksheet that is nothing more than a summary...which re-displays
the grand totals of each worksheet within this file. Here is the problem: I
have a column that names the family member who the item pertains to, and on
the summary worksheet I would like to gather the grand total for all
worksheets for each family member. It happens to be that all worksheets in
this fil has column "E" as the column that lists the name of the family
member.

The family members are not each on their own ror or column. Here is how i
have it:
Each row is listed in order by date. The five colums are in this order:
Date, Company Rendering Service, Amount Paid, Method of Payment, Family
Member the service was for. My file has one sheet for doctor_dentist, one for
hospital_lab, one for insurance, and one for pharmacy. There is a final sheet
that brings together the grand total of all the sheets, and I have no trouble
with that. My trouble is that I want to total each family member's lines from
all the four sheets. Can I do this?

 

[Guest]

It can be done with sumproduct formulas. Here is an example with 2 sheets
instead of 4. The sheets are called doctor and insurance, being totaled on a
sheet called summary. I place the patient name in cell A2 on the summary
sheet.

=SUMPRODUCT((Doctor!$E$2:$E$65536=Summary!A2)*Doctor!$C$2:$C$65536)+SUMPRODUCT((Insurance!$E$2:$E$65536=Summary!A2)*Insurance!$C$2:$C$65536)