Hi all, I am completely lost with my conditions!
For column J I need a total that would be calculated by summing up values in column D divided by values in column H, but only for those lines that are not blank in column J.
It feels like this should be a combination of (IF(NOT(ISBLANK(J5:J54)) and =SUMPRODUCT(1/D5:D54, H5:H54) but I cannot wrap my head around the way to do it.
I am also too far down this rabbit hole to change the logic of the rest of the spreadsheet.
Any suggestions would be very welcome!
For column J I need a total that would be calculated by summing up values in column D divided by values in column H, but only for those lines that are not blank in column J.
It feels like this should be a combination of (IF(NOT(ISBLANK(J5:J54)) and =SUMPRODUCT(1/D5:D54, H5:H54) but I cannot wrap my head around the way to do it.
I am also too far down this rabbit hole to change the logic of the rest of the spreadsheet.
Any suggestions would be very welcome!
| Mercenaries Ledger.xlsm | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| D | E | F | G | H | I | J | K | L | M | N | |||
| 4 | How much - Total | How many | Who Sold | # | Person 1 | Person 2 | Person 3 | Person 4 | Person 5 | ||||
| 5 | 3,000,000.00 ₽ | 1 | Person 1 | 5 | x | x | x | x | x | ||||
| 6 | 0 | 0 | |||||||||||
| 7 | 100,000.00 ₽ | 1 | Person 2 | 2 | x | x | |||||||
| 8 | 250,000.00 ₽ | 1 | Person 3 | 1 | x | ||||||||
| 9 | 0 | 0 | |||||||||||
| 10 | 0 | 0 | |||||||||||
| 11 | |||||||||||||
| 12 | |||||||||||||
| 13 | |||||||||||||
| 14 | |||||||||||||
| 54 | |||||||||||||
| 55 | |||||||||||||
| 56 | 3,350,000.00 ₽ | - 2,350,000.00 ₽ | 550,000.00 ₽ | 600,000.00 ₽ | 600,000.00 ₽ | 600,000.00 ₽ | |||||||
Shared Expenses | |||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D54,D5:D14 | D5 | =C5*E5 |
| H54:H55,H5:H14 | H5 | =IF(NOT(ISBLANK($B5)),COUNTA($J5:$AM5),"") |
| E56 | E56 | =SUMPRODUCT(C5:C54,E5:E54) |
| J56:N56 | J56 | =SUM(IF(NOT(ISBLANK(J5:J55)),(1/$H$5:$H$55)*$D$5:$D$55))-SUMIF($F$5:$F$55,J4,$D$5:$D$55) |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| 'Shared Expenses'!_FilterDatabase | ='Shared Expenses'!$J$4:$Q$4 | J56 |
| Header | ='Shared Expenses'!$J$4 | J56 |
| Participants | =OFFSET('Shared Expenses'!$J$4,,,,COUNTA('Shared Expenses'!$J$4:$AM$4)) | J56 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| F9:F18 | Expression | =AND(NOT(ISBLANK($B9)),ISBLANK($F9)) | text | NO |
| F9:F18 | Expression | =AND(ISERROR(MATCH(F9,$J$4:$AM$4,0)),NOT(ISBLANK(B9))) | text | NO |
| H9:H18 | Expression | =ISBLANK(H9) | text | NO |
| J4:AM4 | Expression | =AND(NOT(ISBLANK(I$4)),ISBLANK(J$4)) | text | NO |
| J5:AM55 | Expression | =AND(NOT(ISBLANK(J$4)),NOT(ISBLANK($B5)),$H5<1) | text | NO |
| J5:AM55 | Expression | =AND(NOT(ISBLANK(J$4)),ISBLANK($B5)) | text | NO |
| F5:F8,F43:F55 | Expression | =AND(NOT(ISBLANK($B5)),ISBLANK($F5)) | text | NO |
| J5:AM55 | Expression | =AND(NOT(ISBLANK(J$4)),NOT(ISBLANK($B5))) | text | NO |
| F5:F8,F43:F55 | Expression | =AND(ISERROR(MATCH(F5,$J$4:$AM$4,0)),NOT(ISBLANK(B5))) | text | NO |
| H45:H55,H5:H8 | Expression | =ISBLANK(H5) | text | NO |
| J56:AM56,J57 | Expression | =J56<0.009 | text | NO |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| F5:F55 | List | =Participants |
| J4:S4 | List | =Mercenaries!$A$2:$A$1048576 |
| J5:AM55 | List | =Marks |
