Thank you Song, you are so helpful to us. It works now for my worksheets

GBU

Frank

"SongBear" wrote:


Frank,
I was able to make the Conditional Sum Wizard work across two worksheets, I
did not try it across two workbooks. As I note below, the Conditional Sum Wiz
creates an array formula, a special formula in Excel that stops being an
array formula when modified, and I nearly always have to modify the formula a
little. You turn the modified formula back into an array formula by hitting
control-shift-enter.

The following is based on a test setup I created in two worksheets in the
same workbook. The results of these formula examples from my test setup are
depicted below the formulas and discussions.


Total Invoiced Per Job
(Notes: these formulas are in cells on the Summary sheet; these are not
array formulas (see below where we do use array formulas); these formulas use
a criteria defined in the Summary sheet (column C), and each formula uses
both a criteria-match range and a numbers-to-sum range in the Data sheet)
=SUMIF(DataSht!$C$7:$C$36,SummarySht!C6,DataSht!$D $7:$D$36)
=SUMIF(DataSht!$C$7:$C$36,SummarySht!C7,DataSht!$D $7:$D$36)
=SUMIF(DataSht!$C$7:$C$36,SummarySht!C8,DataSht!$D $7:$D$36)
=SUMIF(DataSht!$C$7:$C$36,SummarySht!C9,DataSht!$D $7:$D$36)
=SUMIF(DataSht!$C$7:$C$36,SummarySht!C10,DataSht!$ D$7:$D$36)


Total Paid Per Job
(Notes: these multiple criteria formulas are all array formulas; these
formulas are in cells on the Summary sheet; the match-range and the sum-range
are on the Data sheet. One criteria is defined in Column C of the local
(Summary) sheet, the other criteria is simply looking for 'greater than zero'
in the date range.)
{=SUM(IF(DataSht!$C$7:$C$36=C6,IF(DataSht!$E$7:$E$ 36
0,DataSht!$D$7:$D$36,0),0))}
{=SUM(IF(DataSht!$C$7:$C$36=C7,IF(DataSht!$E$7:$E$ 36
0,DataSht!$D$7:$D$36,0),0))}
{=SUM(IF(DataSht!$C$7:$C$36=C8,IF(DataSht!$E$7:$E$ 36
0,DataSht!$D$7:$D$36,0),0))}
{=SUM(IF(DataSht!$C$7:$C$36=C9,IF(DataSht!$E$7:$E$ 36
0,DataSht!$D$7:$D$36,0),0))}
{=SUM(IF(DataSht!$C$7:$C$36=C10,IF(DataSht!$E$7:$E $36
0,DataSht!$D$7:$D$36,0),0))}
More Notes: You can not put the curly brackets on an array formula; if you
type curly brackets in it will not work - Excel has to do it. Enter or modify
the formula (which removes the brackets), then, when finished €“ or when you
want to test it - make sure the cursor is in the formula in the formula bar
and hit the simultaneous three key combination of Shift-Control-Enter.

I made the first of these particular formulas using the Conditional Sum
Wizard, but had to modify the formula to make it work the way I wanted it to.
I then drag-copied the formula down into the other four cells.

The Conditional Sum Wiz creates an array formula for you. I normally use the
wizard to build a basic formula and nearly always have to modify it. When I
built this formula with the wizard, I used the second option at the end of
the wizard, which is to let the wizard define cells in which to put criteria.
This way, for the job number criteria, I clicked the cell with the job number
on the same row in the short list of jobs. The wizard asked me if I wanted to
replace the current contents of that cell with the job number that I had
selected earlier in the wizard. I went ahead and allowed this and then just
typed the correct number back in that job number cell.

This caused the wizard to create a cell reference to the job number cell for
that row.

When the next criteria came up in the wizard, I knew I was going to modify
it, so I just clicked an empty cell. When the wizard was finished, I modified
the formula to replace the reference in the formula to the empty cell with
€œ0€� (Greater than zero). Since the formula was modified, it was no longer an
array formula and I had to Ctl-Shft-Enter the formula as previously mentioned.


