SUM OF PAYABLES DUE ON A GIVEN DATE OR RANGE

  • Thread starter Thread starter la90292
  • Start date Start date
L

la90292

I HAVE AN ACCOUNTS PAYABLE WORK SHEET.

IN COLUMN G ARE THE DATES THAT A PARTICULAR PAYABLE IS DUE, i.e. 30-APR.

IN COLUMN H ARE THE AMOUNTS DUE FOR A PARTICULAR ACCOUNT, i.e. $1000.00.

IN CELL F62, I NEED A FORMULA THAT WILL SUM UP THE TOTAL AMOUNT DUE ON
30-APR. SO I KNOW HOW MUCH CASH IS NEEDED ON THAT DAY TO COVER THOSE
PAYABLES.

IN CELL F63, I NEED A FORMULA THAT WILL SUM UP THE TOTAL AMOUNT DUE FROM
25-APR TO 30-APR SO I KNOW HOW MUCH CASH IS NEEDED DURING THAT PERIOD.
 
If your dates due are in Column G and corresponding amounts due are in
Column H, then, in Cell F62 insert the formula:
=SUMIF($G$1:$H$19,$G$11,$H$1:$H$19) . This is for a total of 19 entries.
You'll probably have a lot more, so the $H$19 would have to be changed to
$H$xx, where "xx" is the total # of rows containing data. In my worksheet,
the $G$11 is the cell containing the date for which you want the total
payables due. In this case, it was 4/30/2005. So in F62, you will have the
total of all payables due on 4/30/2005.

Now, as to summing the payables due over a range of dates, SUMIF function
can only be used with one condition (a single date in your question:
4/30/2005). A crude workaround is to sum multiple SUMIF's in cell F63. All
you'd change in each concatenated SUMIF expression would be the cell that
contains each date within the range for which you want to get the total
payables.

There is an elegant solution to dealing with multiple criteria (dates, in
your case), called Arrays. If your interested, you can check out Chip
Pearson's web site http://www.cpearson.com/excel/array.htm Chip is top
notch at explaining Excel and his explanation of arrays is excellent.

If you want a sample worksheet with all this included, send me an email
address and I'll get it to you as an attachment. Cannot send attachments to
the group.

PJF
 
Thanks for the formula. I'll check out Chip's web site.

Maybe you could give me an example of the SUMIF function based on my work
sheet example for the week 4/25-4/30 or if your sample work sheet has it,
please send it to (e-mail address removed). Thanks so much!
 
la90292 shared this with us in microsoft.public.excel:
I HAVE AN ACCOUNTS PAYABLE WORK SHEET.

IN COLUMN G ARE THE DATES THAT A PARTICULAR PAYABLE IS DUE, i.e.
30-APR.

IN COLUMN H ARE THE AMOUNTS DUE FOR A PARTICULAR ACCOUNT, i.e.
$1000.00.

IN CELL F62, I NEED A FORMULA THAT WILL SUM UP THE TOTAL AMOUNT DUE ON
30-APR. SO I KNOW HOW MUCH CASH IS NEEDED ON THAT DAY TO COVER THOSE
PAYABLES.

IN CELL F63, I NEED A FORMULA THAT WILL SUM UP THE TOTAL AMOUNT DUE
FROM 25-APR TO 30-APR SO I KNOW HOW MUCH CASH IS NEEDED DURING THAT
PERIOD.
Click to expand...

Your keyboard is broken. The Shift Lock (or Caps Lock) key is stuck.

--
Amedee Van Gasse using XanaNews 1.17.3.1
If it has an "X" in the name, it must be Linux?

How To Ask Questions The Smart Way

How to Report Bugs Effectively
http://www.chiark.greenend.org.uk/~sgtatham/bugs.html
Only ask questions with yes/no answers if you want "yes" or "no" as the
answer.
http://homepages.tesco.net/~J.deBoynePollard/FGA/questions-with-yes-or-n
o-answers.html
 
=SUMIF(G2:G1000,--"2005-04-30",H2:H100)

and

=SUMPRODUCT(--(G2:G1000<=--"2005-04-30"),--(G2:G1000>=--"2005-04-25"),H2:H10
00)

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Back
Top