Sumproduct on filtered cells

Guest · Oct 30, 2004

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

Domenic · Oct 30, 2004

=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

Aladin Akyurek · Oct 30, 2004

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 wit
enter.

Domenic · Oct 30, 2004

Aladin said:
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 wit
enter.

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

Guest · Oct 30, 2004

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!

Aladin Akyurek · Oct 30, 2004

Domenic said:
Is there an advantage in using this formula instead of the one
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 o
2

Domenic · Oct 30, 2004

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

Thanks Aladin!

Guest · Oct 31, 2004

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

Aladin Akyurek · Oct 31, 2004

Nick,

The formula combines two tracks...

1.

SUM(IF(FREQUENCY(NumRange,NumRange)>0,1))

where the FREQUENCY(NumRange,NumRange) bit determines frequencies fo
the distinct members of NumRange which also serve as an array of bins
The wrapping IF maps all non-zero frequencies to 1's which the oute
SUM totals. The end result is a count of distinct numbers in NumRange.

2.

SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),,1))

which is due to Longre is described here:

http://j-walk.com/ss/excel/eee/eee001.txt

It produces an array of 1's and 0's which stand for filtered an
unfiltered: 1 corresponds to a row that's visible, 0 to a row that'
invisble.

These two combined...

=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)

allows us to consider just the numbers that are in the visible set.

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 an
I'm
interested in an explanation if you could please oblige.
[...

Guest · Oct 31, 2004

Now I understand!

Thanks!

Aladin Akyurek said:
Nick,

The formula combines two tracks...

1.

SUM(IF(FREQUENCY(NumRange,NumRange)>0,1))

where the FREQUENCY(NumRange,NumRange) bit determines frequencies for
the distinct members of NumRange which also serve as an array of bins.
The wrapping IF maps all non-zero frequencies to 1's which the outer
SUM totals. The end result is a count of distinct numbers in NumRange.

2.

SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),,1))

which is due to Longre is described here:

http://j-walk.com/ss/excel/eee/eee001.txt

It produces an array of 1's and 0's which stand for filtered and
unfiltered: 1 corresponds to a row that's visible, 0 to a row that's
invisble.

These two combined...

=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))

allows us to consider just the numbers that are in the visible set.

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.
[...]

Click to expand...

Guest · Nov 1, 2004

Aladin,

The formula works well when counting numbers, but it returns #N/A when I try
to count text.

Can the formula be modified to count text?

Thanks,

Nick

Ndel40 said:
Now I understand!

Thanks!

Aladin Akyurek said:

Nick,

The formula combines two tracks...

1.

SUM(IF(FREQUENCY(NumRange,NumRange)>0,1))

where the FREQUENCY(NumRange,NumRange) bit determines frequencies for
the distinct members of NumRange which also serve as an array of bins.
The wrapping IF maps all non-zero frequencies to 1's which the outer
SUM totals. The end result is a count of distinct numbers in NumRange.

2.

SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),,1))

which is due to Longre is described here:

http://j-walk.com/ss/excel/eee/eee001.txt

It produces an array of 1's and 0's which stand for filtered and
unfiltered: 1 corresponds to a row that's visible, 0 to a row that's
invisble.

These two combined...

=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))

allows us to consider just the numbers that are in the visible set.

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.
[...]

Click to expand...

Click to expand...

Daniel.M · Nov 1, 2004

Hi,

The formula works well when counting numbers, but it returns #N/A when I try
to count text.

=SUM(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A20)-ROW(A2),,1)),--(MMULT((A2:A20=TRANSPOSE
(A2:A20))*TRANSPOSE(SUBTOTAL(3,OFFSET(A2,ROW(A2:A20)-ROW(A2),,1)))*(ROW(A2:A20)>
=TRANSPOSE(ROW(A2:A20))),ROW(A2:A20)^0)=1)))

Regards,

Daniel M.

Guest · Nov 1, 2004

That worked great!

Thanks,

