Count Visible Negative Cells, but not duplicates???

[natheplas]

Hi All,

Really struggling with this one.

I've got a formula that counts only the visible filtered negative numbers, however, it gets more complex as I don't want it to count it if it's a duplicate in relation to a despatch number.

Please see example table below.

My current formula counts the filtered table minuses on column E . So it would count 32. However, this is wrong. I need the formula to look at the order number and/or despatch number and not count it if it's a duplicate. For example it should return the number 4.

I don't know where to start with this? Maybe you could count each visible minus number on column e, in relation to every unique visible dispatch number?

Please help.



Order No. Despatch no.
[TABLE="width: 362"]
<tbody>[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]124583[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-15[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]124583[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-15[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]124583[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-15[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]124583[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-15[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]124583[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-15[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]124583[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-15[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]124583[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-15[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]124583[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-15[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]124583[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-15[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]124583[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-15[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]124583[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-15[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]124583[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-15[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]124583[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-15[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]124583[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-15[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]124636[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-16[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]124636[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-16[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]125210[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-24[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]125210[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-24[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]125210[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-24[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]125210[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-24[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]125210[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-24[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]125210[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-24[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]125210[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-24[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]125210[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-24[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]125210[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-24[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]125210[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-24[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]125210[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-24[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]125210[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-24[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]125661[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-35[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]125661[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-35[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]125661[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-35[/TD]
[/TR]
[TR]
[TD]A/C[/TD]
[TD="align: right"]101631[/TD]
[TD="align: right"]125661[/TD]
[TD]Company Name[/TD]
[TD="align: right"]-35[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Try:

=SUM(SIGN(FREQUENCY(IF(SUBTOTAL(104,OFFSET(E2,ROW(E2:E50)-ROW(E2),0))<0,MATCH(B2:B50&"|"&C2:C50,B2:B50&"|"&C2:C50,0)),ROW(B2:B50)-ROW(B2)+1)))

confirmed with Control+Shift+Enter.

 

[natheplas]

Eric you legend! Thank you - Worked like a charm.

I've put another question up that no one has come back to me on? I know it's a bit cheeky, but would you mind having a look please?

Can't tell if people arent coming back to me because it cant be done, or if it's so obvious they think Im being a troll.

Thank you!

https://www.mrexcel.com/forum/excel-questions/1060182-cell-change-effects-table-filter.html

 

[Eric W]

Glad to help.