Automatically count number of payments in each payment group in Excel
up vote
-1
down vote
favorite
I have a spreadsheet that looks something like this:
DATE |PYMT AMT
01/01/2000|$1000
02/01/2000|$1000
....
12/01/2000|$1000
01/01/2001|$2000
02/01/2001|$2000
How can I create another table that looks like this:
Payment Count |Payment Amount
03 |$1000
02 |$2000
COUNTIF() helps me to know the payment amounts in advance, by using this formula, =COUNTIF(B1:B300,"1000").
But what if, I don't know in advance what they will be the amount?
microsoft-excel
edited Dec 2 at 6:33
Rajesh S
3,5401422
asked Nov 30 at 0:41
user348514
54212
|
show 1 more comment
up vote
-1
down vote
favorite
I have a spreadsheet that looks something like this:
DATE |PYMT AMT
01/01/2000|$1000
02/01/2000|$1000
....
12/01/2000|$1000
01/01/2001|$2000
02/01/2001|$2000
How can I create another table that looks like this:
Payment Count |Payment Amount
03 |$1000
02 |$2000
COUNTIF() helps me to know the payment amounts in advance, by using this formula, =COUNTIF(B1:B300,"1000").
But what if, I don't know in advance what they will be the amount?
microsoft-excel
edited Dec 2 at 6:33
Rajesh S
3,5401422
asked Nov 30 at 0:41
user348514
54212
Not very understanding your problem, could you provide a more clearly table about this problem?
– Lee
Nov 30 at 9:24
Do you want a Pivot Table? Can you please clarify your question.
– BruceWayne
Nov 30 at 15:22
There isn't really enough information for people to be able to answer. Are you looking for formulas that calculate payment amount for number of payments? Do you already have that and you just want to format your information into a table? What does your information look like? What formula are you using?
– fixer1234
Nov 30 at 23:11
I made some changes to clarify my question but they were rejected. So I'm trying again. I think some of the moderators are too picky.
– user348514
Dec 2 at 2:42
Do you mean that you would like to see the Payment Amount column of your output table to list all unique values from the existing table so that instead of Hard Coding e.g. 1000 in your COUNTIF formula, you shall just refer the cell reference? Also in your formula you are entering "1000" as Text (Because it's enclosed in Double Quotes), it will not match if the $ Values are Numerics.
– pat2015
Dec 2 at 6:40
|
show 1 more comment
up vote
-1
down vote
favorite
up vote
-1
down vote
favorite
I have a spreadsheet that looks something like this:
DATE |PYMT AMT
01/01/2000|$1000
02/01/2000|$1000
....
12/01/2000|$1000
01/01/2001|$2000
02/01/2001|$2000
How can I create another table that looks like this:
Payment Count |Payment Amount
03 |$1000
02 |$2000
COUNTIF() helps me to know the payment amounts in advance, by using this formula, =COUNTIF(B1:B300,"1000").
But what if, I don't know in advance what they will be the amount?
microsoft-excel
edited Dec 2 at 6:33
Rajesh S
3,5401422
asked Nov 30 at 0:41
user348514
54212
I have a spreadsheet that looks something like this:
DATE |PYMT AMT
01/01/2000|$1000
02/01/2000|$1000
....
12/01/2000|$1000
01/01/2001|$2000
02/01/2001|$2000
How can I create another table that looks like this:
Payment Count |Payment Amount
03 |$1000
02 |$2000
COUNTIF() helps me to know the payment amounts in advance, by using this formula, =COUNTIF(B1:B300,"1000").
But what if, I don't know in advance what they will be the amount?
microsoft-excel
microsoft-excel
edited Dec 2 at 6:33
Rajesh S
3,5401422
asked Nov 30 at 0:41
user348514
54212
edited Dec 2 at 6:33
Rajesh S
3,5401422
asked Nov 30 at 0:41
user348514
54212
edited Dec 2 at 6:33
Rajesh S
3,5401422
edited Dec 2 at 6:33
Rajesh S
3,5401422
edited Dec 2 at 6:33
Rajesh S
3,5401422
3,5401422
asked Nov 30 at 0:41
user348514
54212
asked Nov 30 at 0:41
user348514
54212
asked Nov 30 at 0:41
user348514
54212
54212
Not very understanding your problem, could you provide a more clearly table about this problem?
– Lee
Nov 30 at 9:24
Do you want a Pivot Table? Can you please clarify your question.
– BruceWayne
Nov 30 at 15:22
There isn't really enough information for people to be able to answer. Are you looking for formulas that calculate payment amount for number of payments? Do you already have that and you just want to format your information into a table? What does your information look like? What formula are you using?
– fixer1234
Nov 30 at 23:11
I made some changes to clarify my question but they were rejected. So I'm trying again. I think some of the moderators are too picky.
– user348514
Dec 2 at 2:42
Do you mean that you would like to see the Payment Amount column of your output table to list all unique values from the existing table so that instead of Hard Coding e.g. 1000 in your COUNTIF formula, you shall just refer the cell reference? Also in your formula you are entering "1000" as Text (Because it's enclosed in Double Quotes), it will not match if the $ Values are Numerics.
– pat2015
Dec 2 at 6:40
|
show 1 more comment
Not very understanding your problem, could you provide a more clearly table about this problem?
– Lee
Nov 30 at 9:24
Do you want a Pivot Table? Can you please clarify your question.
– BruceWayne
Nov 30 at 15:22
There isn't really enough information for people to be able to answer. Are you looking for formulas that calculate payment amount for number of payments? Do you already have that and you just want to format your information into a table? What does your information look like? What formula are you using?
– fixer1234
Nov 30 at 23:11
I made some changes to clarify my question but they were rejected. So I'm trying again. I think some of the moderators are too picky.
– user348514
Dec 2 at 2:42
Do you mean that you would like to see the Payment Amount column of your output table to list all unique values from the existing table so that instead of Hard Coding e.g. 1000 in your COUNTIF formula, you shall just refer the cell reference? Also in your formula you are entering "1000" as Text (Because it's enclosed in Double Quotes), it will not match if the $ Values are Numerics.
– pat2015
Dec 2 at 6:40
Not very understanding your problem, could you provide a more clearly table about this problem?
– Lee
Nov 30 at 9:24
Not very understanding your problem, could you provide a more clearly table about this problem?
– Lee
Nov 30 at 9:24
Do you want a Pivot Table? Can you please clarify your question.
– BruceWayne
Nov 30 at 15:22
Do you want a Pivot Table? Can you please clarify your question.
– BruceWayne
Nov 30 at 15:22
There isn't really enough information for people to be able to answer. Are you looking for formulas that calculate payment amount for number of payments? Do you already have that and you just want to format your information into a table? What does your information look like? What formula are you using?
– fixer1234
Nov 30 at 23:11
There isn't really enough information for people to be able to answer. Are you looking for formulas that calculate payment amount for number of payments? Do you already have that and you just want to format your information into a table? What does your information look like? What formula are you using?
– fixer1234
Nov 30 at 23:11
I made some changes to clarify my question but they were rejected. So I'm trying again. I think some of the moderators are too picky.
– user348514
Dec 2 at 2:42
I made some changes to clarify my question but they were rejected. So I'm trying again. I think some of the moderators are too picky.
– user348514
Dec 2 at 2:42
Do you mean that you would like to see the Payment Amount column of your output table to list all unique values from the existing table so that instead of Hard Coding e.g. 1000 in your COUNTIF formula, you shall just refer the cell reference? Also in your formula you are entering "1000" as Text (Because it's enclosed in Double Quotes), it will not match if the $ Values are Numerics.
– pat2015
Dec 2 at 6:40
Do you mean that you would like to see the Payment Amount column of your output table to list all unique values from the existing table so that instead of Hard Coding e.g. 1000 in your COUNTIF formula, you shall just refer the cell reference? Also in your formula you are entering "1000" as Text (Because it's enclosed in Double Quotes), it will not match if the $ Values are Numerics.
– pat2015
Dec 2 at 6:40
|
show 1 more comment
1 Answer
1
active
oldest
votes
up vote
0
down vote
I'm sure that you are looking for something like this.
I'm assuming that you have data in Range A2:B6.
How it works:
Write this Array Formula in cell
A10, finish withctrl+Shift+Enter& fill down.
{=IFERROR(INDEX($B$2:$B$6, MATCH(0,COUNTIF($A$9:A9, $B$2:$B$6), 0)),"")}
Enter this Formula in cell
B10& fill down.
=IF(A10="","No Val",(COUNTIF($B$2:$B6,A10)))
N.B.
- First part of above written formula
=IF(A10="","No Val",will help you to generate error if you don't know values or you don't have
values to count.
- Second part
(COUNTIF($B$2:$B6,A10)))will count the occurrence of payments made.
Adjust cell references in the formula as you need.
answered Dec 2 at 7:10
Rajesh S
3,5401422
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
I'm sure that you are looking for something like this.
I'm assuming that you have data in Range A2:B6.
How it works:
Write this Array Formula in cell
A10, finish withctrl+Shift+Enter& fill down.
{=IFERROR(INDEX($B$2:$B$6, MATCH(0,COUNTIF($A$9:A9, $B$2:$B$6), 0)),"")}
Enter this Formula in cell
B10& fill down.
=IF(A10="","No Val",(COUNTIF($B$2:$B6,A10)))
N.B.
- First part of above written formula
=IF(A10="","No Val",will help you to generate error if you don't know values or you don't have
values to count.
- Second part
(COUNTIF($B$2:$B6,A10)))will count the occurrence of payments made.
Adjust cell references in the formula as you need.
answered Dec 2 at 7:10
Rajesh S
3,5401422
add a comment |
up vote
0
down vote
I'm sure that you are looking for something like this.
I'm assuming that you have data in Range A2:B6.
How it works:
Write this Array Formula in cell
A10, finish withctrl+Shift+Enter& fill down.
{=IFERROR(INDEX($B$2:$B$6, MATCH(0,COUNTIF($A$9:A9, $B$2:$B$6), 0)),"")}
Enter this Formula in cell
B10& fill down.
=IF(A10="","No Val",(COUNTIF($B$2:$B6,A10)))
N.B.
- First part of above written formula
=IF(A10="","No Val",will help you to generate error if you don't know values or you don't have
values to count.
- Second part
(COUNTIF($B$2:$B6,A10)))will count the occurrence of payments made.
Adjust cell references in the formula as you need.
answered Dec 2 at 7:10
Rajesh S
3,5401422
add a comment |
up vote
0
down vote
up vote
0
down vote
I'm sure that you are looking for something like this.
I'm assuming that you have data in Range A2:B6.
How it works:
Write this Array Formula in cell
A10, finish withctrl+Shift+Enter& fill down.
{=IFERROR(INDEX($B$2:$B$6, MATCH(0,COUNTIF($A$9:A9, $B$2:$B$6), 0)),"")}
Enter this Formula in cell
B10& fill down.
=IF(A10="","No Val",(COUNTIF($B$2:$B6,A10)))
N.B.
- First part of above written formula
=IF(A10="","No Val",will help you to generate error if you don't know values or you don't have
values to count.
- Second part
(COUNTIF($B$2:$B6,A10)))will count the occurrence of payments made.
Adjust cell references in the formula as you need.
answered Dec 2 at 7:10
Rajesh S
3,5401422
I'm sure that you are looking for something like this.
I'm assuming that you have data in Range A2:B6.
How it works:
Write this Array Formula in cell
A10, finish withctrl+Shift+Enter& fill down.
{=IFERROR(INDEX($B$2:$B$6, MATCH(0,COUNTIF($A$9:A9, $B$2:$B$6), 0)),"")}
Enter this Formula in cell
B10& fill down.
=IF(A10="","No Val",(COUNTIF($B$2:$B6,A10)))
N.B.
- First part of above written formula
=IF(A10="","No Val",will help you to generate error if you don't know values or you don't have
values to count.
- Second part
(COUNTIF($B$2:$B6,A10)))will count the occurrence of payments made.
Adjust cell references in the formula as you need.
answered Dec 2 at 7:10
Rajesh S
3,5401422
answered Dec 2 at 7:10
Rajesh S
3,5401422
answered Dec 2 at 7:10
Rajesh S
3,5401422
answered Dec 2 at 7:10
Rajesh S
3,5401422
3,5401422
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1379613%2fautomatically-count-number-of-payments-in-each-payment-group-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Not very understanding your problem, could you provide a more clearly table about this problem?
– Lee
Nov 30 at 9:24
Do you want a Pivot Table? Can you please clarify your question.
– BruceWayne
Nov 30 at 15:22
There isn't really enough information for people to be able to answer. Are you looking for formulas that calculate payment amount for number of payments? Do you already have that and you just want to format your information into a table? What does your information look like? What formula are you using?
– fixer1234
Nov 30 at 23:11
I made some changes to clarify my question but they were rejected. So I'm trying again. I think some of the moderators are too picky.
– user348514
Dec 2 at 2:42
Do you mean that you would like to see the Payment Amount column of your output table to list all unique values from the existing table so that instead of Hard Coding e.g. 1000 in your COUNTIF formula, you shall just refer the cell reference? Also in your formula you are entering "1000" as Text (Because it's enclosed in Double Quotes), it will not match if the $ Values are Numerics.
– pat2015
Dec 2 at 6:40