How to Get Number of Invoices each Vendor

aamirgee · Oct 23, 2024

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 · Oct 24, 2024

Peter_SSs said:
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 ..

I am surprised Column A missed. Please check now.



INV#	DATE	ITEM DESCRIPTION	QTY	RATE	TOTAL	VENDOR	P.METHOD	VENDOR	# INVOICES	CASH	TRANSFER
N-35289	03/06/2024	HP Cartridge 912(BLK)	1	155.00	155.00	Universal Computers	CASH
N-35289	03/06/2024	HP Cartridge 912(CYN)	1	78.00	78.00	Universal Computers	CASH
N-35289	03/06/2024	HP Cartridge 912(YLW)	1	78.00	78.00	Universal Computers	CASH
N-35289	03/06/2024	HP Cartridge 912(MGN)	1	78.00	78.00	Universal Computers	CASH
N-35289	03/06/2024	HP Cartridge 650(CLR)	1	50.31	50.31	Universal Computers	CASH
N-35289	03/06/2024	HP Cartridge 650(BLK)	1	57.21	57.21	Universal Computers	CASH
2.3E+07	20/05/2024	POSBYTZ Additional User for POS	3	450.00	1350.00	MYTM Information Tech LLC	TRANSFER
N-35841	23/06/2024	HP Cartridge 912(BLK)	1	154.97	154.97	Universal Computers	CASH
N-35841	23/06/2024	HP Cartridge 912(CYN)	1	78.19	78.19	Universal Computers	CASH
N-35991	30/06/2024	HDD CASE M.2 2 IN 1 MD08ME	1	69.00	69.00	Universal Computers	CASH
N-36131	07/03/2024	LAPTOP RAM DDR3 8GB	1	115.00	115.00	Universal Computers	CASH
N-36120	07/03/2024	LAPTOP HP Ci7-1355 16GB/512SSD/13th Gen	1	2650.00	2650.00	Universal Computers	CASH
2.3E+07	09/09/2024	POSBYTZ Billing Software 6 months	1	1725.00	1725.00	MYTM Information Tech LLC	TRANSFER
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
3682	09/09/2024	Microsoft Exchange Online (PLAN1)	1	34.50	34.50	Modern Gates Co. Ltd.	TRANSFER
24-200-000028	26/09/2024	PRINTER REPAIRING	1	270.00	270.00	Afzal Computers Jeddah	CASH
24-200-000029	26/09/2024	EZI PRINT 150A TONER	1	90.85	90.85	Afzal Computers Jeddah	CASH
N-39493	30/09/2024	HP Cartridge 903(B)	1	185.00	185.00	Universal Computers	CASH
N-39493	30/09/2024	HP Cartridge 903(Y)	1	80.00	80.00	Universal Computers	CASH
N-39493	30/09/2024	HP Cartridge 903(C)	1	80.00	80.00	Universal Computers	CASH
N-39493	30/09/2024	HP Cartridge 903(M)	1	80.00	80.00	Universal Computers	CASH
N-39493	30/09/2024	HP Cartridge 912(BLK)	1	149.50	149.50	Universal Computers	CASH
N-39493	30/09/2024	HP Cartridge 912(CYN)	1	78.20	78.20	Universal Computers	CASH
N-39493	30/09/2024	HP Cartridge 912(YLW)	1	78.20	78.20	Universal Computers	CASH
N-39493	30/09/2024	HP Cartridge 912(MGN)	1	78.20	78.20	Universal Computers	CASH
N-39493	30/09/2024	PRINTCO TONER 17A BLK	2	40.00	80.00	Universal Computers	CASH
N-39493	30/09/2024	PRINTCO TONER 05A/80A	1	39.88	39.88	Universal Computers	CASH
S24-88	15/10/2024	Microsoft Exchange Online (PLAN1)	1	13724.10	13724.10	Comlab Information Technology	TRANSFER
3918	17/10/2024	HP Cartridge 650(CLR)	2	60.00	120.00	Aalam Bila Hadood	CASH
3918	17/10/2024	HP Cartridge 650(BLK)	1	65.00	65.00	Aalam Bila Hadood	CASH

