Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Is this possible
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default Is this possible
Date Description Debit Credit Balance
01-01-2005 0.00
07-01-2005 Cash Receipt Voucher 33,000.00 -33,000.00
12-01-2005 Cash Receipt Voucher 59,000.00 -92,000.00
18-01-2005 Credit Sales 423,012.00 331,012.00
21-01-2005 Cash Receipt Voucher 50,000.00 281,012.00
23-01-2005 Cash Receipt Voucher 58,000.00 223,012.00
26-01-2005 Cash Receipt Voucher 3,500.00 219,512.00
28-01-2005 Cash Receipt Voucher 69,300.00 150,212.00
29-01-2005 Credit Sales 331,988.00 60,000.00 422,200.00
29-01-2005 Cash Receipt Voucher 106,500.00 315,700.00
30-01-2005 Cash Receipt Voucher 191,000.00 124,700.00
31-01-2005 Cash Receipt Voucher 76,500.00 48,200.00
01-02-2005 Cash Receipt Voucher 41,000.00 7,200.00
08-02-2005 Cash Receipt Voucher 35,000.00 -27,800.00
26-03-2005 Cash Receipt Voucher 42,000.00 -69,800.00
27-03-2005 Credit Sales 347,565.00 277,765.00
30-03-2005 Cash Receipt Voucher 54,000.00 223,765.00
18-04-2005 Cash Receipt Voucher 96,000.00 127,765.00
18-04-2005 Credit Sales 349,383.00 477,148.00
02-05-2005 Cash Receipt Voucher 84,000.00 393,148.00
10-05-2005 Cash Receipt Voucher 35,000.00 358,148.00
11-05-2005 Credit Sales 502,216.00 860,364.00
01-06-2005 Cash Receipt Voucher 58,000.00 802,364.00
06-06-2005 Cash Receipt Voucher 69,000.00 733,364.00
06-06-2005 Cash Receipt Voucher 36,000.00 697,364.00
06-06-2005 Credit Sales 329,894.00 1,027,258.00
19-06-2005 Cash Receipt Voucher 80,000.00 947,258.00
04-07-2005 Cash Receipt Voucher 101,000.00 846,258.00
06-07-2005 Cash Receipt Voucher 60,000.00 786,258.00
17-07-2005 Cash Receipt Voucher 50,000.00 736,258.00
19-07-2005 Credit Sales 333,865.00 1,070,123.00
09-08-2005 Cash Receipt Voucher 130,000.00 940,123.00
21-08-2005 Cash Receipt Voucher 120,000.00 820,123.00
23-08-2005 Cash Receipt Voucher 85,500.00 734,623.00
24-08-2005 Credit Sales 330,117.66 1,064,740.66
03-09-2005 Cash Receipt Voucher 150,000.00 914,740.66
05-09-2005 Credit Sales 330,992.00 1,245,732.66
10-09-2005 Cash Receipt Voucher 110,000.00 1,135,732.66
12-09-2005 Cash Receipt Voucher 125,000.00 1,010,732.66
18-09-2005 Cash Receipt Voucher 80,400.00 930,332.66
27-09-2005 Cash Receipt Voucher 50,000.00 880,332.66
28-09-2005 Cash Receipt Voucher 20,500.00 859,832.66
29-09-2005 Cash Receipt Voucher 80,000.00 779,832.66
02-10-2005 Credit Sales 490,104.72 1,269,937.38
12-10-2005 Cash Receipt Voucher 293,000.00 976,937.38
13-10-2005 Cash Receipt Voucher 108,000.00 868,937.38
13-10-2005 Credit Sales 473,602.00 1,342,539.38
16-10-2005 Cash Receipt Voucher 125,000.00 1,217,539.38
17-10-2005 Cash Receipt Voucher 92,500.00 1,125,039.38
19-10-2005 Credit Sales 442,948.00 1,567,987.38
23-10-2005 Cash Receipt Voucher 125,000.00 1,442,987.38
25-10-2005 Cash Receipt Voucher 77,000.00 1,365,987.38
26-10-2005 Credit Sales 410,783.76 1,776,771.14
29-10-2005 Cash Receipt Voucher 149,500.00 1,627,271.14
31-10-2005 Cash Receipt Voucher 232,000.00 1,395,271.14
01-11-2005 Credit Sales 371,597.04 1,766,868.18
08-11-2005 Cash Receipt Voucher 309,500.00 1,457,368.18
19-11-2005 Cash Receipt Voucher 162,000.00 1,295,368.18
26-11-2005 Credit Sales 436,266.69 1,731,634.87
1,731,634.87
1,731,634.87
1,731,634.87
Balance 5,904,334.87 4,172,700.00



