Totaling only filtered value on formula with multiple criteria

[goodomens]

I have the below spreadsheet, and I need it to automatically calculate totals when filtered, with multiple criteria on.

Basically: I am trying to get totals on column D for "NP", but only when column B is a unique value. I have a formula for that already:
=SUM(--(LEN(UNIQUE(FILTER(B2:B23,(D2:D23="NP"))))>0))

I just need it to only count the filtered values when I filter by date, rather than the whole thing. I have the sums doing that, and this is the formula I'm using for those:
=SUBTOTAL(109, O2:O23)

Any ideas/suggestions?

 

[SanjayGMusafir]

Try this in some blank cell -

To count dates where D2:D23="NP"

=COUNTA(FILTER(A2:A23,D2:D23="NP",0))

Check and tell if delivers the desired results or tell what change you want.

 

[goodomens]

Try this in some blank cell -

To count dates where D2:D23="NP"

=COUNTA(FILTER(A2:A23,D2:D23="NP",0))

Check and tell if delivers the desired results or tell what change you want.

This counts all NP's, not just the ones with unique values in column B.

 

[SanjayGMusafir]

This counts all NP's, not just the ones with unique values in column B.

Try this -

=COUNTA(UNIQUE(FILTER(B2:B23,D2:D23="NP",0)))

 

[goodomens]

Try this -

=COUNTA(UNIQUE(FILTER(BT
[QUOTE="SanjayGulatiMusafir, post: 5934123, member: 434972"]
Try this -

[CODE=xls]=COUNTA(UNIQUE(FILTER(B2:B23,D2:D23="NP",0)))

That does only count if there's B is a unique value - but if I filter, it's still counting the whole sheet, rather than just the visible cells.
[/QUOTE]

 

[SanjayGMusafir]

That does only count if there's B is a unique value - but if I filter, it's still counting the whole sheet, rather than just the visible cells.

That is how the formula is designed. It will filter only the values that are designed within the formula. To add any new filter you have to edit the formula accordingly.

The SUBTOTAL with 109 function, you originally quoted, is designed to exclude manually hidden rows.

 

[goodomens]

That is how the formula is designed. It will filter only the values that are designed within the formula. To add any new filter you have to edit the formula accordingly.

The SUBTOTAL with 109 function, you originally quoted, is designed to exclude manually hidden rows.

I have the subtotal with 109 on columns J-N. Is there a way then to do the subtotal 109 for column D with the other criteria?

I need column D to do two things:
- Count NP's when Column B is a unique value
- Exclude hidden rows

 

[SanjayGMusafir]

I have the subtotal with 109 on columns J-N. Is there a way then to do the subtotal 109 for column D with the other criteria?

I need column D to do two things:
- Count NP's when Column B is a unique value
- Exclude hidden rows

The underneath function will deliver results for all values (hidden or Visible)

=COUNTA(UNIQUE(FILTER(B2:B23,D2:D23="NP",0)))

The formula to count excluding hidden rows is SUBTOTAL with 102 function

When I try to wrap same (underneath) formula within SUBTOTAL that does not work

=UNIQUE(FILTER(B2:B23,D2:D23="NP",0))

Some kind of Excel limitation

You can try

=SUBTOTAL(102,A2:A23)

in totals row below and when you filter the above table manually it will give you the desired result. Try That.

 

[goodomens]

The underneath function will deliver results for all values (hidden or Visible)

=COUNTA(UNIQUE(FILTER(B2:B23,D2:D23="NP",0)))

The formula to count excluding hidden rows is SUBTOTAL with 102 function

When I try to wrap same (underneath) formula within SUBTOTAL that does not work

=UNIQUE(FILTER(B2:B23,D2:D23="NP",0))

Some kind of Excel limitation

You can try

=SUBTOTAL(102,A2:A23)

in totals row below and when you filter the above table manually it will give you the desired result. Try That.

I'm so sorry, maybe I'm just not totally understanding, but this doesn't seem to work. I greatly appreciate you trying though!

 

[Fluff]

I would use a helper column in P2 put this formula & fill down

=SUBTOTAL(103,B2)

Then you can use

=ROWS(UNIQUE(FILTER(B2:B23,(D2:D23="NP")*(P2:P23=1))))