Auto number count up but for same value not count

sophea_tum

New Member
Joined
May 8, 2015
Messages
42
Hi,

I am wonder how can i get the result as in "Auto Number" column:

A titleB TitleC TitleD DateAuto Number
A1
B1Yes28-Jun-181
A1
B1Yes28-Jun-181
A1B2No
22-Jul-18
1
A1
B1Yes23-Jul-182
A4
B4Yes20-Aug-18
1
A5
B3Yes23-Aug-18
1
A5
B3Yes1-Sep-182
A6B6
Yes
3-Sep-181

<tbody>
</tbody>

When A, B C D column is the same value, it count only 1, but when A, B C same but D different it will count one more over the A,B,C that with same value.

Sorry it is difficult to explain but please look the table above than we will understand that result that I want.

Please help me!!! I am stuck with that!

thanks,
Sophea.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
Please try this:

in cell E2 put:
=IF(COUNTIFS($A$1:$A4,A5,$B$1:$B4,B5,$C$1:$C4,C5,$D$1:$D4,D5)>0,1,MAXIFS($E$1:$E4,$A$1:$A4,A5,$B$1:$B4,B5,$C$1:$C4,C5)+1)

Copy the formula down.

This assumes you have the table starting in cell A1
 

Forum statistics

Threads
1,141,316
Messages
5,705,708
Members
421,406
Latest member
kluna90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top