I created a fake data sheet to test this; the results are depicted below.
Row and Column numbers are as given.
Note that the checksum (Total paid) for each worksheet is the same,
indicating that the formulas are working. Now that the sheets are set up, all
I have to do is enter a new date in the Payment Date column of the Data sheet
and the job total on the Summary sheet and the totals in both sheets
checksums change automatically.

The assumptions we 1. Each job can have multiple invoices; 2. Each
invoice is paid in full when paid, no partial payments per invoice. 3.
Invoices are recorded at the time of initiation of the transaction; payments
can be made and recorded later. 4. The bottom limit of the columns of
invoices is arbitrary; you would extend the selection in the formulas to
include enough rows to hold all of the data for the number of invoices you
expect to have there. (in other words, instead of stopping your formula at
row 36, extend it to row 200 or row 1000, or some appropriate number. It will
work just as well and this will prevent one reason for needing to modify the
formula later.

If this helps, answers the question, or if it requires clarification, please
let us know.

SongBear

Frank, one more thing: As you have noticed, the computer is a tool which
increases capability, efficiency and productivity, reduces mistakes, and can
help make your company (and yourself) more competitive in the marketplace.
But, as we entrust more and more mission critical data to it, the computer
has a tendency to lull us into a false sense of safety. Catastrophic failure
can still happen, a hard disk or a computer can still fail, wind storms,
floods, fire, etc. can destroy data in a single location.

In the absence of an IT department, it falls to you to back up critical
data, preferably in several different media forms, as part of an overall
disaster recovery plan. Secondary hard drives in the same computer is the
minimum, with copies of data on hard drives in multiple computers being
better. Also data burned on CD or DVD, data stored on €œmemory sticks€�,
external stand alone hard drives; with all of the portable media stored
off-site in professional external storage sites, in an on-site fire-proof
safe, in bank deposit boxes, etc.

Advice that you did not ask for: Figure out what is appropriate for your
local economic/cultural/business infrastructure and for your companys
economic/cultural/business infrastructure and become your bosss ally in
creating, maintaining, and promoting a competent disaster recovery plan for
the company.

The TEST Worksheets (both worksheets in the same book)

Worksheet: SummarySht (Summary Sheet)
Checksum formula: =SUM(E6:E10)

C D E
3 SummarySht Total Paid: $661.60
4
5 Total
Invoiced Total Paid
Job Per Job Per Job
6 12132 $582.79 $-
7 25252 $639.25 $222.65
8 3654 $1,161.71 $-
9 6985 $845.17 $179.96
10 325785 $848.63 $259.00



Worksheet: DataSht (Data Sheet)
Checksum formula: =SUMIF(E7:E36,"0",D7:D36) [note: not an array formula]

C D E
4 DataSht Total Paid $661.60
5
6 Job Invoice Payment
Number Amount Date

7 12132 $12.00
8 25252 $14.00 1/3/2007
9 3654 $145.00
10 6985 $26.00 2/12/2007
11 325785 $35.00
12 12132 $33.00
13 25252 $52.00
14 3654 $458.00
15 6985 $22.00
16 325785 $258.00 3/3/2007
17 12132 $45.00
18 25252 $65.00 4/1/2007
19 3654 $35.00
20 6985 $258.00
21 325785 $1.00 2/2/2007
22 12132 $138.50
23 25252 $143.65 1/15/2007
24 3654 $148.80
25 6985 $153.96 3/9/2007
26 325785 $159.11
27 12132 $164.26
28 25252 $169.42
29 3654 $174.57
30 6985 $179.72
31 325785 $184.88
32 12132 $190.03
33 25252 $195.18
34 3654 $200.34
35 6985 $205.49
36 325785 $210.65



***********END***********


"Frank Situmorang" wrote:

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