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)

 

[aamirgee]

Two version are fine, but neither of them is 365 as shown in your profile.

, you don't have any solution in Excel 2019 or 2021??

 

[Peter_SSs]

you don't have any solution in Excel 2019 or 2021??

I might have.
You don't have agreement to update your profile?

 

[kvsrinivasamurthy]

Pl upload a sample file with few rows(15 to 20rows). I will check .

 

[aamirgee]

Pl upload a sample file with few rows(15 to 20rows). I will check .

SAMPLE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2	INV#	DATE	ITEM DESCRIPTION	QTY	RATE	TOTAL	VENDOR	P.METHOD			VENDOR	# INVOICES	CASH	TRANSFER
3	N-35289	03/06/2024	HP Cartridge 912(BLK)	1	155.00	155.00	Universal Computers	CASH			Universal Computers
4	N-35289	03/06/2024	HP Cartridge 912(CYN)	1	78.00	78.00	Universal Computers	CASH			MYTM Information Tech LLC
5	N-35289	03/06/2024	HP Cartridge 912(YLW)	1	78.00	78.00	Universal Computers	CASH			Modern Gates Co. Ltd.
6	N-35289	03/06/2024	HP Cartridge 912(MGN)	1	78.00	78.00	Universal Computers	CASH			Afzal Computers Jeddah
7	N-35289	03/06/2024	HP Cartridge 650(CLR)	1	50.31	50.31	Universal Computers	CASH			Comlab Information Technology
8	N-35289	03/06/2024	HP Cartridge 650(BLK)	1	57.21	57.21	Universal Computers	CASH			Aalam Bila Hadood
9	2.3E+07	20/05/2024	POSBYTZ Additional User for POS	3	450.00	1350.00	MYTM Information Tech LLC	TRANSFER
10	N-35841	23/06/2024	HP Cartridge 912(BLK)	1	154.97	154.97	Universal Computers	CASH
11	N-35841	23/06/2024	HP Cartridge 912(CYN)	1	78.19	78.19	Universal Computers	CASH
12	N-35991	30/06/2024	HDD CASE M.2 2 IN 1 MD08ME	1	69.00	69.00	Universal Computers	CASH
13	N-36131	07/03/2024	LAPTOP RAM DDR3 8GB	1	115.00	115.00	Universal Computers	CASH
14	N-36120	07/03/2024	LAPTOP HP Ci7-1355 16GB/512SSD/13th Gen	1	2650.00	2650.00	Universal Computers	CASH
15	2.3E+07	09/09/2024	POSBYTZ Billing Software 6 months	1	1725.00	1725.00	MYTM Information Tech LLC	TRANSFER
16	3515	29/07/2024	Microsoft Exchange Online (PLAN1)	1	63.25	63.25	Modern Gates Co. Ltd.	TRANSFER			Now I want first filter Vendors and copy to K3. 2nd Number of Invoces for each Vendor. 3rd Number of Invoces in Cash OR Transfer
17	3682	09/09/2024	Microsoft Exchange Online (PLAN1)	1	34.50	34.50	Modern Gates Co. Ltd.	TRANSFER
18	24-200-000028	26/09/2024	PRINTER REPAIRING	1	270.00	270.00	Afzal Computers Jeddah	CASH
19	24-200-000029	26/09/2024	EZI PRINT 150A TONER	1	90.85	90.85	Afzal Computers Jeddah	CASH
20	N-39493	30/09/2024	HP Cartridge 903(B)	1	185.00	185.00	Universal Computers	CASH
21	N-39493	30/09/2024	HP Cartridge 903(Y)	1	80.00	80.00	Universal Computers	CASH
22	N-39493	30/09/2024	HP Cartridge 903(C)	1	80.00	80.00	Universal Computers	CASH
23	N-39493	30/09/2024	HP Cartridge 903(M)	1	80.00	80.00	Universal Computers	CASH
24	N-39493	30/09/2024	HP Cartridge 912(BLK)	1	149.50	149.50	Universal Computers	CASH
25	N-39493	30/09/2024	HP Cartridge 912(CYN)	1	78.20	78.20	Universal Computers	CASH
26	N-39493	30/09/2024	HP Cartridge 912(YLW)	1	78.20	78.20	Universal Computers	CASH
27	N-39493	30/09/2024	HP Cartridge 912(MGN)	1	78.20	78.20	Universal Computers	CASH
28	N-39493	30/09/2024	PRINTCO TONER 17A BLK	2	40.00	80.00	Universal Computers	CASH
29	N-39493	30/09/2024	PRINTCO TONER 05A/80A	1	39.88	39.88	Universal Computers	CASH
30	S24-88	15/10/2024	Microsoft Exchange Online (PLAN1)	1	13724.10	13724.10	Comlab Information Technology	TRANSFER
31	3918	17/10/2024	HP Cartridge 650(CLR)	2	60.00	120.00	Aalam Bila Hadood	CASH
32	3918	17/10/2024	HP Cartridge 650(BLK)	1	65.00	65.00	Aalam Bila Hadood	CASH
Sample

Cell Formulas
Range	Formula
K3:K8	K3	=UNIQUE(G3:G32)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A15:H32	Expression	=$A15<>""	text	YES
A3:H14	Expression	=$A3<>""	text	NO

 

[Fluff]

@aamirgee can you please change your profile to show 2021 rather than 365 as has been requested.
Thanks

 

[kvsrinivasamurthy]

For the given data A2:H32 I have used below formula in K2. I got correct result. If not working show what youare getting. result sample given below in image.

=LET(ven,G3:G32,inv,A3:A32,met,H3:H32,x,UNIQUE(ven),zinv,BYROW(x,LAMBDA(ra,COUNTA(UNIQUE(FILTER(inv,ven=ra))))),zcash,BYROW(x,LAMBDA(ra,COUNTA(UNIQUE(FILTER(inv,(ven=ra)*(met="cash")))))),ztfr,BYROW(x,LAMBDA(ra,COUNTA(UNIQUE(FILTER(inv,(ven=ra)*(met="transfer")))))),hdr,{"Vendor","Invoice","Cash","Transfer"},VSTACK(hdr,HSTACK(x,zinv,zcash,ztfr)))

Result.

Vendor	Invoice	Cash	Transfer
Universal Computers	6	6	1
MYTM Information Tech LLC	1	1	1
Modern Gates Co. Ltd.	2	1	2
Afzal Computers Jeddah	2	2	1
Comlab Information Technology	1	1	1
Aalam Bila Hadood	1	1	1

 

[Fluff]

@kvsrinivasamurthy if you bothered to read the thread you would know that the OP is not using 365, but 2021 & so does not have some of those functions.

 

[kvsrinivasamurthy]

Assumed you are having HSTACK function.
Enter headings K1:N1 as

VENDOR

INV#

CASH

TRANSFER

In K2

=UNIQUE(G3:G32)

In L2 copied down

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

 

[Fluff]

Assumed you are having HSTACK function.

Unfortunately not, that's only in 365 & 2024

 

[kvsrinivasamurthy]

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