How to consider duplicate cell/number as one in VLOOKUP

[Abuahmed]

DATA

[TABLE="width: 705"]
<tbody>[TR]
[TD]PO NO[/TD]
[TD]Vendor No.[/TD]
[TD]Vendor Name[/TD]
[TD]Account[/TD]
[TD] US$ [/TD]
[TD]Posting Date[/TD]
[TD]COMMENTS[/TD]
[/TR]
[TR]
[TD]45280[/TD]
[TD]12031[/TD]
[TD]ABC COMPANY[/TD]
[TD]11002002[/TD]
[TD] 200.00 [/TD]
[TD]31-12-2016[/TD]
[TD]NO INVOICE[/TD]
[/TR]
[TR]
[TD]45280[/TD]
[TD]12031[/TD]
[TD]ABC COMPANY[/TD]
[TD]11002003[/TD]
[TD] 350.00 [/TD]
[TD]31-12-2016[/TD]
[TD]NO INVOICE[/TD]
[/TR]
[TR]
[TD]45280[/TD]
[TD]12031[/TD]
[TD]ABC COMPANY[/TD]
[TD]11002004[/TD]
[TD] 300.00 [/TD]
[TD]31-12-2016[/TD]
[TD]NO INVOICE[/TD]
[/TR]
[TR]
[TD]45280[/TD]
[TD]12031[/TD]
[TD]ABC COMPANY[/TD]
[TD]11002005[/TD]
[TD] 270.00 [/TD]
[TD]31-12-2016[/TD]
[TD]NO INVOICE[/TD]
[/TR]
[TR]
[TD]45230[/TD]
[TD]12008[/TD]
[TD]XYZ COMPANY[/TD]
[TD]11002090[/TD]
[TD] 2,500.00 [/TD]
[TD]31-12-2015[/TD]
[TD]WRONG ADDRESS[/TD]
[/TR]
[TR]
[TD]45233[/TD]
[TD]12008[/TD]
[TD]XYZ COMPANY[/TD]
[TD]11002090[/TD]
[TD] 3,000.00 [/TD]
[TD]31-12-2015[/TD]
[TD]WRONG ADDRESS[/TD]
[/TR]
[TR]
[TD]45263[/TD]
[TD]12031[/TD]
[TD]GOOD LLC[/TD]
[TD]11002090[/TD]
[TD] 500.00 [/TD]
[TD]31-12-2015[/TD]
[TD]NO INVOICE[/TD]
[/TR]
[TR]
[TD]45275[/TD]
[TD]11042[/TD]
[TD]LINE COMPANY[/TD]
[TD]11002090[/TD]
[TD] 300.00 [/TD]
[TD]31-12-2015[/TD]
[TD]WRONG AMOUNT[/TD]
[/TR]
[TR]
[TD]45275[/TD]
[TD]11042[/TD]
[TD]LINE COMPANY[/TD]
[TD]11002091[/TD]
[TD] 400.00 [/TD]
[TD]31-12-2015[/TD]
[TD]WRONG AMOUNT[/TD]
[/TR]
[TR]
[TD]45435[/TD]
[TD]11288[/TD]
[TD]JK LLC[/TD]
[TD]11002090[/TD]
[TD] 700.00 [/TD]
[TD]31-12-2015[/TD]
[TD]NO INVOICE[/TD]
[/TR]
[TR]
[TD]45480[/TD]
[TD]10038[/TD]
[TD]LOGIC COMPANY[/TD]
[TD]11002090[/TD]
[TD] 80.00 [/TD]
[TD]31-12-2016[/TD]
[TD]CLEAN INVOICE[/TD]
[/TR]
[TR]
[TD]45480[/TD]
[TD]10038[/TD]
[TD]LOGIC COMPANY[/TD]
[TD]11002080[/TD]
[TD] 50.00 [/TD]
[TD]31-12-2016[/TD]
[TD]CLEAN INVOICE[/TD]
[/TR]
[TR]
[TD]45480[/TD]
[TD]10038[/TD]
[TD]LOGIC COMPANY[/TD]
[TD]11002090[/TD]
[TD] 30.00 [/TD]
[TD]31-12-2016[/TD]
[TD]CLEAN INVOICE[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>[/TABLE]

When I run vlookup I am getting the below result: It count NO PO including duplicate numbers:

[TABLE="width: 292"]
<tbody>[TR]
[TD]COMMENTS[/TD]
[TD]Count of PO NO[/TD]
[TD]Sum of US$[/TD]
[/TR]
[TR]
[TD]CLEAN INVOICE[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]160[/TD]
[/TR]
[TR]
[TD]NO INVOICE[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2320[/TD]
[/TR]
[TR]
[TD]WRONG ADDRESS[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5500[/TD]
[/TR]
[TR]
[TD]WRONG AMOUNT[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]700[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]8680[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]


I do not want counting duplicate just consider as one. like below example:
how can I get the below result in the vlookup please.
[TABLE="width: 292"]
<tbody>[TR]
[TD]COMMENTS[/TD]
[TD]Count of PO NO[/TD]
[TD]Sum of US$[/TD]
[/TR]
[TR]
[TD]CLEAN INVOICE[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]160[/TD]
[/TR]
[TR]
[TD]NO INVOICE[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2320[/TD]
[/TR]
[TR]
[TD]WRONG ADDRESS[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5500[/TD]
[/TR]
[TR]
[TD]WRONG AMOUNT[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]700[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8680[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

 

[admiral100]

Hi,

B19 =SUMPRODUCT(--1/COUNTIF($A$2:$A$14,$A$2:$A$14),--($G$2:$G$14=A19))

C19 =SUMPRODUCT(--($G$2:$G$14=A19),$E$2:$E$14)

[TABLE="width: 814"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]PO NO[/TD]
[TD]Vendor No.[/TD]
[TD]Vendor Name[/TD]
[TD]Account[/TD]
[TD]US$[/TD]
[TD]Posting Date[/TD]
[TD]COMMENTS[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]45280[/TD]
[TD]12031[/TD]
[TD]ABC COMPANY[/TD]
[TD]11002002[/TD]
[TD]200[/TD]
[TD]31/12/16[/TD]
[TD]NO INVOICE[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]45280[/TD]
[TD]12031[/TD]
[TD]ABC COMPANY[/TD]
[TD]11002003[/TD]
[TD]350[/TD]
[TD]31/12/16[/TD]
[TD]NO INVOICE[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]45280[/TD]
[TD]12031[/TD]
[TD]ABC COMPANY[/TD]
[TD]11002004[/TD]
[TD]300[/TD]
[TD]31/12/16[/TD]
[TD]NO INVOICE[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]45280[/TD]
[TD]12031[/TD]
[TD]ABC COMPANY[/TD]
[TD]11002005[/TD]
[TD]270[/TD]
[TD]31/12/16[/TD]
[TD]NO INVOICE[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]45230[/TD]
[TD]12008[/TD]
[TD]XYZ COMPANY[/TD]
[TD]11002090[/TD]
[TD]2,500.00[/TD]
[TD]31/12/15[/TD]
[TD]WRONG ADDRESS[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]45233[/TD]
[TD]12008[/TD]
[TD]XYZ COMPANY[/TD]
[TD]11002090[/TD]
[TD]3,000.00[/TD]
[TD]31/12/15[/TD]
[TD]WRONG ADDRESS[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]45263[/TD]
[TD]12031[/TD]
[TD]GOOD LLC[/TD]
[TD]11002090[/TD]
[TD]500[/TD]
[TD]31/12/15[/TD]
[TD]NO INVOICE[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]45275[/TD]
[TD]11042[/TD]
[TD]LINE COMPANY[/TD]
[TD]11002090[/TD]
[TD]300[/TD]
[TD]31/12/15[/TD]
[TD]WRONG AMOUNT[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]45275[/TD]
[TD]11042[/TD]
[TD]LINE COMPANY[/TD]
[TD]11002091[/TD]
[TD]400[/TD]
[TD]31/12/15[/TD]
[TD]WRONG AMOUNT[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]45435[/TD]
[TD]11288[/TD]
[TD]JK LLC[/TD]
[TD]11002090[/TD]
[TD]700[/TD]
[TD]31/12/15[/TD]
[TD]NO INVOICE[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]45480[/TD]
[TD]10038[/TD]
[TD]LOGIC COMPANY[/TD]
[TD]11002090[/TD]
[TD]80[/TD]
[TD]31/12/16[/TD]
[TD]CLEAN INVOICE[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]45480[/TD]
[TD]10038[/TD]
[TD]LOGIC COMPANY[/TD]
[TD]11002080[/TD]
[TD]50[/TD]
[TD]31/12/16[/TD]
[TD]CLEAN INVOICE[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]45480[/TD]
[TD]10038[/TD]
[TD]LOGIC COMPANY[/TD]
[TD]11002090[/TD]
[TD]30[/TD]
[TD]31/12/16[/TD]
[TD]CLEAN INVOICE[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]COMMENTS[/TD]
[TD]Count of PO NO[/TD]
[TD]Sum of US$[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]CLEAN INVOICE[/TD]
[TD]1[/TD]
[TD]160[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]NO INVOICE[/TD]
[TD]3[/TD]
[TD]2320[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]WRONG ADDRESS[/TD]
[TD]2[/TD]
[TD]5500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]WRONG AMOUNT[/TD]
[TD]1[/TD]
[TD]700[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]Grand Total[/TD]
[TD][/TD]
[TD]8680[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][/tr][tr][td]

1​

[/td][td]PO NO[/td][td]Vendor No.[/td][td]Vendor Name[/td][td]Account[/td][td]US$ [/td][td]Posting Date[/td][td]COMMENTS[/td][td] COMMENTS[/td][td] Count of PO NO[/td][td] Sum of US$[/td][/tr]
[tr][td]

2​

[/td][td]

45280​

[/td][td]

12031​

[/td][td]ABC COMPANY[/td][td]

11002002​

[/td][td]

200​

[/td][td]

12/31/2016​

[/td][td]NO INVOICE[/td][td] CLEAN INVOICE[/td][td]

1​

[/td][td]

160​

[/td][/tr]
[tr][td]

3​

[/td][td]

45280​

[/td][td]

12031​

[/td][td]ABC COMPANY[/td][td]

11002003​

[/td][td]

350​

[/td][td]

12/31/2016​

[/td][td]NO INVOICE[/td][td] NO INVOICE[/td][td]

3​

[/td][td]

2320​

[/td][/tr]
[tr][td]

4​

[/td][td]

45280​

[/td][td]

12031​

[/td][td]ABC COMPANY[/td][td]

11002004​

[/td][td]

300​

[/td][td]

12/31/2016​

[/td][td]NO INVOICE[/td][td] WRONG ADDRESS[/td][td]

2​

[/td][td]

5500​

[/td][/tr]
[tr][td]

5​

[/td][td]

45280​

[/td][td]

12031​

[/td][td]ABC COMPANY[/td][td]

11002005​

[/td][td]

270​

[/td][td]

12/31/2016​

[/td][td]NO INVOICE[/td][td] WRONG AMOUNT[/td][td]

1​

[/td][td]

700​

[/td][/tr]
[tr][td]

6​

[/td][td]

45230​

[/td][td]

12008​

[/td][td]XYZ COMPANY[/td][td]

11002090​

[/td][td]

2,500.00​

[/td][td]

12/31/2015​

[/td][td]WRONG ADDRESS[/td][td] [/td][td]

​

[/td][td]

​

[/td][/tr]
[tr][td]

7​

[/td][td]

45233​

[/td][td]

12008​

[/td][td]XYZ COMPANY[/td][td]

11002090​

[/td][td]

3,000.00​

[/td][td]

12/31/2015​

[/td][td]WRONG ADDRESS[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]

45263​

[/td][td]

12031​

[/td][td]GOOD LLC[/td][td]

11002090​

[/td][td]

500​

[/td][td]

12/31/2015​

[/td][td]NO INVOICE[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]

45275​

[/td][td]

11042​

[/td][td]LINE COMPANY[/td][td]

11002090​

[/td][td]

300​

[/td][td]

12/31/2015​

[/td][td]WRONG AMOUNT[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td]

45275​

[/td][td]

11042​

[/td][td]LINE COMPANY[/td][td]

11002091​

[/td][td]

400​

[/td][td]

12/31/2015​

[/td][td]WRONG AMOUNT[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td]

45435​

[/td][td]

11288​

[/td][td]JK LLC[/td][td]

11002090​

[/td][td]

700​

[/td][td]

12/31/2015​

[/td][td]NO INVOICE[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td]

45480​

[/td][td]

10038​

[/td][td]LOGIC COMPANY[/td][td]

11002090​

[/td][td]

80​

[/td][td]

12/31/2016​

[/td][td]CLEAN INVOICE[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

13​

[/td][td]

45480​

[/td][td]

10038​

[/td][td]LOGIC COMPANY[/td][td]

11002080​

[/td][td]

50​

[/td][td]

12/31/2016​

[/td][td]CLEAN INVOICE[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

14​

[/td][td]

45480​

[/td][td]

10038​

[/td][td]LOGIC COMPANY[/td][td]

11002090​

[/td][td]

30​

[/td][td]

12/31/2016​

[/td][td]CLEAN INVOICE[/td][td][/td][td][/td][td][/td][/tr]
[/table]

In K2 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($G$2:$G$14=$J2,$A$2:$A$14),$A$2:$A$14),1))

In L2 just enter and copy down:

=SUMIFS($E$2:$E$14,$G$2:$G$14,$J2)

This set up should be a tad faster.

 

[Abuahmed]

thanks