Sum Product help

[Stacy M via OfficeKB.com]

I am trying to set up an accounts payable spreadsheet that automatically
calculates what is due to the doctors we work with based on which visits they
have performed. Here's an example:
Visit 1 Visit 2 Visit 3 Total
Amt. Due:
350 175 200
Dates:
5/7/05 5/24/05


I used the sum product function to figure this out, as suggested by someone
on here. (=SUMPRODUCT(--(A7:C7<>0),A5:C5)) Where the first range (A7:C7) is
the visit range, and the second being the amount due range. What I'm trying
to figure out is....once we pay off some of those visits, but there's still
an amount due, is there any way to change it so that it is not calculated in
the total? For example, if we wanted to pay Visit 1 (5/7/05), is there
something we could do, such as bold the text, to show that it's already been
paid, as well as keeping it from being included in total amount due?

I apologize if this is confusing--it's hard to explain it to others!! If
anyone could give me some suggestions on what to try, i would really
appreciate it.

 

[Bernie Deitrick]

Stacy,

Forget formulas: Use a database and a pivot table.

Set up your database as

Doctor Date Amount Due Status

Fill in one row for every visit. Enter "Owed" in the Status when you first enter the bill, then go
back and enter "Paid" when the bill is paid.

Then use a pivot table on the database, and use Doctor and Status as the row fields, and sum of
Amount Due as the data field.

No formulas involved, and much less chance of error.

When you set up your database and pivot table, include a "Last Line" entry, and always insert rows
above this last line to keep Excel informed of where your data is.

HTH,
Bernie
MS Excel MVP