# Limit A/F Record count to ONLY "SI" rows

#### jim may

##### Well-known Member
In my multi-column AutoFilter Column B (2)
I have

WhosePrc (this is the header in B7)
SI (1st record in B8)
SI Average

zCP
zCP Average

SI
SI Average

zCP
zCP Average

SI
SI Average

zCP

zCP Average

Ordinarily I would use = subtotal(3,B8:B1000), but the count is Much greater than
the SI count ONLY.. How can I alter my formula to Subtotal only the SI records?

### Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Is this what you mean?

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B8:B1000,ROW(B8:B1000)-ROW(B8),0,1)),--(B8:B1000="SI"))

Hope this helps!

Domenic -- YOU ARE EXACTLY right !!!

Thanks,

After sending this 30 minutes ago -- I cound not find it posted on the board, so I sent it again -- CAUSING it to show up twice - don't you know grrrrrrrr

Thanks again,

Jim

Domenic,

With some "after-thought" -- not in a million years would I have come up with the formula you did. Is there some "background" on why these certain "side-references" are necessary in order to finally get to the answer. I previously thought a CSE formula
would have been the answer, but I guessed wrong,,,,

Thanks again,

Jim

Have a look at Aladin's explanation here...

Hope this helps!

Replies
1
Views
1K
Replies
5
Views
284
Replies
1
Views
394
Replies
9
Views
981
Replies
4
Views
753

1,196,483
Messages
6,015,460
Members
441,897
Latest member
erma

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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