Nick

Daniel.M said:
Hi,

=SUM(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A20)-ROW(A2),,1)),--(MMULT((A2:A20=TRANSPOSE
(A2:A20))*TRANSPOSE(SUBTOTAL(3,OFFSET(A2,ROW(A2:A20)-ROW(A2),,1)))*(ROW(A2:A20)>
=TRANSPOSE(ROW(A2:A20))),ROW(A2:A20)^0)=1)))

Regards,

Daniel M.

Daniel.M · Nov 1, 2004

You're welcome.

You may also try this one (also an ARRAY formula):

=SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A20)-ROW(A2),,1)),
MATCH(A2:A20,A2:A20,0)),ROW(INDIRECT("1:"&ROWS(A2:A20)))-1)>0))

Regards,

Daniel M.

Daniel.M · Nov 1, 2004

=SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A20)-ROW(A2),,1)),

MATCH(A2:A20,A2:A20,0)),ROW(INDIRECT("1:"&ROWS(A2:A20)))-1)>0))

The -1 is not required (it puts the 0 count at the resulting array's beginning
instead of at the end). So :

=SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A20)-ROW(A2),,1)),
MATCH(A2:A20,A2:A20,0)),ROW(INDIRECT("1:"&ROWS(A2:A20))))>0))

You can even 'count unique' on more criterias OUTSIDE of the filtered list (if
you see fit), as in:

=SUM(--(FREQUENCY(IF(SUBTOTAL(...)*(cond2)*(cond3),MATCH(...)),ROW(...))>0))

Regards,

Daniel M.

Aladin Akyurek · Nov 1, 2004

A credit-deserving contribution.

Daniel.M said:
The -1 is not required (it puts the 0 count at the resulting array'
beginning
instead of at the end). So :

=SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A20)-ROW(A2),,1)),
MATCH(A2:A20,A2:A20,0)),ROW(INDIRECT("1:"&ROWS(A2:A20))))>0))

You can even 'count unique' on more criterias OUTSIDE of the filtere
list (if
you see fit), as in:

=SUM(--(FREQUENCY(IF(SUBTOTAL(...)*(cond2)*(cond3),MATCH(...)),ROW(...))>0))

Regards,

Daniel M

Frank Kabel · Nov 1, 2004

Hi Daniel

just as an alternatve (very similar of course

)
=COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A20)-ROW(A2),,1)),MAT
CH(A2:A20,A2:A20,0)),ROW(INDIRECT("1:"&ROWS(A2:A20)))))

Daniel.M · Nov 2, 2004

Thanks Aladin

Daniel.M · Nov 2, 2004

Hi Frank,

just as an alternatve (very similar of course )
=COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A20)-ROW(A2),,1)),MAT
CH(A2:A20,A2:A20,0)),ROW(INDIRECT("1:"&ROWS(A2:A20)))))

Yep. Nick has indeed many choices

Regards,

Daniel M.

Guest · Jan 20, 2005

This version worked great and is much faster than the other formula!

Thanks,

Nick

SUMPRODUCT on filtered rows	6	Aug 5, 2009
Sumproduct Help!	2	Apr 8, 2013
Filtered table using Countif	6	Jun 23, 2008
Formula to count text string in column cells of filtered rows	3	Oct 2, 2009
Sumproduct across mutliple columns	3	Jan 25, 2010
Sumproduct in matrix means	3	Jun 6, 2012
Count Position of Filtered TEXT cells in a column	8	May 16, 2005
Sumproduct Subtotal #Value! error	2	Feb 20, 2009

Sumproduct on filtered cells

Guest

Domenic

Aladin Akyurek

Domenic

Guest

Aladin Akyurek

Domenic

Guest

Aladin Akyurek

Guest

Guest

Daniel.M

Guest

Daniel.M

Daniel.M

Aladin Akyurek

Frank Kabel

Daniel.M

Daniel.M

Guest

Ask a Question

Similar Threads