How to consider duplicate cell/number as one in VLOOKUP

[Abuahmed]

DATA

PO NO	Vendor No.	Vendor Name	Account	US$	Posting Date	COMMENTS
45280	12031	ABC COMPANY	11002002	200.00	31-12-2016	NO INVOICE
45280	12031	ABC COMPANY	11002003	350.00	31-12-2016	NO INVOICE
45280	12031	ABC COMPANY	11002004	300.00	31-12-2016	NO INVOICE
45280	12031	ABC COMPANY	11002005	270.00	31-12-2016	NO INVOICE
45230	12008	XYZ COMPANY	11002090	2,500.00	31-12-2015	WRONG ADDRESS
45233	12008	XYZ COMPANY	11002090	3,000.00	31-12-2015	WRONG ADDRESS
45263	12031	GOOD LLC	11002090	500.00	31-12-2015	NO INVOICE
45275	11042	LINE COMPANY	11002090	300.00	31-12-2015	WRONG AMOUNT
45275	11042	LINE COMPANY	11002091	400.00	31-12-2015	WRONG AMOUNT
45435	11288	JK LLC	11002090	700.00	31-12-2015	NO INVOICE
45480	10038	LOGIC COMPANY	11002090	80.00	31-12-2016	CLEAN INVOICE
45480	10038	LOGIC COMPANY	11002080	50.00	31-12-2016	CLEAN INVOICE
45480	10038	LOGIC COMPANY	11002090	30.00	31-12-2016	CLEAN INVOICE

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>

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

COMMENTS	Count of PO NO	Sum of US$
CLEAN INVOICE	3	160
NO INVOICE	6	2320
WRONG ADDRESS	2	5500
WRONG AMOUNT	2	700
Grand Total	13	8680

<tbody>
</tbody><colgroup><col><col><col></colgroup>

I do not want counting duplicate just consider as one. like below example:
how can I get the below result in the vlookup please.

COMMENTS	Count of PO NO	Sum of US$
CLEAN INVOICE	1	160
NO INVOICE	3	2320
WRONG ADDRESS	2	5500
WRONG AMOUNT	1	700
Grand Total	7	8680

<tbody>
</tbody><colgroup><col><col><col></colgroup>

 

[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)

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

<tbody>
</tbody>

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​	G​	J​	K​	L​
1​	PO NO	Vendor No.	Vendor Name	Account	US$	Posting Date	COMMENTS	COMMENTS	Count of PO NO	Sum of US$
2​	45280​	12031​	ABC COMPANY	11002002​	200​	12/31/2016​	NO INVOICE	CLEAN INVOICE	1​	160​
3​	45280​	12031​	ABC COMPANY	11002003​	350​	12/31/2016​	NO INVOICE	NO INVOICE	3​	2320​
4​	45280​	12031​	ABC COMPANY	11002004​	300​	12/31/2016​	NO INVOICE	WRONG ADDRESS	2​	5500​
5​	45280​	12031​	ABC COMPANY	11002005​	270​	12/31/2016​	NO INVOICE	WRONG AMOUNT	1​	700​
6​	45230​	12008​	XYZ COMPANY	11002090​	2,500.00​	12/31/2015​	WRONG ADDRESS		​	​
7​	45233​	12008​	XYZ COMPANY	11002090​	3,000.00​	12/31/2015​	WRONG ADDRESS
8​	45263​	12031​	GOOD LLC	11002090​	500​	12/31/2015​	NO INVOICE
9​	45275​	11042​	LINE COMPANY	11002090​	300​	12/31/2015​	WRONG AMOUNT
10​	45275​	11042​	LINE COMPANY	11002091​	400​	12/31/2015​	WRONG AMOUNT
11​	45435​	11288​	JK LLC	11002090​	700​	12/31/2015​	NO INVOICE
12​	45480​	10038​	LOGIC COMPANY	11002090​	80​	12/31/2016​	CLEAN INVOICE
13​	45480​	10038​	LOGIC COMPANY	11002080​	50​	12/31/2016​	CLEAN INVOICE
14​	45480​	10038​	LOGIC COMPANY	11002090​	30​	12/31/2016​	CLEAN INVOICE

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