whats the formula that works on 1 sheet
sorry, but i'm not following exactly
why use an indirect()
what columns are you trying to SUM and what criteria
column Z has 1234 in cell Z2
on the other sheet
column H is hidden
or on the first image you have windows sheet master in H
what are you summing, based on what criteria ???
as i say really sorry i'm not following , other members may understand
Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.
A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.
MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.
XL2BB - Excel Range to BBCode
Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...www.mrexcel.com
You can also test to see if it works ok, in the "Test Here" forum.
Test Here
Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days
OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed
whats the formula that works on 1 sheet
sorry, but i'm not following exactly
why use an indirect()
what columns are you trying to SUM and what criteria
column Z has 1234 in cell Z2
on the other sheet
column H is hidden
or on the first image you have windows sheet master in H
what are you summing, based on what criteria ???
as i say really sorry i'm not following , other members may understand
Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.
A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.
MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.
XL2BB - Excel Range to BBCode
Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...
You can also test to see if it works ok, in the "Test Here" forum.
Test Here
Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days
OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed
| SUM IFS TEST.xlsm | |||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | X | Y | Z | AA | ||||||||||||||||||
| 1 | Cuts | -82.40 | ADDS | 49.90 | R | runs | adds | ||||||||||||||||||||||
| 2 | Station | PC | HOURS | RUN | RUN | window sheet master | Cuts | -23.9 | 3215 | =SUMIFS(INDIRECT( | |||||||||||||||||||
| 3 | B | 1 | 7.9 | 1609 | 1609 | 1 | Adds | 0 | 3211 | ||||||||||||||||||||
| 4 | B | 20 | 7.8 | 607 | 607 | 01s | 1234 | ||||||||||||||||||||||
| 5 | B | 19 | 9.2 | 607 | 3215 | 1s | |||||||||||||||||||||||
| 6 | B | 01s | 7.7 | 607 | 3211 | 19 | |||||||||||||||||||||||
| 7 | b | 1 | 8.4 | 607 | 1234 | 20 | |||||||||||||||||||||||
| 8 | B | 23 | 8.6 | 1609 | 0 | ||||||||||||||||||||||||
| 9 | F | 1 | 8.9 | 607 | |||||||||||||||||||||||||
| 10 | R | 1 | 8.5 | 3215 | |||||||||||||||||||||||||
| 11 | R | 1 | 7.2 | 3211 | |||||||||||||||||||||||||
| 12 | R | 1 | 8.2 | 1234 | |||||||||||||||||||||||||
Platform Hours | |||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B1 | B1 | =SUM(C:C)*-1 |
| G1 | G1 | =SUM(J3+O3+T3+Y3) |
| Y2 | Y2 | =SUMIF($A:$A,"R",$C:$C)*-1 |
| Y3 | Y3 | =SUM(AA2:AA38) |
| Z2:Z4 | Z2 | =_xlfn.UNIQUE(_xlfn._xlws.FILTER(D3:D20,A3:A20=X1)) |
| F2 | F2 | =D2 |
| F3:F8 | F3 | =_xlfn.UNIQUE(D3:D40) |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
| SUM IFS TEST.xlsm | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | G | I | J | K | L | M | |||||
| 1 | Date | Station | Employee Id | Employee Name | Pay Code | Att Code | Paid Hours | Cut | Alt | Borrowed Acct | Run | ||||
| 20 | 9/25/2022 | R | 7290 | 01 | 8.50 | cut | 3215 | ||||||||
| 21 | 9/25/2022 | R | 7347 | 01 | 7.20 | cut | 3211 | ||||||||
| 23 | 9/25/2022 | R | 7211 | 01 | 8.20 | cut | 1234 | ||||||||
| 24 | 9/25/2022 | R | 7267 | 01s | 4.00 | 1234 | |||||||||
| 197 | 9/25/2022 | R | 5268 | 02 | 7.70 | 3215 | |||||||||
| 198 | 9/25/2022 | R | 5596 | 99 | 8.60 | 14507 | |||||||||
| 199 | 9/25/2022 | R | 5474 | 16 | 7.70 | 1234 | |||||||||
| 200 | 9/25/2022 | R | 5351 | 27 | 8.10 | 14511 | |||||||||
| 201 | 9/25/2022 | R | 5536 | 01s | 8.10 | 3211 | |||||||||
| 202 | 9/25/2022 | R | 5523 | 19 | 7.70 | 3211 | |||||||||
| 203 | 9/25/2022 | R | 5174 | 20 | 7.70 | 3211 | |||||||||
| 204 | 9/25/2022 | R | 5420 | 01 | 7.70 | 14517 | |||||||||
| 205 | 9/25/2022 | R | 5525 | 01 | 7.70 | 14518 | |||||||||
Window Sheet Master | |||||||||||||||
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| E2:E9999 | Expression | =AND($E2 <> 1, $E2 <> "") | text | YES |
| L2:L9999 | Expression | =($L2 <> "") | text | NO |
| C2:D9999,I2:I9999 | Expression | =OR(AND($E2 <> 1, $C2 <> ""), ($G2 <> ""), ($J2 = "Cut"), ($K2 = "Alt")) | text | NO |
Thanks so very much for your patients. Let me try to explain a bit better. I need the sum of the RUN in column Z (platform hours) but only if the run has pay codes of (01, 01s, 1s, 19,20). True values of the runs can be found on the Window Sheet Master (RUN in column M, Pay code in column E, and Hours in Column I). Again I cant thank you enough for being so patient.
| Forum Q - ETAF.xlsx | ||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | X | Y | Z | AA | AB | ||||||||||||||||||
| 1 | Cuts | -82.4 | ADDS | 51.4 | R | runs | adds | |||||||||||||||||||||||
| 2 | Station | PC | HOURS | RUN | RUN | window sheet master | Cuts | -23.9 | 3215 | 8.5 | correct | |||||||||||||||||||
| 3 | B | 1 | 7.9 | 1609 | 1609 | 1 | Adds | 51.4 | 3211 | 30.7 | correct | |||||||||||||||||||
| 4 | B | 20 | 7.8 | 607 | 607 | 01s | 1234 | 12.2 | correct | |||||||||||||||||||||
| 5 | B | 19 | 9.2 | 607 | 3215 | 1s | ||||||||||||||||||||||||
| 6 | B | 01s | 7.7 | 607 | 3211 | 19 | ||||||||||||||||||||||||
| 7 | b | 1 | 8.4 | 607 | 1234 | 20 | ||||||||||||||||||||||||
| 8 | B | 23 | 8.6 | 1609 | 0 | |||||||||||||||||||||||||
| 9 | F | 1 | 8.9 | 607 | ||||||||||||||||||||||||||
| 10 | R | 1 | 8.5 | 3215 | ||||||||||||||||||||||||||
| 11 | R | 1 | 7.2 | 3211 | ||||||||||||||||||||||||||
| 12 | R | 1 | 8.2 | 1234 | ||||||||||||||||||||||||||
Platform Hours | ||||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B1 | B1 | =SUM(C:C)*-1 |
| G1 | G1 | =SUM(J3+O3+T3+Y3) |
| Z2:Z4 | Z2 | =UNIQUE(FILTER(D3:D20,A3:A20=X1)) |
| F2 | F2 | =D2 |
| F3:F8 | F3 | =UNIQUE(D3:D40) |
| Y2 | Y2 | =SUMIF($A:$A,"R",$C:$C)*-1 |
| Y3 | Y3 | =SUM(AA2:AA38) |
| AA2:AA4 | AA2 | =SUMPRODUCT(SUMIFS('Window Sheet Master'!I:I,'Window Sheet Master'!E:E,'Platform Hours'!$H$3:$H$7,'Window Sheet Master'!M:M,'Platform Hours'!Z2)) |
| Dynamic array formulas. | ||
| Forum Q - ETAF.xlsx | ||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | X | Y | Z | AA | AB | ||||||||||||||||||
| 1 | Cuts | -82.4 | ADDS | 51.4 | R | runs | adds | |||||||||||||||||||||||
| 2 | Station | PC | HOURS | RUN | RUN | window sheet master | Cuts | -23.9 | 3215 | 8.5 | correct | |||||||||||||||||||
| 3 | B | 1 | 7.9 | 1609 | 1609 | 1 | Adds | 51.4 | 3211 | 30.7 | correct | |||||||||||||||||||
| 4 | B | 20 | 7.8 | 607 | 607 | 01s | 1234 | 12.2 | correct | |||||||||||||||||||||
| 5 | B | 19 | 9.2 | 607 | 3215 | 1s | ||||||||||||||||||||||||
| 6 | B | 01s | 7.7 | 607 | 3211 | 19 | ||||||||||||||||||||||||
| 7 | b | 1 | 8.4 | 607 | 1234 | 20 | ||||||||||||||||||||||||
| 8 | B | 23 | 8.6 | 1609 | 0 | |||||||||||||||||||||||||
| 9 | F | 1 | 8.9 | 607 | ||||||||||||||||||||||||||
| 10 | R | 1 | 8.5 | 3215 | ||||||||||||||||||||||||||
| 11 | R | 1 | 7.2 | 3211 | ||||||||||||||||||||||||||
| 12 | R | 1 | 8.2 | 1234 | ||||||||||||||||||||||||||
Platform Hours | ||||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B1 | B1 | =SUM(C:C)*-1 |
| G1 | G1 | =SUM(J3+O3+T3+Y3) |
| Z2:Z4 | Z2 | =UNIQUE(FILTER(D3:D20,A3:A20=X1)) |
| F2 | F2 | =D2 |
| F3:F8 | F3 | =UNIQUE(D3:D40) |
| Y2 | Y2 | =SUMIF($A:$A,"R",$C:$C)*-1 |
| Y3 | Y3 | =SUM(AA2:AA38) |
| AA2:AA4 | AA2 | =SUMPRODUCT(SUMIFS('Window Sheet Master'!I:I,'Window Sheet Master'!E:E,'Platform Hours'!$H$3:$H$7,'Window Sheet Master'!M:M,'Platform Hours'!Z2)) |
| Dynamic array formulas. | ||
Thank you so muchwe are summing 2 criteria
PH - Column Z RUN code - Matches with WSM - Column M
so thats the numbers -
3215
3211
1234
AND also has the codes from PH - column H - H3:H7 - Match with the WMS column E
which is this list
1
01s
1s
19
20
But that does not match your Green Highlight
and then the column to sum is WSM - column I
result in PH - column AA2
then
=SUMPRODUCT(SUMIFS('Window Sheet Master'!I:I,'Window Sheet Master'!E:E,'Platform Hours'!$H$3:$H$7,'Window Sheet Master'!M:M,'Platform Hours'!Z2))
should work
I have checked and i'm getting correct result
i have put on a share - will only be in for a few days - simply because you seem to have values in lots of rows/columns with many hidden and was getting a bit lost
Dropbox - File Deleted - Simplify your life
www.dropbox.com
Forum Q - ETAF.xlsx
A B C D E F G H X Y Z AA AB 1 Cuts -82.4 ADDS 51.4 R runs adds 2 Station PC HOURS RUN RUN window sheet master Cuts -23.9 3215 8.5 correct 3 B 1 7.9 1609 1609 1 Adds 51.4 3211 30.7 correct 4 B 20 7.8 607 607 01s 1234 12.2 correct 5 B 19 9.2 607 3215 1s 6 B 01s 7.7 607 3211 19 7 b 1 8.4 607 1234 20 8 B 23 8.6 1609 0 9 F 1 8.9 607 10 R 1 8.5 3215 11 R 1 7.2 3211 12 R 1 8.2 1234
Cell Formulas Range Formula B1 B1 =SUM(C:C)*-1 G1 G1 =SUM(J3+O3+T3+Y3) Z2:Z4 Z2 =UNIQUE(FILTER(D3:D20,A3:A20=X1)) F2 F2 =D2 F3:F8 F3 =UNIQUE(D3:D40) Y2 Y2 =SUMIF($A:$A,"R",$C:$C)*-1 Y3 Y3 =SUM(AA2:AA38) AA2:AA4 AA2 =SUMPRODUCT(SUMIFS('Window Sheet Master'!I:I,'Window Sheet Master'!E:E,'Platform Hours'!$H$3:$H$7,'Window Sheet Master'!M:M,'Platform Hours'!Z2)) Dynamic array formulas.
column N , O, P - i have put the values manually where i think the conditions for runcode and for the list match and checked
Forum Q - ETAF.xlsx
A B C D E F G H X Y Z AA AB 1 Cuts -82.4 ADDS 51.4 R runs adds 2 Station PC HOURS RUN RUN window sheet master Cuts -23.9 3215 8.5 correct 3 B 1 7.9 1609 1609 1 Adds 51.4 3211 30.7 correct 4 B 20 7.8 607 607 01s 1234 12.2 correct 5 B 19 9.2 607 3215 1s 6 B 01s 7.7 607 3211 19 7 b 1 8.4 607 1234 20 8 B 23 8.6 1609 0 9 F 1 8.9 607 10 R 1 8.5 3215 11 R 1 7.2 3211 12 R 1 8.2 1234
Cell Formulas Range Formula B1 B1 =SUM(C:C)*-1 G1 G1 =SUM(J3+O3+T3+Y3) Z2:Z4 Z2 =UNIQUE(FILTER(D3:D20,A3:A20=X1)) F2 F2 =D2 F3:F8 F3 =UNIQUE(D3:D40) Y2 Y2 =SUMIF($A:$A,"R",$C:$C)*-1 Y3 Y3 =SUM(AA2:AA38) AA2:AA4 AA2 =SUMPRODUCT(SUMIFS('Window Sheet Master'!I:I,'Window Sheet Master'!E:E,'Platform Hours'!$H$3:$H$7,'Window Sheet Master'!M:M,'Platform Hours'!Z2)) Dynamic array formulas.
