Forum Discussion
Microsoft Excel Formula Assistance
Hello, Here is my existing formula. This logic checks the Column K (rows through 2-14) for "Rej" =SUM(IF(FREQUENCY(IF($A$2:$A$14=A2,IF($D$2:$D$14="Final",IF($I$2:$I$14="C D w/ I",IF(ISERROR(S...
| Column A | Column D | Column E | Column I | Expected Value |
| 111 | Final | 100 | C D w/ I | 150 |
| 111 | Final | 100 | C D w/ I | 150 |
| 111 | Final | 50 | C D w/ I | 150 |
| 111 | Test | 1000 | Test | 150 |
| 222 | Final | 500 | Test | - |
| 333 | Final | 100 | C D w/ I | 100 |
| 333 | Final | 100 | C D w/ I | 100 |
| 444 | Final | 100 | C D w/ I | 250 |
| 444 | Final | 150 | C D w/ I | 250 |
It appears the first suggestion works, slightly modified to remove some of the IF statements (I included a second example in the workbook that I believe would also work, although longer) . You may have to hit Ctrl+Shift+Enter after copying/keying it into the formula bar instead of just enter (may not be necessary on the latest version of office 365).
=SUM(IF(FREQUENCY(IF(($A$2:$A$10=A2)*($D$2:$D$10="Final")*($I$2:$I$10="C D w/ I"),$E$2:$E$10),--$E$2:$E$10),$E$2:$E$10))
To get the "-" for zero values, I would suggest using a custom number format.
