usnapoleon
Board Regular
- Joined
- May 22, 2014
- Messages
- 79
- Office Version
- 365
- Platform
- Windows
Thank you for your help. I thought I had this figured out and I was very excited, but then I saw that the number returned by my formula did not sum up multiple instances of a match.
I have 2 tabs on a spreadsheet. 1 is raw data, and the other extrapolates specific information from it. I will first show the raw data, then the cells I'm formulating in
Raw data, where we want $$ totals from column M, after running matches with columns H, W, and Q:
Here is the spreadsheet I'm trying to feed into
The formula is in K5, but here it is raw: =IFERROR(INDEX(Drillbit!M:M,MATCH(1,(Drillbit!H:H='1'!E1)*(Drillbit!W:W='1'!R5)*(Drillbit!Q:Q='1'!K4),0)),0)
That Raw Data tab only has 1 day in there so far, just for testing, 1/3/2024, but it is meant to have a whole month in there. For that reason, we first match the date, then we match the outlet's code, then we match the card type. As you can see in the raw data, there are 2 inputted numbers for MASTERCARD, from department REST 0250. Whether it has 2, or 5, I want it to combine them.
If there is a better formula, I am happy to learn it. I really thought I nailed it... kicking myself. Thank you in advance!
I have 2 tabs on a spreadsheet. 1 is raw data, and the other extrapolates specific information from it. I will first show the raw data, then the cells I'm formulating in
Raw data, where we want $$ totals from column M, after running matches with columns H, W, and Q:
| 3.18 NEW spreadsheet WIP.xlsx | |||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | |||
| 7 | Ledger | BU | OpUnit | Dept | Account | Account Descr | Journal ID | Journal Date | Trans Cur | Trans Amt | Base Cur | Base Amt | USD Amt | Year | Pd | Posted Date | Line Descr | EID | Source Code | Source Descr | Journal Line | Journal Descr | Journal Line Ref | ||
| 8 | ACTUALS | E1311 | 34K93 | 0410 | 134111 | Credit Card Receivables | F151080476 | 1/3/2024 | USD | 4.50 | USD | 4.50 | 4.50 | 2024 | 1 | 01/04/2024 | DISCOVER | MAESTRO | F15 | FDR Squirrel | 327 | MP 0282 | |||
| 9 | ACTUALS | E1311 | 34K93 | 0281 | 601101 | Hospitality | F151080476 | 1/3/2024 | USD | 24.24 | USD | 24.24 | 24.24 | 2024 | 1 | 01/04/2024 | HOSP 0281 | MAESTRO | F15 | FDR Squirrel | 328 | PC 0281 | |||
| 10 | ACTUALS | E1311 | 34K93 | 0410 | 134111 | Credit Card Receivables | F151080476 | 1/3/2024 | USD | 95.57 | USD | 95.57 | 95.57 | 2024 | 1 | 01/04/2024 | MASTERCARD | MAESTRO | F15 | FDR Squirrel | 329 | TC 0131 | |||
| 11 | ACTUALS | E1311 | 34K93 | 0410 | 134111 | Credit Card Receivables | F151080476 | 1/3/2024 | USD | 47.41 | USD | 47.41 | 47.41 | 2024 | 1 | 01/04/2024 | MASTERCARD | MAESTRO | F15 | FDR Squirrel | 330 | REST 0250 | |||
| 12 | ACTUALS | E1311 | 34K93 | 0410 | 134111 | Credit Card Receivables | F151080476 | 1/3/2024 | USD | 19.40 | USD | 19.40 | 19.40 | 2024 | 1 | 01/04/2024 | MASTERCARD | MAESTRO | F15 | FDR Squirrel | 331 | REST 0250 | |||
Drillbit | |||||||||||||||||||||||||
Here is the spreadsheet I'm trying to feed into
| 3.18 NEW spreadsheet WIP.xlsx | |||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | |||
| 1 | Enter Run Date as MM-DD-YYYY and then Refresh All from Data tab => | 1/3/2024 | ◄ Auto, don’t touch | A u d i t o r : | a balance check between squirrel payments and revenue??? Input one | ||||||||||||||||||
| 2 | |||||||||||||||||||||||
| 3 | JC Pro Shop Revenue (G1) | Theater Breakout Worksheet (TH) | Auditors Daily Credit Card Report | Department Identification Data | |||||||||||||||||||
| 4 | Report Total | Adjustments | Report Total | OUTLET from POS systems | MASTERCARD | VISA | AMEX | DISCOVER | Totals | ||||||||||||||
| 5 | Member Account | - | The Addams Family | - | Canyon Grille | 47.41 | - | REST 0250 | |||||||||||||||
| 6 | Cash | - | Welkome Home | - | Canyon Grille Bar | - | CG Bar was removed as an outlet | ||||||||||||||||
| 7 | Mastercard | - | - | Marketplace | - | MP 0282 | |||||||||||||||||
| 8 | Visa | Wonderettes | - | Pizza Hut | - | PC 0281 | |||||||||||||||||
| 9 | American Express | - | Spamalot | - | Boulder Springs | - | PB 0220 | ||||||||||||||||
| 10 | Discover | - | Elf | - | Mountain Springs | - | PB 0280 | ||||||||||||||||
| 11 | Personal Check | Bodyguard | - | The Shop | - | GS 0400 | |||||||||||||||||
| 12 | Room Charge | - | Theater Concessions | - | TC 0131 | ||||||||||||||||||
| 13 | Rain Check (JC) | - | Beatles - Hard Days Night | - | Theater | - | |||||||||||||||||
1 | |||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| K5 | K5 | =IFERROR(INDEX(Drillbit!M:M,MATCH(1,(Drillbit!H:H='1'!E1)*(Drillbit!W:W='1'!R5)*(Drillbit!Q:Q='1'!K4),0)),0) |
| D5,D12,D9:D10 | D5 | =SUM(B5:C5) |
| D6 | D6 | =SUM(B6:C6)+SUM(B11:C11) |
| D7 | D7 | =SUM(B7:C8) |
| H5:H11,H13 | H5 | =SUM(G5:G5) |
| D13 | D13 | =SUM(B13:C15) |
| O5:O13 | O5 | =L5+M5+N5 |
The formula is in K5, but here it is raw: =IFERROR(INDEX(Drillbit!M:M,MATCH(1,(Drillbit!H:H='1'!E1)*(Drillbit!W:W='1'!R5)*(Drillbit!Q:Q='1'!K4),0)),0)
That Raw Data tab only has 1 day in there so far, just for testing, 1/3/2024, but it is meant to have a whole month in there. For that reason, we first match the date, then we match the outlet's code, then we match the card type. As you can see in the raw data, there are 2 inputted numbers for MASTERCARD, from department REST 0250. Whether it has 2, or 5, I want it to combine them.
If there is a better formula, I am happy to learn it. I really thought I nailed it... kicking myself. Thank you in advance!
