How to Get Number of Invoices each Vendor

[aamirgee]

Hello! I want to count total Number of Invoices for each Vendors & also want to count how many invoices have payment method Cash, Transfer and Others.

Help me. (This is my 1st Question)

 

[Peter_SSs]

@aamirgee
Thank you for now updating your profile.

For Excel 2021, try these

aamirgee.xlsm
	A						G	H	I	J	K	L	M	N
1
2	INV#						VENDOR	P.METHOD			VENDOR	# INVOICES	CASH	TRANSFER
3	N-35289						Universal Computers	CASH			Universal Computers	6	6	0
4	N-35289						Universal Computers	CASH			MYTM Information Tech LLC	2	0	2
5	N-35289						Universal Computers	CASH			Modern Gates Co. Ltd.	2	0	2
6	N-35289						Universal Computers	CASH			Afzal Computers Jeddah	2	2	0
7	N-35289						Universal Computers	CASH			Comlab Information Technology	1	0	1
8	N-35289						Universal Computers	CASH			Aalam Bila Hadood	1	1	0
9	23240052						MYTM Information Tech LLC	TRANSFER
10	N-35841						Universal Computers	CASH
11	N-35841						Universal Computers	CASH
12	N-35991						Universal Computers	CASH
13	N-36131						Universal Computers	CASH
14	N-36120						Universal Computers	CASH
15	23240064						MYTM Information Tech LLC	TRANSFER
16	3515						Modern Gates Co. Ltd.	TRANSFER
17	3682						Modern Gates Co. Ltd.	TRANSFER
18	24-200-000028						Afzal Computers Jeddah	CASH
19	24-200-000029						Afzal Computers Jeddah	CASH
20	N-39493						Universal Computers	CASH
21	N-39493						Universal Computers	CASH
22	N-39493						Universal Computers	CASH
23	N-39493						Universal Computers	CASH
24	N-39493						Universal Computers	CASH
25	N-39493						Universal Computers	CASH
26	N-39493						Universal Computers	CASH
27	N-39493						Universal Computers	CASH
28	N-39493						Universal Computers	CASH
29	N-39493						Universal Computers	CASH
30	S24-88						Comlab Information Technology	TRANSFER
31	3918						Aalam Bila Hadood	CASH
32	3918						Aalam Bila Hadood	CASH
Invoices (2)

Cell Formulas
Range	Formula
K3:K8	K3	=UNIQUE(G3:G32)
L3:L15	L3	=IF(K3="","",ROWS(UNIQUE(FILTER(A$3:A$32,G$3:G$32=K3))))
M3:N15	M3	=IF($K3="","",IFNA(ROWS(UNIQUE(FILTER($A$3:$A$32,($G$3:$G$32=$K3)*($H$3:$H$32=M$2),NA()))),0))
Dynamic array formulas.

 

[Peter_SSs]

Enter headings K1:N1 as

VENDOR

INV#

CASH

TRANSFER

In K2
Excel Formula:
=UNIQUE(G3:G32)
In L2 copied down

=IF($K2="","",LET(ven,G3:G32,inv,A3:A32,COUNTA(UNIQUE(FILTER(inv,ven=$K2)))))

In M2 copied down

=IF($K2="","",LET(ven,G3:G32,inv,A3:A32,met,H3:H32,COUNTA(UNIQUE(FILTER(inv,(ven=$K2)*(met="CASH"))))))

In N2 copied down

=IF($K2="","",LET(ven,G3:G32,inv,A3:A32,met,H3:H32,COUNTA(UNIQUE(FILTER(inv,(ven=$K2)*(met="TRANSFER"))))))

Since you had asked for sample data it wouldn't be a bad idea to check your formula results against that data to see if they give sensible results for that data.
For example, note your TRANSFER result for Aalam Bila Hadood and then check how many TRANSFER invoices there are in the data for that Vendor.
There are several other incorrect results with your formulas.

 

[aamirgee]

Enter headings K1:N1 as

VENDOR

INV#

CASH

TRANSFER

In K2
Excel Formula:
=UNIQUE(G3:G32)
In L2 copied down

=IF($K2="","",LET(ven,G3:G32,inv,A3:A32,COUNTA(UNIQUE(FILTER(inv,ven=$K2)))))

In M2 copied down

=IF($K2="","",LET(ven,G3:G32,inv,A3:A32,met,H3:H32,COUNTA(UNIQUE(FILTER(inv,(ven=$K2)*(met="CASH"))))))

In N2 copied down

=IF($K2="","",LET(ven,G3:G32,inv,A3:A32,met,H3:H32,COUNTA(UNIQUE(FILTER(inv,(ven=$K2)*(met="TRANSFER"))))))

NOT ACCURATE CALCULATIONS

 

[Peter_SSs]

NOT ACCURATE CALCULATIONS

What about the formulas in post 41?