# SUBTOTAL and SUMPRODUCT

##### Board Regular
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.

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

##### MrExcel MVP
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.

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

##### Board Regular
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?

##### MrExcel MVP
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.

##### Board Regular
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.

##### MrExcel MVP
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
CDEF
7
8Filter:Red
9Distinct Count2
10
11NameColor
12JoeRed
13BobBlue
14JoeGreen
15JonRed
16JoeRed
17BobGreen
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.

##### Board Regular
That does it... Thanks!

Replies
9
Views
271
Replies
2
Views
458
Replies
3
Views
340
Replies
3
Views
370
Replies
3
Views
267

1,195,673
Messages
6,011,088
Members
441,582
Latest member
Topkapi

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back