kumara_faith
Well-known Member
- Joined
- Aug 19, 2006
- Messages
- 922
- Office Version
- 365
Hi,
I have the following table:
1) Table 1 is the raw data
2) Table 2 is the code to identify the payment description. Example: In Table 1, the column for Policy shows that there is an abbreviation infron of the policy no. This identifies the payment type.
3 Table 3- I am trying to summarize based on the columns. For the name column, I tried using Unique but its not accurate because Javier has sales in both Seattle and Boston. In addition I am not sure how to populate the branch and the count of policies by month
4) Table 4- I am trying to summarize based on the columns. Payment type is derived from the codes in Table 2. This is the abbreviation used in the policies for Table 1. Example PRM0001 means Premium. I am not sure how to derive the count based on the data in Table 1.
5) Table 5 - I am trying to summarize based on the columns. I tried using Countifs but I am not sure how to specify the count by month based on the date in Table 1.
Appreciate any help.
I have the following table:
| Book1 | |||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | |||
| 2 | Table 1 | Table 3 | Table 4 | Table 5 | |||||||||||||||||||||||
| 3 | Name | ID No | Policy | Branch | Date | Name | Branch | ID Card | Jan | Feb | Mar | Apr | Payment Type | Jan | Feb | Mar | Apr | Branch | Jan | Feb | Mar | Apr | |||||
| 4 | Amy | 55487 | PRM55877 | Dallas | 1/1/2022 | Amy | 55487 | 0 | Premium | Dallas | |||||||||||||||||
| 5 | Bryan | 66678 | NB44785 | New York | 2/2/2022 | Bryan | 66678 | Loan | New York | ||||||||||||||||||
| 6 | John | 45487 | LOA55547 | Chicago | 3/2/2022 | John | 45487 | New Business | Chicago | ||||||||||||||||||
| 7 | Javier | 25568 | PRM5544 | Seattle | 4/4/2022 | Javier | 25568 | Seattle | |||||||||||||||||||
| 8 | Javier | 45464 | NB446558 | Boston | 1/1/2022 | Javier | Boston | ||||||||||||||||||||
| 9 | |||||||||||||||||||||||||||
| 10 | Table 2 | ||||||||||||||||||||||||||
| 11 | Code | Description | |||||||||||||||||||||||||
| 12 | PRM | Premium | |||||||||||||||||||||||||
| 13 | LOA | Loan | |||||||||||||||||||||||||
| 14 | NB | New Business | |||||||||||||||||||||||||
Sheet1 | |||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| K4 | K4 | =COUNTIFS(B4:B8,H4,F4:F8,K3) |
| V4:V8 | V4 | =UNIQUE(E4:E8) |
| J4:J7 | J4 | =XLOOKUP(H4,$B$4:$B$8,$C$4:$C$8) |
| Dynamic array formulas. | ||
1) Table 1 is the raw data
2) Table 2 is the code to identify the payment description. Example: In Table 1, the column for Policy shows that there is an abbreviation infron of the policy no. This identifies the payment type.
3 Table 3- I am trying to summarize based on the columns. For the name column, I tried using Unique but its not accurate because Javier has sales in both Seattle and Boston. In addition I am not sure how to populate the branch and the count of policies by month
4) Table 4- I am trying to summarize based on the columns. Payment type is derived from the codes in Table 2. This is the abbreviation used in the policies for Table 1. Example PRM0001 means Premium. I am not sure how to derive the count based on the data in Table 1.
5) Table 5 - I am trying to summarize based on the columns. I tried using Countifs but I am not sure how to specify the count by month based on the date in Table 1.
Appreciate any help.
