Increment value based on multiple criteria

[tinkythomas]

Hi,

I want to increment a number in an adjacent column to my data based on 2 criteria. Firstly the value in ColA is the same and the value in ColB is the same.

For example the following data...

ColA, ColB
E6322001, T1
E6322001, T1
E6322001, T2
E6322001, T2

The result I'm after a count in ColC...
ColA, ColB, ColC
E6322001, T1, 1
E6322001, T1, 2
E6322001, T2, 1
E6322001, T2, 2

I tried using the CountA function (COUNTA($A$1:A1)) but that only counts the 1 column and I end up with...

ColA, ColB, ColC
E6322001, T1, 1
E6322001, T1, 2
E6322001, T2, 3
E6322001, T2, 4

Any help would be appreciated.

 

[Haseeb Avarakkan]

Hello,

C1, copy down.

=COUNTIF(B$1:B1,B1)

If you want to Count Col_A & Col_B,

C1, copy down.

=SUMPRODUCT(--(A$1:A1=A1),--(B$1:B1=B1))

Excel 2007 or later,

C1, copy down.

=COUNTIFS(A$1:A1,A1,B$1:B1,B1)

 

[tinkythomas]

If you want to Count Col_A & Col_B,

C1, copy down.

=SUMPRODUCT(--(A$1:A1=A1),--(B$1:B1=B1))

Thank you very much the SUMPRODUCT function gives me the desired result.

I must confess that I don't really understand how this function works? If you don't mind could you please explain how it works?

Thank you,

 

[T. Valko]

Thank you very much the SUMPRODUCT function gives me the desired result.

I must confess that I don't really understand how this function works? If you don't mind could you please explain how it works?

Thank you,

See this...

http://xldynamic.com/source/xld.SUMPRODUCT.html

 

[tinkythomas]

Thank you very much for the link. I now understand a little better! It's like anything else, the more you use something in real world scenarios the easier it is to uderstand.

Regards,

 

[T. Valko]

Thank you very much for the link. I now understand a little better! It's like anything else, the more you use something in real world scenarios the easier it is to uderstand.

Regards,

You're welcome. Thanks for the feedback!