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)

 

[bobsan42]

pivot tables is what you need

 

[Peter_SSs]

Welcome to the MrExcel board!

It might help if we could copy your data for testing. MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

In your image, the first 6 rows under the heading are all for 'Universal Computers' and all have the same invoice number. Do you want that counted as 6 for Universal Computers because there are 6 rows or do you want that counted as 1 because they are all the same invoice number?

 

[kvsrinivasamurthy]

Formula in J1

=LET(ven,G2:G13,inv,A2:A13,met,H2:H13,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)))

 

[aamirgee]

View attachment 118427
Formula in J1

=LET(ven,G2:G13,inv,A2:A13,met,H2:H13,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)))

I have tried your code as it but it is not working. please if you have sample workbook then send me.(Thanks)

 

[aamirgee]

pivot tables is what you need

need only calculations

 

[Peter_SSs]

please if you have sample workbook then send me.

Instead of helpers here having to send you something, what about you send some samples as requested and answer the questions that have been asked?

 

[aamirgee]

Instead of helpers here having to send you something, what about you send some samples as requested and answer the questions that have been asked?

The following is my sample sheet.

SAMPLE.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
2	DATE	ITEM DESCRIPTION	QTY	RATE	TOTAL	VENDOR	P.METHOD			VENDOR	# INVOICES	CASH	TRANSFER
3	03/06/2024	HP Cartridge 912(BLK)	1	155.00	155.00	Universal Computers	CASH
4	03/06/2024	HP Cartridge 912(CYN)	1	78.00	78.00	Universal Computers	CASH
5	03/06/2024	HP Cartridge 912(YLW)	1	78.00	78.00	Universal Computers	CASH
6	03/06/2024	HP Cartridge 912(MGN)	1	78.00	78.00	Universal Computers	CASH
7	03/06/2024	HP Cartridge 650(CLR)	1	50.31	50.31	Universal Computers	CASH
8	03/06/2024	HP Cartridge 650(BLK)	1	57.21	57.21	Universal Computers	CASH
9	20/05/2024	POSBYTZ Additional User for POS	3	450.00	1350.00	MYTM Information Tech LLC	TRANSFER
10	23/06/2024	HP Cartridge 912(BLK)	1	154.97	154.97	Universal Computers	CASH
11	23/06/2024	HP Cartridge 912(CYN)	1	78.19	78.19	Universal Computers	CASH
12	30/06/2024	HDD CASE M.2 2 IN 1 MD08ME	1	69.00	69.00	Universal Computers	CASH
13	07/03/2024	LAPTOP RAM DDR3 8GB	1	115.00	115.00	Universal Computers	CASH
14	07/03/2024	LAPTOP HP Ci7-1355 16GB/512SSD/13th Gen	1	2650.00	2650.00	Universal Computers	CASH
15	09/09/2024	POSBYTZ Billing Software 6 months	1	1725.00	1725.00	MYTM Information Tech LLC	TRANSFER
16	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	09/09/2024	Microsoft Exchange Online (PLAN1)	1	34.50	34.50	Modern Gates Co. Ltd.	TRANSFER
18	26/09/2024	PRINTER REPAIRING	1	270.00	270.00	Afzal Computers Jeddah	CASH
19	26/09/2024	EZI PRINT 150A TONER	1	90.85	90.85	Afzal Computers Jeddah	CASH
20	30/09/2024	HP Cartridge 903(B)	1	185.00	185.00	Universal Computers	CASH
21	30/09/2024	HP Cartridge 903(Y)	1	80.00	80.00	Universal Computers	CASH
22	30/09/2024	HP Cartridge 903(C)	1	80.00	80.00	Universal Computers	CASH
23	30/09/2024	HP Cartridge 903(M)	1	80.00	80.00	Universal Computers	CASH
24	30/09/2024	HP Cartridge 912(BLK)	1	149.50	149.50	Universal Computers	CASH
25	30/09/2024	HP Cartridge 912(CYN)	1	78.20	78.20	Universal Computers	CASH
26	30/09/2024	HP Cartridge 912(YLW)	1	78.20	78.20	Universal Computers	CASH
27	30/09/2024	HP Cartridge 912(MGN)	1	78.20	78.20	Universal Computers	CASH
28	30/09/2024	PRINTCO TONER 17A BLK	2	40.00	80.00	Universal Computers	CASH
29	30/09/2024	PRINTCO TONER 05A/80A	1	39.88	39.88	Universal Computers	CASH
30	15/10/2024	Microsoft Exchange Online (PLAN1)	1	13724.10	13724.10	Comlab Information Technology	TRANSFER
31	17/10/2024	HP Cartridge 650(CLR)	2	60.00	120.00	Aalam Bila Hadood	CASH
32	17/10/2024	HP Cartridge 650(BLK)	1	65.00	65.00	Aalam Bila Hadood	CASH
Sample

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

 

[Sanjeev1976]

You can try :

Book3
	B	C	D	E	F	G	H	I	J	K	L	M	N
2	DATE	ITEM DESCRIPTION	QTY	RATE	TOTAL	VENDOR	P.METHOD			VENDOR	# INVOICES	CASH	TRANSFER
3	03/06/2024	HP Cartridge 912(BLK)	1	155	155	Universal Computers	CASH			Aalam Bila Hadood	2	2
4	03/06/2024	HP Cartridge 912(CYN)	1	78	78	Universal Computers	CASH			Afzal Computers Jeddah	2	2
5	03/06/2024	HP Cartridge 912(YLW)	1	78	78	Universal Computers	CASH			Comlab Information Technology	1		1
6	03/06/2024	HP Cartridge 912(MGN)	1	78	78	Universal Computers	CASH			Modern Gates Co. Ltd.	2		2
7	03/06/2024	HP Cartridge 650(CLR)	1	50.31	50.31	Universal Computers	CASH			MYTM Information Tech LLC	2		2
8	03/06/2024	HP Cartridge 650(BLK)	1	57.21	57.21	Universal Computers	CASH			Universal Computers	21	21
9	20/05/2024	POSBYTZ Additional User for POS	3	450	1350	MYTM Information Tech LLC	TRANSFER			TOTAL	30	25	5
10	23/06/2024	HP Cartridge 912(BLK)	1	154.97	154.97	Universal Computers	CASH
11	23/06/2024	HP Cartridge 912(CYN)	1	78.19	78.19	Universal Computers	CASH
12	30/06/2024	HDD CASE M.2 2 IN 1 MD08ME	1	69	69	Universal Computers	CASH
13	07/03/2024	LAPTOP RAM DDR3 8GB	1	115	115	Universal Computers	CASH
14	07/03/2024	LAPTOP HP Ci7-1355 16GB/512SSD/13th Gen	1	2650	2650	Universal Computers	CASH
15	09/09/2024	POSBYTZ Billing Software 6 months	1	1725	1725	MYTM Information Tech LLC	TRANSFER
16	29/07/2024	Microsoft Exchange Online (PLAN1)	1	63.25	63.25	Modern Gates Co. Ltd.	TRANSFER
17	09/09/2024	Microsoft Exchange Online (PLAN1)	1	34.5	34.5	Modern Gates Co. Ltd.	TRANSFER
18	26/09/2024	PRINTER REPAIRING	1	270	270	Afzal Computers Jeddah	CASH
19	26/09/2024	EZI PRINT 150A TONER	1	90.85	90.85	Afzal Computers Jeddah	CASH
20	30/09/2024	HP Cartridge 903(B)	1	185	185	Universal Computers	CASH
21	30/09/2024	HP Cartridge 903(Y)	1	80	80	Universal Computers	CASH
22	30/09/2024	HP Cartridge 903(C)	1	80	80	Universal Computers	CASH
23	30/09/2024	HP Cartridge 903(M)	1	80	80	Universal Computers	CASH
24	30/09/2024	HP Cartridge 912(BLK)	1	149.5	149.5	Universal Computers	CASH
25	30/09/2024	HP Cartridge 912(CYN)	1	78.2	78.2	Universal Computers	CASH
26	30/09/2024	HP Cartridge 912(YLW)	1	78.2	78.2	Universal Computers	CASH
27	30/09/2024	HP Cartridge 912(MGN)	1	78.2	78.2	Universal Computers	CASH
28	30/09/2024	PRINTCO TONER 17A BLK	2	40	80	Universal Computers	CASH
29	30/09/2024	PRINTCO TONER 05A/80A	1	39.88	39.88	Universal Computers	CASH
30	15/10/2024	Microsoft Exchange Online (PLAN1)	1	13724	13724	Comlab Information Technology	TRANSFER
31	17/10/2024	HP Cartridge 650(CLR)	2	60	120	Aalam Bila Hadood	CASH
32	17/10/2024	HP Cartridge 650(BLK)	1	65	65	Aalam Bila Hadood	CASH
Sheet1

Cell Formulas
Range	Formula
K3:N8	K3	=LET(vend_list,G3:G32,vendor,SORT(UNIQUE(vend_list)),Pay_Method,H3:H32,HSTACK(vendor,COUNTIF(vend_list,vendor),COUNTIFS(vend_list,vendor,Pay_Method,M2),COUNTIFS(vend_list,vendor,Pay_Method,N2)))
L9:N9	L9	=SUM(L3:L8)
Dynamic array formulas.

 

[Peter_SSs]

The following is my sample sheet.

Thanks for getting XL2BB going and providing some sample data. I'm wondering why it is missing column A. Isn't that an important column in relation to your question?

Also, unless your question has now been resolved, it might help if you addressed my earlier question ..

In your image, the first 6 rows under the heading are all for 'Universal Computers' and all have the same invoice number. Do you want that counted as 6 for Universal Computers because there are 6 rows or do you want that counted as 1 because they are all the same invoice number?