if I want to count or calculate the a mount or the value of every credit
sales that I've paied in 30 day or less and also which I pay between 31-60
days ect. Is this possible


Credit Sales Analysis 1 - 30 31 - 60 61 - 90 91- 120 121 - 150 151 - 180 180 +
Amount $423,012
$331,988


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Toppers
 
Posts: n/a
Default Is this possible
One way ...

Use something like this:

1-30
=SUMPRODUCT(($A$3:$A$101=$A$2)*($A$3:$A$101<=$A$2 +30)*($B$3:$B$101))

31-60
=SUMPRODUCT(($A$3:$A$101=$A$2+31)*($A$3:$A$101<=$ A$2+60)*($B$3:$B$101))

where column A has your dates, column B the values to added and $A$2=start
date

Adjust ranges to suit and add other SUMPRODUCTS formulae to fit your other
time periods

HTH


" wrote:

Date Description Debit Credit Balance
01-01-2005 0.00
07-01-2005 Cash Receipt Voucher 33,000.00 -33,000.00
12-01-2005 Cash Receipt Voucher 59,000.00 -92,000.00
18-01-2005 Credit Sales 423,012.00 331,012.00
21-01-2005 Cash Receipt Voucher 50,000.00 281,012.00
23-01-2005 Cash Receipt Voucher 58,000.00 223,012.00
26-01-2005 Cash Receipt Voucher 3,500.00 219,512.00
28-01-2005 Cash Receipt Voucher 69,300.00 150,212.00
29-01-2005 Credit Sales 331,988.00 60,000.00 422,200.00
29-01-2005 Cash Receipt Voucher 106,500.00 315,700.00
30-01-2005 Cash Receipt Voucher 191,000.00 124,700.00
31-01-2005 Cash Receipt Voucher 76,500.00 48,200.00
01-02-2005 Cash Receipt Voucher 41,000.00 7,200.00
08-02-2005 Cash Receipt Voucher 35,000.00 -27,800.00
26-03-2005 Cash Receipt Voucher 42,000.00 -69,800.00
27-03-2005 Credit Sales 347,565.00 277,765.00
30-03-2005 Cash Receipt Voucher 54,000.00 223,765.00
18-04-2005 Cash Receipt Voucher 96,000.00 127,765.00
18-04-2005 Credit Sales 349,383.00 477,148.00
02-05-2005 Cash Receipt Voucher 84,000.00 393,148.00
10-05-2005 Cash Receipt Voucher 35,000.00 358,148.00
11-05-2005 Credit Sales 502,216.00 860,364.00
01-06-2005 Cash Receipt Voucher 58,000.00 802,364.00
06-06-2005 Cash Receipt Voucher 69,000.00 733,364.00
06-06-2005 Cash Receipt Voucher 36,000.00 697,364.00
06-06-2005 Credit Sales 329,894.00 1,027,258.00
19-06-2005 Cash Receipt Voucher 80,000.00 947,258.00
04-07-2005 Cash Receipt Voucher 101,000.00 846,258.00
06-07-2005 Cash Receipt Voucher 60,000.00 786,258.00
17-07-2005 Cash Receipt Voucher 50,000.00 736,258.00
19-07-2005 Credit Sales 333,865.00 1,070,123.00
09-08-2005 Cash Receipt Voucher 130,000.00 940,123.00
21-08-2005 Cash Receipt Voucher 120,000.00 820,123.00
23-08-2005 Cash Receipt Voucher 85,500.00 734,623.00
24-08-2005 Credit Sales 330,117.66 1,064,740.66
03-09-2005 Cash Receipt Voucher 150,000.00 914,740.66
05-09-2005 Credit Sales 330,992.00 1,245,732.66
10-09-2005 Cash Receipt Voucher 110,000.00 1,135,732.66
12-09-2005 Cash Receipt Voucher 125,000.00 1,010,732.66
18-09-2005 Cash Receipt Voucher 80,400.00 930,332.66
27-09-2005 Cash Receipt Voucher 50,000.00 880,332.66
28-09-2005 Cash Receipt Voucher 20,500.00 859,832.66
29-09-2005 Cash Receipt Voucher 80,000.00 779,832.66
02-10-2005 Credit Sales 490,104.72 1,269,937.38
12-10-2005 Cash Receipt Voucher 293,000.00 976,937.38
13-10-2005 Cash Receipt Voucher 108,000.00 868,937.38
13-10-2005 Credit Sales 473,602.00 1,342,539.38
16-10-2005 Cash Receipt Voucher 125,000.00 1,217,539.38
17-10-2005 Cash Receipt Voucher 92,500.00 1,125,039.38
19-10-2005 Credit Sales 442,948.00 1,567,987.38
23-10-2005 Cash Receipt Voucher 125,000.00 1,442,987.38
25-10-2005 Cash Receipt Voucher 77,000.00 1,365,987.38
26-10-2005 Credit Sales 410,783.76 1,776,771.14
29-10-2005 Cash Receipt Voucher 149,500.00 1,627,271.14
31-10-2005 Cash Receipt Voucher 232,000.00 1,395,271.14
01-11-2005 Credit Sales 371,597.04 1,766,868.18
08-11-2005 Cash Receipt Voucher 309,500.00 1,457,368.18
19-11-2005 Cash Receipt Voucher 162,000.00 1,295,368.18
26-11-2005 Credit Sales 436,266.69 1,731,634.87
1,731,634.87
1,731,634.87
1,731,634.87
Balance 5,904,334.87 4,172,700.00



