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 were: 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 company’s
economic/cultural/business infrastructure and become your boss’s 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
36) [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 said:
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