ChetanPuri
Board Regular
- Joined
- Sep 5, 2018
- Messages
- 70
- Office Version
- 365
- Platform
- Windows
Good Morning Excel Gurus,
I have question regarding my attached worksheet the First worksheet I named it as Sheet 1 (2) and another tab is called EXCELWORKSHEET. On the First worksheet, In column D I am trying to build a 3 way sumifs, to sum the amounts from source worksheet "EXCELWORKSHEET" , based on Dates COLUMN A 6, Account Description Column D 6 and S/Code E6 onwards. On the first worksheet, I have Account No. starting from A2, Location (S/Code) Column B2 and dates D1 onwards. The issue I get my sumifs formula works fine If I just refer to Dates for EOM, but as soon as I add any other 2 Column A & B, it shows "#Value Error". Any help regarding this is highly appreciated.
Many thanks,
Regards,
Chetan
I have question regarding my attached worksheet the First worksheet I named it as Sheet 1 (2) and another tab is called EXCELWORKSHEET. On the First worksheet, In column D I am trying to build a 3 way sumifs, to sum the amounts from source worksheet "EXCELWORKSHEET" , based on Dates COLUMN A 6, Account Description Column D 6 and S/Code E6 onwards. On the first worksheet, I have Account No. starting from A2, Location (S/Code) Column B2 and dates D1 onwards. The issue I get my sumifs formula works fine If I just refer to Dates for EOM, but as soon as I add any other 2 Column A & B, it shows "#Value Error". Any help regarding this is highly appreciated.
Many thanks,
Regards,
Chetan
Book2 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | Account No. | Location | Account description | Jul-2019 | Aug-2019 | Sep-2019 | Oct-2019 | Nov-2019 | ||
2 | 20.23.100.620 | GIR155-SIL | Health & Safety | #VALUE! | ||||||
3 | 20.23.100.620 | JOR005-SIL | Health & Safety | #VALUE! | ||||||
4 | 20.23.100.620 | KUR024-SIL | Health & Safety | #VALUE! | ||||||
5 | 20.23.100.620 | STU009-SIL | Health & Safety | #VALUE! | ||||||
6 | 20.23.100.620 | VIC020-SIL | Health & Safety | #VALUE! | ||||||
Sheet1 (2) |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E1:H1 | E1 | =DATE(YEAR(D1),MONTH(D1)+2,0) |
D2:D615 | D2 | =SUMIFS(ExcelWorksheet!$F:$F,ExcelWorksheet!$B$6:$B$78719,'Sheet1 (2)'!A2:A615,ExcelWorksheet!$A:$A,">="&DATE(YEAR('Sheet1 (2)'!D$1),MONTH('Sheet1 (2)'!D$1),1),ExcelWorksheet!$A:$A,"<="&EOMONTH('Sheet1 (2)'!D$1,0)) |
Dynamic array formulas. |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
R1:AD1,D1:P1 | Expression | =$D$2>#REF! | text | NO |
Book2 | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | Process Date: | ||||||||
2 | From Period Start: | ||||||||
3 | |||||||||
4 | To Period End: | 1 | |||||||
5 | Date | Account No. | Cost Code | Account Description | S/Code | Debit | Credit | ||
6 | 7-Jul-19 | 10.10.100.604 | 604 | Alarm & Security | PAL010 | 745.00 | |||
7 | 11-Jul-19 | 10.10.100.604 | 604 | Alarm & Security | PAL010 | 727.04 | |||
8 | 25-Jul-19 | 10.10.100.604 | 604 | Alarm & Security | PAL010 | 160.00 | |||
9 | 3-Jul-19 | 10.10.100.604 | 604 | Alarm & Security | WAG010 | 65.46 | |||
10 | 30-Jul-19 | 10.10.100.604 | 604 | Alarm & Security | WAG010 | 67.37 | |||
11 | 7-Aug-19 | 10.10.100.604 | 604 | Alarm & Security | WAG010 | 160.00 | |||
12 | 23-Sep-19 | 10.10.100.604 | 604 | Alarm & Security | KAT010 | 90.00 | |||
13 | 3-Oct-19 | 10.10.100.604 | 604 | Alarm & Security | PAL010 | 200.00 | |||
14 | 8-Nov-19 | 10.10.100.604 | 604 | Alarm & Security | WAG010 | 13.35 | |||
15 | 31-Jul-19 | 10.10.100.609 | 609 | Cleaning & Pest Control | 72.44 | ||||
ExcelWorksheet |