How can I use the following SUMPRODUCT formula that counts only unique
records on filter list?

=SUMPRODUCT(($A$2:$A$20<"")/(COUNTIF($A$2:$A$20,$A$2:$A$20)+($A$2:$A$20="")))

The formula works fine on the list until I filter the data. I would like to
have the formula update based on the filtered data.

For example:

Unfiltered list:
Unfiltered result = 3
Data
1
1
1
2
2
3
3

Filtered list, which displays only 1 & 2:
Desired filtered result = 2
Data
1
1
1
2
2

Thanks,

Nick

=SUM(IF((A2:A20<"")*(SUBTOTAL(9,OFFSET(A2,ROW(A2: A20)-ROW(A2),0))),1/COUNTIF(A2:A20,A2:A20)))

...entered using CONTROL+SHIFT+ENTER.

Hop this helps!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=273885

Assuming that A2:A20 consists of numbers as your sample suggests...

=SUM(IF(FREQUENCY(SUBTOTAL(3,OFFSET(A2:A20,ROW(A2: A20)-MIN(ROW(A2:A20)),,1))*A2:A20,SUBTOTAL(3,OFFSET(A2: A20,ROW(A2:A20)-MIN(ROW(A2:A20)),,1))*A2:A20)0,1))-(SUBTOTAL(3,A2:A20)<COUNTA(A2:A20))

which must be confirmed with control+shift+enter instead of just with
enter.

Ndel40 Wrote:
How can I use the following SUMPRODUCT formula that counts only unique
records on filter list?

=SUMPRODUCT(($A$2:$A$20<"")/(COUNTIF($A$2:$A$20,$A$2:$A$20)+($A$2:$A$20="")))

The formula works fine on the list until I filter the data. I would
like to
have the formula update based on the filtered data.

For example:

Unfiltered list:
Unfiltered result = 3
Data
1
1
1
2
2
3
3

Filtered list, which displays only 1 & 2:
Desired filtered result = 2
Data
1
1
1
2
2

Thanks,

Nick


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=273885

Aladin Akyurek Wrote:
Assuming that A2:A20 consists of numbers as your sample suggests...

=SUM(IF(FREQUENCY(SUBTOTAL(3,OFFSET(A2:A20,ROW(A2: A20)-MIN(ROW(A2:A20)),,1))*A2:A20,SUBTOTAL(3,OFFSET(A2: A20,ROW(A2:A20)-MIN(ROW(A2:A20)),,1))*A2:A20)0,1))-(SUBTOTAL(3,A2:A20)<COUNTA(A2:A20))

which must be confirmed with control+shift+enter instead of just with
enter.

Is there an advantage in using this formula instead of the one I
offered?


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=273885

I used:

=SUM(IF((A2:A20<"")*(SUBTOTAL(9,OFFSET(A2,ROW(A2: A20)-ROW(A2),0))),1/COUNTIF(A2:A20,A2:A20)))

It works perfect!!!

Thanks!

"Domenic" wrote:


Aladin Akyurek Wrote:
Assuming that A2:A20 consists of numbers as your sample suggests...

=SUM(IF(FREQUENCY(SUBTOTAL(3,OFFSET(A2:A20,ROW(A2: A20)-MIN(ROW(A2:A20)),,1))*A2:A20,SUBTOTAL(3,OFFSET(A2: A20,ROW(A2:A20)-MIN(ROW(A2:A20)),,1))*A2:A20)0,1))-(SUBTOTAL(3,A2:A20)<COUNTA(A2:A20))

which must be confirmed with control+shift+enter instead of just with
enter.

Is there an advantage in using this formula instead of the one I
offered?


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=273885

Domenic Wrote:
Is there an advantage in using this formula instead of the one I
offered?

Domenic,

That formula as such won't work. Consider:

Data Code
1 x
1 x
2 x
2 y
3 y
3 y

Filter for Code = x. The result that you'll get will be: 1.5 instead of
2.


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=273885

Yep! It looks like I didn't test it fully. As far as your formula...I
think for now I'll go cower in some corner. :)

Thanks Aladin!

Aladin Akyurek Wrote:
Domenic,

That formula as such won't work. Consider:

Data Code
1 x
1 x
2 x
2 y
3 y
3 y

Filter for Code = x. The result that you'll get will be: 1.5 instead of
2.


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=273885

I updated my spreadsheet to use Aladin's formula. It works great... although
I'm not sure how the formula works. I always seek to understand and I'm
interested in an explanation if you could please oblige.

Thanks,

Nick


"Domenic" wrote:


Yep! It looks like I didn't test it fully. As far as your formula...I
think for now I'll go cower in some corner. :)

Thanks Aladin!

Aladin Akyurek Wrote:
Domenic,

That formula as such won't work. Consider:

Data Code
1 x
1 x
2 x
2 y
3 y
3 y

Filter for Code = x. The result that you'll get will be: 1.5 instead of
2.


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=273885