|
Sumproduct on filtered cells
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 |
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 he 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. Ndel40 Wrote: 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. [...] -- 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 |
Now I understand!
Thanks! "Aladin Akyurek" wrote: 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 he 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. Ndel40 Wrote: 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. [...] -- 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,
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" wrote: Now I understand! Thanks! "Aladin Akyurek" wrote: 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 he 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. Ndel40 Wrote: 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. [...] -- 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 |
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:A2 0)-ROW(A2),,1)))*(ROW(A2:A20) =TRANSPOSE(ROW(A2:A20))),ROW(A2:A20)^0)=1))) Regards, Daniel M. |
That worked great!
Thanks, Nick "Daniel.M" wrote: 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:A2 0)-ROW(A2),,1)))*(ROW(A2:A20) =TRANSPOSE(ROW(A2:A20))),ROW(A2:A20)^0)=1))) Regards, Daniel M. |
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. "Ndel40" wrote in message ... That worked great! Thanks, Nick |
=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. |
A credit-deserving contribution. Daniel.M Wrote: =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 ------------------------------------------------------------------------ Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=273885 |
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 ))))) -- Regards Frank Kabel Frankfurt, Germany Daniel.M wrote: =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. |
Thanks Aladin :-)
|
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. |
This version worked great and is much faster than the other formula!
Thanks, Nick "Daniel.M" wrote: 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. "Ndel40" wrote in message ... That worked great! Thanks, Nick |
| All times are GMT +1. The time now is 07:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com