# Duplicate Occurence Number Count

#### matt-gilbert

##### Member
Hi all,
My data in column A has duplicates. I am trying to write a formula in column B to identify the occurence number count of each duplicate. Desired results as follows;
A B
Red 1
Yellow 1
Green 1
Yellow 2
Blue 1
Red 2
Yellow 3
I thought this was going to be simple however I have been searching internet for over an hour now and all I can find are formulas which give me a total count of duplicates. Thanks in advance.
Matt

#### Hui

##### Excel Ninja
Staff member
I assume your data is in A2:Ax
In B2: =COUNTIF(A\$2:A2,A2)
Copy down

#### matt-gilbert

##### Member
Thanks Hui. I had a feeling it might be that simple...

##### New Member
I need to count the number of invoices which are having bill type as F2 & F8.

Bill Type Invoice
F2 90309330
F8 90309350
F2 90309351
F8 90309352
F2 90309354
F2 90309357
F2 90309358
F2 90309359
F2 90309360
RE 90309362
F2 90309363
F2 90309363
F2 90309363
F2 90309364
F2 90309365
F2 90309366
F8 90311860
F8 90311861
F8 90311862
F2 90311863
F2 90311866

#### Somendra Misra

##### Excel Ninja
Hi Sunil,

See below for the formulas.

Regards,

##### New Member
Hi Sunil,

See below for the formulas.

View attachment 7373

Regards,

Hi Somendra,

Thanks for the post, i think i couldn't put my query right. I have to count total no of invoices which are of bill type F2 & F8. there are few duplicate entries in coloumn "Invoice" which need not be counted.

The workbook is attached, kindly help.

#### Attachments

• 8.6 KB Views: 10

#### Somendra Misra

##### Excel Ninja

Try below array formula.

=SUMPRODUCT(IF(FREQUENCY(IF((\$A\$2:\$A\$22="F2")+(\$A\$2:\$A\$22="F8"),\$B\$2:\$B\$22),\$B\$2:\$B\$22),1,0))

Confirm with Ctrl+Shift+Enter.

Regards,

#### Debraj

##### Excel Ninja
Hi Sunil,

here another approach.. using Ctrl + Shift + Enter

=SUM(IF(A2:A22={"F2","F8"},1/COUNTIFS(B2:B22,B2:B22,A2:A22,{"F2","F8"})))
• + Point..
• If Invoice Number are in AlphaNumeric.. Still it will Work..
• - Point..
• In case of blank INVOICE #, it will fail..
unlike @Somendra Misra 's formula..

#### Somendra Misra

##### Excel Ninja
Another alternative to overcome cons raised by @Debraj Dada....

=SUMPRODUCT(IF(FREQUENCY(IFERROR(MATCH(IF((\$A\$2:\$A\$5000="F2")+(\$A\$2:\$A\$5000="F8"),\$B\$2:\$B\$5000),\$B\$2:\$B\$5000,0),"e"),IFERROR(MATCH(IF((\$A\$2:\$A\$5000="F2")+(\$A\$2:\$A\$5000="F8"),\$B\$2:\$B\$5000),\$B\$2:\$B\$5000,0),"e")),1,0))

• +ve Points.. Include both alphanumeric + blanks cases
• Faster really fast
• -ve Points...Longer formula
Regards,

##### New Member

Try below array formula.

=SUMPRODUCT(IF(FREQUENCY(IF((\$A\$2:\$A\$22="F2")+(\$A\$2:\$A\$22="F8"),\$B\$2:\$B\$22),\$B\$2:\$B\$22),1,0))

Confirm with Ctrl+Shift+Enter.

Regards,

Thank You Very Much, Somendra.

Formula has worked.

Cheers.

##### New Member
Hi Sunil,

here another approach.. using Ctrl + Shift + Enter

=SUM(IF(A2:A22={"F2","F8"},1/COUNTIFS(B2:B22,B2:B22,A2:A22,{"F2","F8"})))
• + Point..
• If Invoice Number are in AlphaNumeric.. Still it will Work..
• - Point..
• In case of blank INVOICE #, it will fail..
unlike @Somendra Misra 's formula..

Thanks a lot Debraj,

The formula has worked for me.

Cheers.

#### Somendra Misra

##### Excel Ninja

Welcome back anytime!!!

Regards,

##### New Member
Hi All,

Please see to attachment, kindly help me with the formula.

#### Attachments

• 38.9 KB Views: 6

#### Somendra Misra

##### Excel Ninja

See the attached file, green cells with formula.

Regards,

#### Attachments

• 40.4 KB Views: 8

##### New Member

See the attached file, green cells with formula.

Regards,

Thanks a ton Somendra Bhai,

Could you please also help with the attached query.

#### Attachments

• 34.9 KB Views: 2

#### Somendra Misra

##### Excel Ninja
Hi Sunil,

See the attached file.

Solutions with two method.
1. Formula based (Yellow Cells)
2. Pivot Table.

Regards,

#### Attachments

• 40.1 KB Views: 6

##### New Member
Hi Sunil,

See the attached file.

Solutions with two method.
1. Formula based (Yellow Cells)
2. Pivot Table.

Regards,
Hi Somendra,

I wanted to do it by formula method, there were some duplicate value in coloumn invoice which should not be counted.

Kindly check the attachment and suggest formula to do it.

#### Attachments

• 39.5 KB Views: 3

#### Somendra Misra

##### Excel Ninja
Try Below array formula in F8 and copy down:

=SUMPRODUCT(IF(FREQUENCY(IF(\$A\$2:\$A\$21=E8,IF((\$B\$2:\$B\$21="F2")+(\$B\$2:\$B\$21="F8"),\$C\$2:\$C\$21)),\$C\$2:\$C\$21),1,0))

Enter with Ctrl+Shift+Enter.

Regards,

##### New Member
Try Below array formula in F8 and copy down:

=SUMPRODUCT(IF(FREQUENCY(IF(\$A\$2:\$A\$21=E8,IF((\$B\$2:\$B\$21="F2")+(\$B\$2:\$B\$21="F8"),\$C\$2:\$C\$21)),\$C\$2:\$C\$21),1,0))

Enter with Ctrl+Shift+Enter.

Regards,

Thank you Somendra.

#### Attachments

• 15.1 KB Views: 1

#### Somendra Misra

##### Excel Ninja

Can you fill in the complete week column manually stating what week it will be?

Regards,

##### New Member

Can you fill in the complete week column manually stating what week it will be?

Regards,

PFA the updated file

#### Attachments

• 16.6 KB Views: 6

#### Haseeb A

##### Active Member
Hello Sunil,

May be this...

="Week "&INT((A2-DATE(YEAR(A2),MONTH(A2)-(DAY(A2)<26),25)-1)/7)+1

Or with EOMONTH

="Week "&INT((A2-(EOMONTH(A2,-1-(DAY(A2)<26))+25)-1)/7)+1

##### New Member
Hello Sunil,

May be this...

="Week "&INT((A2-DATE(YEAR(A2),MONTH(A2)-(DAY(A2)<26),25)-1)/7)+1
Or with EOMONTH
="Week "&INT((A2-(EOMONTH(A2,-1-(DAY(A2)<26))+25)-1)/7)+1

Thank you Haseeb

Hi Everbody,

#### Attachments

• 32.7 KB Views: 3

Hi Sunil ,