aamirgee · Oct 24, 2024

Sanjeev1976 said:
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.

sorry i missed the Invoice Column .



INV#	DATE	ITEM DESCRIPTION	QTY	RATE	TOTAL	VENDOR	P.METHOD	VENDOR	# INVOICES	CASH	TRANSFER
N-35289	03/06/2024	HP Cartridge 912(BLK)	1	155.00	155.00	Universal Computers	CASH
N-35289	03/06/2024	HP Cartridge 912(CYN)	1	78.00	78.00	Universal Computers	CASH
N-35289	03/06/2024	HP Cartridge 912(YLW)	1	78.00	78.00	Universal Computers	CASH
N-35289	03/06/2024	HP Cartridge 912(MGN)	1	78.00	78.00	Universal Computers	CASH
N-35289	03/06/2024	HP Cartridge 650(CLR)	1	50.31	50.31	Universal Computers	CASH
N-35289	03/06/2024	HP Cartridge 650(BLK)	1	57.21	57.21	Universal Computers	CASH
2.3E+07	20/05/2024	POSBYTZ Additional User for POS	3	450.00	1350.00	MYTM Information Tech LLC	TRANSFER
N-35841	23/06/2024	HP Cartridge 912(BLK)	1	154.97	154.97	Universal Computers	CASH
N-35841	23/06/2024	HP Cartridge 912(CYN)	1	78.19	78.19	Universal Computers	CASH
N-35991	30/06/2024	HDD CASE M.2 2 IN 1 MD08ME	1	69.00	69.00	Universal Computers	CASH
N-36131	07/03/2024	LAPTOP RAM DDR3 8GB	1	115.00	115.00	Universal Computers	CASH
N-36120	07/03/2024	LAPTOP HP Ci7-1355 16GB/512SSD/13th Gen	1	2650.00	2650.00	Universal Computers	CASH
2.3E+07	09/09/2024	POSBYTZ Billing Software 6 months	1	1725.00	1725.00	MYTM Information Tech LLC	TRANSFER
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
3682	09/09/2024	Microsoft Exchange Online (PLAN1)	1	34.50	34.50	Modern Gates Co. Ltd.	TRANSFER
24-200-000028	26/09/2024	PRINTER REPAIRING	1	270.00	270.00	Afzal Computers Jeddah	CASH
24-200-000029	26/09/2024	EZI PRINT 150A TONER	1	90.85	90.85	Afzal Computers Jeddah	CASH
N-39493	30/09/2024	HP Cartridge 903(B)	1	185.00	185.00	Universal Computers	CASH
N-39493	30/09/2024	HP Cartridge 903(Y)	1	80.00	80.00	Universal Computers	CASH
N-39493	30/09/2024	HP Cartridge 903(C)	1	80.00	80.00	Universal Computers	CASH
N-39493	30/09/2024	HP Cartridge 903(M)	1	80.00	80.00	Universal Computers	CASH
N-39493	30/09/2024	HP Cartridge 912(BLK)	1	149.50	149.50	Universal Computers	CASH
N-39493	30/09/2024	HP Cartridge 912(CYN)	1	78.20	78.20	Universal Computers	CASH
N-39493	30/09/2024	HP Cartridge 912(YLW)	1	78.20	78.20	Universal Computers	CASH
N-39493	30/09/2024	HP Cartridge 912(MGN)	1	78.20	78.20	Universal Computers	CASH
N-39493	30/09/2024	PRINTCO TONER 17A BLK	2	40.00	80.00	Universal Computers	CASH
N-39493	30/09/2024	PRINTCO TONER 05A/80A	1	39.88	39.88	Universal Computers	CASH
S24-88	15/10/2024	Microsoft Exchange Online (PLAN1)	1	13724.10	13724.10	Comlab Information Technology	TRANSFER
3918	17/10/2024	HP Cartridge 650(CLR)	2	60.00	120.00	Aalam Bila Hadood	CASH
3918	17/10/2024	HP Cartridge 650(BLK)	1	65.00	65.00	Aalam Bila Hadood	CASH