if I want to count or calculate the a mount or the value of every credit
sales that I've paied in 30 day or less and also which I pay between 31-60
days ect. Is this possible


Credit Sales Analysis 1 - 30 31 - 60 61 - 90 91- 120 121 - 150 151 - 180 180 +
Amount $423,012
$331,988


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Is this possible
=SUMPRODUCT(--(TODAY()-A2:A2000),--(TODAY()-A2:A200<=30))

for the count

=SUMPRODUCT(--(TODAY()-A2:A2000),--(TODAY()-A2:A200<=30),C2:C200)

for the amount

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"
wrote in message ...
Date Description Debit Credit Balance
01-01-2005 0.00
07-01-2005 Cash Receipt Voucher 33,000.00 -33,000.00
12-01-2005 Cash Receipt Voucher 59,000.00 -92,000.00
18-01-2005 Credit Sales 423,012.00 331,012.00
21-01-2005 Cash Receipt Voucher 50,000.00 281,012.00
23-01-2005 Cash Receipt Voucher 58,000.00 223,012.00
26-01-2005 Cash Receipt Voucher 3,500.00 219,512.00
28-01-2005 Cash Receipt Voucher 69,300.00 150,212.00
29-01-2005 Credit Sales 331,988.00 60,000.00 422,200.00
29-01-2005 Cash Receipt Voucher 106,500.00 315,700.00
30-01-2005 Cash Receipt Voucher 191,000.00 124,700.00
31-01-2005 Cash Receipt Voucher 76,500.00 48,200.00
01-02-2005 Cash Receipt Voucher 41,000.00 7,200.00
08-02-2005 Cash Receipt Voucher 35,000.00 -27,800.00
26-03-2005 Cash Receipt Voucher 42,000.00 -69,800.00
27-03-2005 Credit Sales 347,565.00 277,765.00
30-03-2005 Cash Receipt Voucher 54,000.00 223,765.00
18-04-2005 Cash Receipt Voucher 96,000.00 127,765.00
18-04-2005 Credit Sales 349,383.00 477,148.00
02-05-2005 Cash Receipt Voucher 84,000.00 393,148.00
10-05-2005 Cash Receipt Voucher 35,000.00 358,148.00
11-05-2005 Credit Sales 502,216.00 860,364.00
01-06-2005 Cash Receipt Voucher 58,000.00 802,364.00
06-06-2005 Cash Receipt Voucher 69,000.00 733,364.00
06-06-2005 Cash Receipt Voucher 36,000.00 697,364.00
06-06-2005 Credit Sales 329,894.00 1,027,258.00
19-06-2005 Cash Receipt Voucher 80,000.00 947,258.00
04-07-2005 Cash Receipt Voucher 101,000.00 846,258.00
06-07-2005 Cash Receipt Voucher 60,000.00 786,258.00
17-07-2005 Cash Receipt Voucher 50,000.00 736,258.00
19-07-2005 Credit Sales 333,865.00 1,070,123.00
09-08-2005 Cash Receipt Voucher 130,000.00 940,123.00
21-08-2005 Cash Receipt Voucher 120,000.00 820,123.00
23-08-2005 Cash Receipt Voucher 85,500.00 734,623.00
24-08-2005 Credit Sales 330,117.66 1,064,740.66
03-09-2005 Cash Receipt Voucher 150,000.00 914,740.66
05-09-2005 Credit Sales 330,992.00 1,245,732.66
10-09-2005 Cash Receipt Voucher 110,000.00 1,135,732.66
12-09-2005 Cash Receipt Voucher 125,000.00 1,010,732.66
18-09-2005 Cash Receipt Voucher 80,400.00 930,332.66
27-09-2005 Cash Receipt Voucher 50,000.00 880,332.66
28-09-2005 Cash Receipt Voucher 20,500.00 859,832.66
29-09-2005 Cash Receipt Voucher 80,000.00 779,832.66
02-10-2005 Credit Sales 490,104.72 1,269,937.38
12-10-2005 Cash Receipt Voucher 293,000.00 976,937.38
13-10-2005 Cash Receipt Voucher 108,000.00 868,937.38
13-10-2005 Credit Sales 473,602.00 1,342,539.38
16-10-2005 Cash Receipt Voucher 125,000.00 1,217,539.38
17-10-2005 Cash Receipt Voucher 92,500.00 1,125,039.38
19-10-2005 Credit Sales 442,948.00 1,567,987.38
23-10-2005 Cash Receipt Voucher 125,000.00 1,442,987.38
25-10-2005 Cash Receipt Voucher 77,000.00 1,365,987.38
26-10-2005 Credit Sales 410,783.76 1,776,771.14
29-10-2005 Cash Receipt Voucher 149,500.00 1,627,271.14
31-10-2005 Cash Receipt Voucher 232,000.00 1,395,271.14
01-11-2005 Credit Sales 371,597.04 1,766,868.18
08-11-2005 Cash Receipt Voucher 309,500.00 1,457,368.18
19-11-2005 Cash Receipt Voucher 162,000.00 1,295,368.18
26-11-2005 Credit Sales 436,266.69 1,731,634.87
1,731,634.87
1,731,634.87
1,731,634.87
Balance 5,904,334.87 4,172,700.00



if I want to count or calculate the a mount or the value of every credit
sales that I've paied in 30 day or less and also which I pay between
31-60
days ect. Is this possible


Credit Sales Analysis 1 - 30 31 - 60 61 - 90 91- 120 121 - 150 151 - 180
180 +
Amount $423,012
$331,988




Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


All times are GMT +1. The time now is 02:47 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement