Song,

Conditional wizzard using sumif can only work if the criteria sits in the
same sheet, in my case one criteria is in the summary sheet, while the other
criteria is on the data sheet.

OK, to have the total for paid amount column, I already add 1 column for
paid amount using if statement to populate the cash payment and in the
summary sheet I can have the total payment for each job number. Sorry Song,
live is very hard in Indonesia, so we (accounting people) tried to do it
ourselves, actually it should have been IT dept. do this, but we do not have
IT dept in our company.

Thanks for all your idea.

Frank

"SongBear" wrote:


Frank
OK, so the data is not already in Access, I did not understand this from the
original question. And you do have experience in Access, which is good to
know as a starting point for future questions.

I think that there may be a quick answer but I need you to try it and tell
me if further help is needed. If further help is needed, I might be of more
help if I do not have to re-create a sample of your worksheets blindly to
test my suggestions.

I was just sitting here trying to imagine what your sheets looked like
exactly so I could work with the problem and suddenly realized, you need to
meet an old friend of mine.
This old friend is called the Conditional Sum Wizard. It helps you create
multiple level sum-if formulas and it might just be the fastest way to answer
your question. A lot better than writing a book.

You find the Conditional Sum Wizard by dropping and expanding your Tools
menu. It is an add-in that comes with Excel €“ I am pretty sure I have seen it
at least since XL97, not sure.

If the conditional sum wizard is not showing, then click Add Ins and check
it to install it.
Then follow the wizard.

Let me know if this is what you needed; if this does not get it done, we can
keep working on it here.
SongBear

"Frank Situmorang" wrote:

thanks song for your extensive explanation, I am studying all your
suggestion. In fact I have been successful to develop an access database for
tracking supplier invoices and it is "go live" now.

Now my Japanese boss asked me again to create a lingk between an excell
spreadsheet in the Budget Control Section with the Revenue section and asked
if it is possible to make it in access.

But I think, since I want to make it quickly, so I intend to make it in
excell but my problem is this

there is a sheet contains these columns:
1. job number,
2 invoice amount,
3. payment date

This is filled out by revenue section on timely basis randomly( not
sequenced by job)

I alreadty created another sheet(summarySheet) contains summary using
"Sumif" for total ivoicec by project).

How can I make in the same summary sheet, the total collection by using
SumIF but fon only if the payment date exists( Not blank). I tried to to
combine 2 criteria 1 from summary sheet but it does not work for the total
collection by job number.

Thank you very much.

Frank