Peter_SSs · Oct 24, 2024

aamirgee said:
I am surprised Column A missed. Please check now.

Yes, got column A as well now.

But ..

Better to use Mini Sheet than Table Only as we can then see column letters, rows numbers, any formulas etc.
Still no answer to the question that I have now asked twice.

aamirgee · Oct 24, 2024

Peter_SSs said:
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 ..

yes , Universal computer 6 rows with same invoice # count as 1 Invoice.

Peter_SSs · Oct 24, 2024

aamirgee said:
yes , Universal computer 6 rows with same invoice # count as 1 Invoice.

Thanks.
See if this does what you want.

24 10 24.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

0

4

N-35289

Universal Computers

CASH

MYTM Information Tech LLC

1

0

1

5

N-35289

Universal Computers

CASH

Modern Gates Co. Ltd.

2

0

2

6

N-35289

Universal Computers

CASH

Afzal Computers Jeddah

2

0

7

N-35289

Universal Computers

CASH

Comlab Information Technology

1

0

1

8

N-35289

Universal Computers

CASH

Aalam Bila Hadood

1

0

9

2.30E+07

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

2.30E+07

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

Cell Formulas
Range	Formula
K3:K8	K3	=UNIQUE(G3:G32)
L3:L8	L3	=BYROW(K3#,LAMBDA(r,ROWS(UNIQUE(FILTER(A3:A32,G3:G32=r)))))
M3:N8	M3	=LET(c,CHOOSECOLS($A3:$H32,1,7),BYROW($K3#,LAMBDA(r,IFNA(ROWS(UNIQUE(FILTER(c,($G3:$G32=r)*($H3:$H32=M2),NA()))),0))))
Dynamic array formulas.

aamirgee · Oct 24, 2024

Peter_SSs said:
LET(c,CHOOSECOLS($A3:$H32,1,7),BYROW($K3#,LAMBDA(r,IFNA(ROWS(UNIQUE(FILTER(c,($G3:$G32=r)*($H3:$H32=M2),NA()))),0))))

When i put the formula in column L It gives the error

Peter_SSs · Oct 24, 2024

aamirgee said:
When i put the formula in column L It gives the error

Perhaps your language version of excel uses ; instead of , as a separator in formulas?

aamirgee · Oct 24, 2024

Peter_SSs said:
Perhaps your language version of excel uses ; instead of , as a separator in formulas?

MY LANGUAGE IS US-EN. Thans for quick reply. watitng ........

Peter_SSs · Oct 24, 2024

In a blank cell, which of these formulas gives a result of 3 for you?
=SUM(1,2)
=SUM(1;2)

aamirgee · Oct 24, 2024

Peter_SSs said:
In a blank cell, which of these formulas gives a result of 3 for you?
=SUM(1,2)
=SUM(1;2)

=SUM(1,2) this gives result 3

How to Get Number of Invoices each Vendor

aamirgee

New Member

aamirgee

New Member

Excel Facts

aamirgee

New Member

Peter_SSs

MrExcel MVP, Moderator

aamirgee

New Member

Peter_SSs

MrExcel MVP, Moderator

aamirgee

New Member

Attachments

Peter_SSs

MrExcel MVP, Moderator

aamirgee

New Member

Peter_SSs

MrExcel MVP, Moderator

aamirgee

New Member

Similar threads

Forum statistics

Share this page

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.

How to Get Number of Invoices each Vendor

New Member

New Member

Excel Facts

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

Attachments

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock