gtd526
Well-known Member
- Joined
- Jul 30, 2013
- Messages
- 696
- Office Version
- 2019
- Platform
- Windows
Hello,
Trying to list all invoices per CustID(K2) with each Description(E:E) on the invoice (can be multiple descriptions per invoice).
Below are the results I have so far, but its not listing correctly when I change to CustID.
The first example is how it should look when completed. When I change the CustID it changes incorrectly. See additional examples below.
See J:J for the summary, which is what I want to correct.
Thank you.
Correct:
Incorrect:
Trying to list all invoices per CustID(K2) with each Description(E:E) on the invoice (can be multiple descriptions per invoice).
Below are the results I have so far, but its not listing correctly when I change to CustID.
The first example is how it should look when completed. When I change the CustID it changes incorrectly. See additional examples below.
See J:J for the summary, which is what I want to correct.
Thank you.
Correct:
Create Invoice.xlsm | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | |||
1 | Invoice | Cust ID | Name | Address | Description | Quantity | Subtotal | Grand Total | List of Invoices by Cust ID | ||||||
2 | 1 | gredow | Greg Downs | 3090 N Course Dr #910 | Tile - Installation | 1 | $15,000.00 | $15,000.00 | Cust ID: | joebla | |||||
3 | 2 | joebla | Joe Black | 123 Main St | Door Hinge | 1 | $100.00 | $100.00 | Address: | 123 Main St | Correct | ||||
4 | 3 | gredow | Greg Downs | Door Insulation | 1 | $200.00 | $200.00 | ||||||||
5 | 4 | gredow | Greg Downs | Replace Tile | 1 | $5,000.00 | $5,000.00 | Count | Invoice(s) | Summary | Price | ||||
6 | 5 | gredow | Greg Downs | Ceiling Fan | 1 | $356.75 | $356.75 | 1 | 2 | Door Hinge | $100.00 | ||||
7 | 6 | joebla | Joe Black | Install Lighting | 1 | $250.00 | $250.00 | 3 | 6 | Install Lighting | $600.00 | ||||
8 | 6 | joebla | Joe Black | Cabinets | 2 | $100.00 | $200.00 | Cabinets | |||||||
9 | 6 | joebla | Joe Black | Bed2 | 1 | $150.00 | $150.00 | Bed2 | |||||||
10 | 7 | gredow | Greg Downs | Floor | 1 | $25.00 | $25.00 | 2 | 8 | Walls | $720.00 | ||||
11 | 7 | gredow | Greg Downs | Ceiling | 1 | $300.00 | $300.00 | Floor Rug | |||||||
12 | 8 | joebla | Joe Black | Walls | 2 | $120.00 | $240.00 | 2 | 9 | Patch Walls | $450.00 | ||||
13 | 8 | joebla | Joe Black | Floor Rug | 3 | $160.00 | $480.00 | Thin Set Grout | |||||||
14 | 9 | joebla | Joe Black | Patch Walls | 1 | $200.00 | $200.00 | 2 | 10 | Lights | $350.00 | ||||
15 | 9 | joebla | Joe Black | Thin Set Grout | 1 | $250.00 | $250.00 | Ceiling | |||||||
16 | 10 | joebla | Joe Black | Lights | 1 | $300.00 | $300.00 | 1 | 11 | help | $25.00 | ||||
17 | 10 | joebla | Joe Black | Ceiling | 2 | $50.00 | $100.00 | ||||||||
18 | 11 | joebla | Joe Black | help | 1 | $25.00 | $25.00 | ||||||||
19 | 12 | jasblo | Jas Blow | Kitchen | 1 | $65.00 | $65.00 | ||||||||
20 | 13 | jasblo | Jas Blow | Carpet | 2 | $100.00 | $200.00 | ||||||||
21 | 13 | jasblo | Jas Blow | Blinds | 1 | $130.00 | $130.00 | Total: | $2,245.00 | ||||||
Test |
Cell Formulas | ||
---|---|---|
Range | Formula | |
K3 | K3 | =IFERROR(INDEX(Details!D:D,MATCH(Details!K2,Details!B:B,0)),"") |
I6:I20 | I6 | =IF(ISNUMBER(J6),COUNTIFS($A$1:$A$99, $J6),"") |
J6:J20 | J6 | =IF(ISNUMBER(M6),INDEX($A:$A, MATCH(0, IF($K$2=$B:$B, COUNTIF($J$5:$J5, $A:$A), ""), 0)),"") |
K6:K20 | K6 | =IFERROR(IF(COUNTIF($A$1:$A$99, $J6)=1,INDEX($E$2:$E$99,AGGREGATE(15,6,(ROW($E$2:$E$99)-ROW($E$2)+1)/($J6=$A$2:$A$99)/(ISNA(MATCH($E$2:$E$99,K$5:K5,0))),1)),INDEX($E$2:$E$99,AGGREGATE(15,6,(ROW($E$2:$E$99)-ROW($E$2)+1)/(LOOKUP(2,1/($J$6:J6<>""),$J$6:J6)=$A$2:$A$99)/(ISNA(MATCH($E$2:$E$99,K$5:K5,0))),1))),"") |
A18:A20,A16,A14,A3:A7 | A3 | =A2+1 |
B2:B21 | B2 | =LOWER(LEFT(C2,3)&MID(C2,FIND(" ",C2)+1,3)) |
H2:H21 | H2 | =F2*G2 |
M6:M7 | M6 | =IFERROR(IF(COUNTIFS($A:$A, $J6,$A:$A,"<>")>=2,SUMIF(A:A,$J6,H:H),INDEX(G:G,MATCH(J6,A:A,0))),"") |
M11:M20,M8:M9 | M8 | =IFERROR(IF(COUNTIFS($A:$A, $J8,$A:$A,"<>")>=2,SUMIF(A:A,$J8,G:G),(INDEX(G:G,MATCH(J8,A:A,0)))),"") |
M10 | M10 | =IFERROR(IF(COUNTIFS($A:$A, $J10,$A:$A,"<>")>=2,SUMIF(A:A,$J10,H:H),INDEX(H:H,MATCH(J10,A:A,0))),"") |
M21 | M21 | =SUM(IF(ISERROR(M6:M20),"",M6:M20)) |
Press CTRL+SHIFT+ENTER to enter array formulas. |
Incorrect:
Create Invoice.xlsm | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | |||
1 | Invoice | Cust ID | Name | Address | Description | Quantity | Subtotal | Grand Total | List of Invoices by Cust ID | ||||||
2 | 1 | gredow | Greg Downs | 3090 N Course Dr #910 | Tile - Installation | 1 | $15,000.00 | $15,000.00 | Cust ID: | gredow | |||||
3 | 2 | joebla | Joe Black | 123 Main St | Door Hinge | 1 | $100.00 | $100.00 | Address: | 123 Main St | Incorrect | ||||
4 | 3 | gredow | Greg Downs | Door Insulation | 1 | $200.00 | $200.00 | ||||||||
5 | 4 | gredow | Greg Downs | Replace Tile | 1 | $5,000.00 | $5,000.00 | Count | Invoice(s) | Summary | Price | ||||
6 | 5 | gredow | Greg Downs | Ceiling Fan | 1 | $356.75 | $356.75 | 1 | 1 | Tile - Installation | $15,000.00 | ||||
7 | 6 | joebla | Joe Black | Install Lighting | 1 | $250.00 | $250.00 | 1 | 3 | Door Insulation | $200.00 | ||||
8 | 6 | joebla | Joe Black | Cabinets | 2 | $100.00 | $200.00 | ||||||||
9 | 6 | joebla | Joe Black | Bed2 | 1 | $150.00 | $150.00 | ||||||||
10 | 7 | gredow | Greg Downs | Floor | 1 | $25.00 | $25.00 | 1 | 4 | Replace Tile | $5,000.00 | ||||
11 | 7 | gredow | Greg Downs | Ceiling | 1 | $300.00 | $300.00 | ||||||||
12 | 8 | joebla | Joe Black | Walls | 2 | $120.00 | $240.00 | 1 | 5 | Ceiling Fan | $356.75 | ||||
13 | 8 | joebla | Joe Black | Floor Rug | 3 | $160.00 | $480.00 | ||||||||
14 | 9 | joebla | Joe Black | Patch Walls | 1 | $200.00 | $200.00 | 2 | 7 | Floor | $325.00 | ||||
15 | 9 | joebla | Joe Black | Thin Set Grout | 1 | $250.00 | $250.00 | Ceiling | |||||||
16 | 10 | joebla | Joe Black | Lights | 1 | $300.00 | $300.00 | ||||||||
17 | 10 | joebla | Joe Black | Ceiling | 2 | $50.00 | $100.00 | ||||||||
18 | 11 | joebla | Joe Black | help | 1 | $25.00 | $25.00 | ||||||||
19 | 12 | jasblo | Jas Blow | Kitchen | 1 | $65.00 | $65.00 | ||||||||
20 | 13 | jasblo | Jas Blow | Carpet | 2 | $100.00 | $200.00 | ||||||||
21 | 13 | jasblo | Jas Blow | Blinds | 1 | $130.00 | $130.00 | Total: | $20,881.75 | ||||||
Test |
Cell Formulas | ||
---|---|---|
Range | Formula | |
K3 | K3 | =IFERROR(INDEX(Details!D:D,MATCH(Details!K2,Details!B:B,0)),"") |
I6:I20 | I6 | =IF(ISNUMBER(J6),COUNTIFS($A$1:$A$99, $J6),"") |
J6:J20 | J6 | =IF(ISNUMBER(M6),INDEX($A:$A, MATCH(0, IF($K$2=$B:$B, COUNTIF($J$5:$J5, $A:$A), ""), 0)),"") |
K6:K20 | K6 | =IFERROR(IF(COUNTIF($A$1:$A$99, $J6)=1,INDEX($E$2:$E$99,AGGREGATE(15,6,(ROW($E$2:$E$99)-ROW($E$2)+1)/($J6=$A$2:$A$99)/(ISNA(MATCH($E$2:$E$99,K$5:K5,0))),1)),INDEX($E$2:$E$99,AGGREGATE(15,6,(ROW($E$2:$E$99)-ROW($E$2)+1)/(LOOKUP(2,1/($J$6:J6<>""),$J$6:J6)=$A$2:$A$99)/(ISNA(MATCH($E$2:$E$99,K$5:K5,0))),1))),"") |
A18:A20,A16,A14,A3:A7 | A3 | =A2+1 |
B2:B21 | B2 | =LOWER(LEFT(C2,3)&MID(C2,FIND(" ",C2)+1,3)) |
H2:H21 | H2 | =F2*G2 |
M6:M7 | M6 | =IFERROR(IF(COUNTIFS($A:$A, $J6,$A:$A,"<>")>=2,SUMIF(A:A,$J6,H:H),INDEX(G:G,MATCH(J6,A:A,0))),"") |
M11:M20,M8:M9 | M8 | =IFERROR(IF(COUNTIFS($A:$A, $J8,$A:$A,"<>")>=2,SUMIF(A:A,$J8,G:G),(INDEX(G:G,MATCH(J8,A:A,0)))),"") |
M10 | M10 | =IFERROR(IF(COUNTIFS($A:$A, $J10,$A:$A,"<>")>=2,SUMIF(A:A,$J10,H:H),INDEX(H:H,MATCH(J10,A:A,0))),"") |
M21 | M21 | =SUM(IF(ISERROR(M6:M20),"",M6:M20)) |
Press CTRL+SHIFT+ENTER to enter array formulas. |