Hello,
I have a formula that excludes certain text in cells, and counts everything else- but I want it in addition to counting the cells that exclude the text, sum up the costs in the next column. So far I have this which works in counting all the cells that DO NOT contain the texts:
=COUNTA(client name OE'!J2:J2418)-SUMPRODUCT(--('client name OE'!J2:J2418={"DECLINED","NOTOFFERED","INELIGIBLE","MAINTAIN",""""})).
So column A (Acc), counts all the text that does NOT contain "DECLINED","NOTOFFERED","INELIGIBLE","MAINTAIN","""", then I want it to sum up the costs in the next column. So for the example below, it should look at ACCEE2, ACCEE, ACCECH, then add the costs next column.
<tbody>
</tbody>
I have a formula that excludes certain text in cells, and counts everything else- but I want it in addition to counting the cells that exclude the text, sum up the costs in the next column. So far I have this which works in counting all the cells that DO NOT contain the texts:
=COUNTA(client name OE'!J2:J2418)-SUMPRODUCT(--('client name OE'!J2:J2418={"DECLINED","NOTOFFERED","INELIGIBLE","MAINTAIN",""""})).
So column A (Acc), counts all the text that does NOT contain "DECLINED","NOTOFFERED","INELIGIBLE","MAINTAIN","""", then I want it to sum up the costs in the next column. So for the example below, it should look at ACCEE2, ACCEE, ACCECH, then add the costs next column.
Acc | AccCost |
DECLINED | 0.00 |
MAINTAIN | 13.00 |
NOTOFFERED | 0.00 |
MAINTAIN | 50.00 |
ACCEE2 | 70.00 |
ACCEE | 25.00 |
ACCECH | 30.00 |
MAINTAIN | 50.00 |
DECLINED | 0.00 |
<tbody>
</tbody>