Automatic sequential numbering where a value changes

Hello!
I have items coming to me from various areas, which get recorded in a
spreadsheet and given the next number in a list. A manager reviews the item,
and then approves or otherwise
So the spreadsheet looks like this:
Request number; Area; Details ;Approved/Not Approved
1 - Green - DaDa - Approved
2 - Green - DaDa - Approved
3 - Red - DaDa - Approved
4 - Red - DaDa - Not Approved
5 - Green - DaDa - Approved
6 - Red - DaDa - Approved

I want to put in a column that automatically puts in the approval number for
each area. So in this example, request 5 would be approval number 3 for the
Green area, and request 6 would be approval number 2 for the red area.
I have been trying to do an if statement where if E= "approved", but cannot
get the value if true statement to do a count of the number of times there
has already been an approved for a particular area.

Would appreciate some assistance

I think you are looking for a formula like this...

=SUMPRODUCT((B2:B1000="Red")*(D21000="Approved") )

Just change the color in the first logical test as needed and change the
ranges (use same row limits) so it covers the maximum number of rows you
might ever expect to have filled in.

--
Rick (MVP - Excel)



"suek" wrote in message
...
Hello!
I have items coming to me from various areas, which get recorded in a
spreadsheet and given the next number in a list. A manager reviews the
item,
and then approves or otherwise
So the spreadsheet looks like this:
Request number; Area; Details ;Approved/Not Approved
1 - Green - DaDa - Approved
2 - Green - DaDa - Approved
3 - Red - DaDa - Approved
4 - Red - DaDa - Not Approved
5 - Green - DaDa - Approved
6 - Red - DaDa - Approved

I want to put in a column that automatically puts in the approval number
for
each area. So in this example, request 5 would be approval number 3 for
the
Green area, and request 6 would be approval number 2 for the red area.
I have been trying to do an if statement where if E= "approved", but
cannot
get the value if true statement to do a count of the number of times there
has already been an approved for a particular area.

Would appreciate some assistance

Assume that you are having the data like the below:

Col / Row A B C D E
Row1 Req No. Area Details Approved/NotApproved Approval No's
Row2 1 Green DaDa Approved
Row3 2 Green DaDa Approved
Row4 3 Red DaDa Approved
Row5 4 Red DaDa Not Approved
Row6 5 Green DaDa Approved
Row7 6 Red DaDa Approved

Copy and past the below formula in E2 cell.

=IF($D2="APPROVED","Approval No:
"&SUMPRODUCT(($B$2:$B2=B2)*($D$2:$D2="APPROVED")), "")

Now copy the E2 cell and paste it to the remaining cells of E column based
on the B & D Column Data.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"suek" wrote:

Hello!
I have items coming to me from various areas, which get recorded in a
spreadsheet and given the next number in a list. A manager reviews the item,
and then approves or otherwise
So the spreadsheet looks like this:
Request number; Area; Details ;Approved/Not Approved
1 - Green - DaDa - Approved
2 - Green - DaDa - Approved
3 - Red - DaDa - Approved
4 - Red - DaDa - Not Approved
5 - Green - DaDa - Approved
6 - Red - DaDa - Approved

I want to put in a column that automatically puts in the approval number for
each area. So in this example, request 5 would be approval number 3 for the
Green area, and request 6 would be approval number 2 for the red area.
I have been trying to do an if statement where if E= "approved", but cannot
get the value if true statement to do a count of the number of times there
has already been an approved for a particular area.

Would appreciate some assistance

Assuming your data starts from row2 with headers in row1; in cell E2 try the
below formula and copy down as required..

=IF(D2="Approved",SUMPRODUCT(($B$2:$B2=B2)*($D$2:$ D2="Approved")),"")

--
Jacob (MVP - Excel)


"suek" wrote:

Hello!
I have items coming to me from various areas, which get recorded in a
spreadsheet and given the next number in a list. A manager reviews the item,
and then approves or otherwise
So the spreadsheet looks like this:
Request number; Area; Details ;Approved/Not Approved
1 - Green - DaDa - Approved
2 - Green - DaDa - Approved
3 - Red - DaDa - Approved
4 - Red - DaDa - Not Approved
5 - Green - DaDa - Approved
6 - Red - DaDa - Approved

I want to put in a column that automatically puts in the approval number for
each area. So in this example, request 5 would be approval number 3 for the
Green area, and request 6 would be approval number 2 for the red area.
I have been trying to do an if statement where if E= "approved", but cannot
get the value if true statement to do a count of the number of times there
has already been an approved for a particular area.

Would appreciate some assistance

Hi,
If your Details is column B and Approved/Not is column D use the below
formula in the first row and just copy it down and you will get what you need.
=SUMPRODUCT(--(B1:$B$1=B1),--(D1:$D$1="Approved"))
--
If this helps, please click "Yes"



"suek" wrote:

Hello!
I have items coming to me from various areas, which get recorded in a
spreadsheet and given the next number in a list. A manager reviews the item,
and then approves or otherwise
So the spreadsheet looks like this:
Request number; Area; Details ;Approved/Not Approved
1 - Green - DaDa - Approved
2 - Green - DaDa - Approved
3 - Red - DaDa - Approved
4 - Red - DaDa - Not Approved
5 - Green - DaDa - Approved
6 - Red - DaDa - Approved

I want to put in a column that automatically puts in the approval number for
each area. So in this example, request 5 would be approval number 3 for the
Green area, and request 6 would be approval number 2 for the red area.
I have been trying to do an if statement where if E= "approved", but cannot
get the value if true statement to do a count of the number of times there
has already been an approved for a particular area.

Would appreciate some assistance