SUBTOTAL and SUMPRODUCT

[Sanador]

In an ongoing attempt to derive some statistics from data, I am experimenting with some formulas using both SUMPRODUCT and the SUBTOTAL command. I've stolen the following formula from other posts which enables me to count the number of unique entries from a list of names:

=SUMPRODUCT((D12:D241<>"")/COUNTIF(D12:D241,D12:D241&""))

Now, there are a couple of manipulations I'd like to make. First, how can I successfully add the SUBTOTAL command to this formula so that when the list is filtered, the calculation uses only the data shown?

Next, how do I then take that formula and add other criteria to the equation. For example, with the formula above I have the number unique entries in column D. The next portion of my post would then update that number when the list is filtered. Finally, how could I then derive the number of these entries also containing data in column T? By adding...

,--($T$12:$T$241<>"")

or something similar to the SUMPRODUCT command?

Thanks as always.

 

[Aladin Akyurek]

In an ongoing attempt to derive some statistics from data, I am experimenting with some formulas using both SUMPRODUCT and the SUBTOTAL command. I've stolen the following formula from other posts which enables me to count the number of unique entries from a list of names:

=SUMPRODUCT((D12:D241<>"")/COUNTIF(D12:D241,D12:D241&""))

Now, there are a couple of manipulations I'd like to make. First, how can I successfully add the SUBTOTAL command to this formula so that when the list is filtered, the calculation uses only the data shown?

Next, how do I then take that formula and add other criteria to the equation. For example, with the formula above I have the number unique entries in column D. The next portion of my post would then update that number when the list is filtered. Finally, how could I then derive the number of these entries also containing data in column T? By adding...

,--($T$12:$T$241<>"")

or something similar to the SUMPRODUCT command?

Thanks as always.

If you download and install the latest version (3.9) of the morefunc.xll add-in:

=COUNTDIFF(IF(SUBTOTAL(3,OFFSET(D12,ROW(D12:D241)-ROW(D12),,1)),D12:D241,0),FALSE,0)

which must be confirmed with control+shift+enter.

You can easily add another conditional to the formula...

=COUNTDIFF(IF((SUBTOTAL(3,OFFSET(D12,ROW(D12:D241)-ROW(D12),,1)))*(T12:T241<>""),D12:D241,0),FALSE,0)

again followed by control+shift+enter.

 

[Sanador]

Can't seem to install the new morefunc and am getting an "#NAME?" error as a result. I am on a work computer so that may be the problem. Any ideas or other methods?

 

[Aladin Akyurek]

Can't seem to install the new morefunc and am getting an "#NAME?" error as a result. I am on a work computer so that may be the problem. Any ideas or other methods?

Try to install it in your home directory in a folder that you could name AddIns.

 

[Sanador]

Got it... thanks. However, the formula is only counting the items that meet the criteria listed in the first used row (12). For example, given the data set:

Column D E

Row 12 Joe Red
Row 13 Bob Blue
Row 14 Joe Green
Row 15 Jon Red
Row 16 Joe Red
Row 17 Bob Green

If filtered on column E for "Red," the formula should equal 2.
If filtered on column E for "Green," the formula should also equal 2.

Currently the "Red" filter results in 2, but the "Green" or any other filter results in 0.

This sounds like an easy fix. I spologize.

 

[Aladin Akyurek]

Got it... thanks. However, the formula is only counting the items that meet the criteria listed in the first used row (12). For example, given the data set:

Column D E

Row 12 Joe Red
Row 13 Bob Blue
Row 14 Joe Green
Row 15 Jon Red
Row 16 Joe Red
Row 17 Bob Green

If filtered on column E for "Red," the formula should equal 2.
If filtered on column E for "Green," the formula should also equal 2.

Currently the "Red" filter results in 2, but the "Green" or any other filter results in 0.

This sounds like an easy fix. I spologize.

Book4
	C	D	E	F
7
8		Filter:	Red
9		Distinct Count	2
10
11		Name	Color
12		Joe	Red
13		Bob	Blue
14		Joe	Green
15		Jon	Red
16		Joe	Red
17		Bob	Green
18
Sheet1

E8:

=IF(SUBTOTAL(3,E12:E241)=COUNTA(E12:E241),"",INDEX(E12:E241,MATCH(1,SUBTOTAL(3,OFFSET(E12:E241,ROW(E12:E241)-ROW(E12),,1)),0)))

which must be confirmed with control+shift+enter.

Added in order to show you the filter applied to the E-range.

E9:

=COUNTDIFF(IF(SUBTOTAL(3,OFFSET(D12,ROW(D12:D241)-ROW(D12),,1)),D12:D241,0),FALSE,0)

which you must confirm with control+shift+enter.

Shows the count of distinct names depending on the filter appled on the E-range.

 

[Sanador]

That does it... Thanks!