# Count on Filtered results not working

#### BTL

##### New Member
I am using:

=SUMPRODUCT(SUBTOTAL(103,OFFSET('Branch mapping'!A4:A1127,ROW('Branch mapping'!A4:A1127)-MIN(ROW('Branch mapping'!A4:A1127)),,1)),--('Branch mapping'!A4:A1127='Branch Summary'!C48))

where C48 has the value of 6, to count the number of times the value 6 occurs in the filtered results of A4:A1127. I have cut and pasted the formula from other cells in the sheet where it works fine on other ranges and and values but with this version I get the answer #DIV/0. Any suggestions why this might be the case.

Have to admit I do not understand how this formula works but it is working elsewhere on the sheet.

Any suggestions most welcome.

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### shemayisroel

##### Well-known Member
BTL Hi & Welcome,

Any suggestions would be guesses as if the formula is working somewhere else and does not work at the new place then I'm assuming that there is something in your data range thats causing the error...

I'd suggest you post some sample data with expected results and therefore someone can possibly post you a solution, use the below to post your sample data

http://www.excel-jeanie-html.de/index.php?f=1

#### Peter_SSs

##### MrExcel MVP, Moderator
I am using:

=SUMPRODUCT(SUBTOTAL(103,OFFSET('Branch mapping'!A4:A1127,ROW('Branch mapping'!A4:A1127)-MIN(ROW('Branch mapping'!A4:A1127)),,1)),--('Branch mapping'!A4:A1127='Branch Summary'!C48))

where C48 has the value of 6, to count the number of times the value 6 occurs in the filtered results of A4:A1127. I have cut and pasted the formula from other cells in the sheet where it works fine on other ranges and and values but with this version I get the answer #DIV/0. Any suggestions why this might be the case.

Have to admit I do not understand how this formula works but it is working elsewhere on the sheet.

Any suggestions most welcome.
There is no division in your formula, so the formula itself should not be causing that error. I would suspect that one (or more) of the cells in the data that this formula refers to contains a division formula that is resulting in a #DIV/0! error.

#### BTL

##### New Member
Here is an example of the data. Could it be that there are some #DIV/0 results in there that are stuffing up the result?
Excel Workbook
ABCDEFGHIJKL
3SSPTAreaBranchSiteIDPhoneFaxU GRADESS MemSS Non%
36912#DIV/0!Huntly1723Glen Massey School078248423(07)-824-8473U220100
3702#DIV/0!#DIV/0!Huntly0119Huntly College078287152(07)-828-7153Sec11379
37112#DIV/0!Huntly1751Huntly School078287198(07)-828-6154U490100
37212#DIV/0!Huntly1752Huntly West School078288371(07)-828-8545U430100
37312#DIV/0!Huntly1777Kimihia School078287495078287695U570100
37432#DIV/0!Huntly1856Ohinewai School078288919(07)-828-8918U32167
37532#DIV/0!Huntly1869Orini Combined School078244847(07)-824-4605U31150
37611#DIV/0!Huntly1905Pukemiro School078284769(07)-828-4969U110100
377#DIV/0!#DIV/0!#DIV/0!Huntly1917Rakaumanga School078289013(07)-828-8080#DIV/0!
3786#DIV/0!#DIV/0!Huntly1919Rangiriri School078263654(07)-826-3654U1010
37912#DIV/0!Huntly1937Ruawaro Combined School078266706(07)-826-6939U230100
38012#DIV/0!Huntly1943St Anthonys School Huntly078288516(07)-828-8509U230100
38111#DIV/0!Huntly1987Taupiri School078246713(07)-824-6535U220100
38263#DIV/0!Huntly1998Te Akau School078254777078254776U1010
3834#DIV/0!#DIV/0!Huntly0115Te Kauwhata College078263715(07)-826-3714Sec4931
38412#DIV/0!Huntly2005Te Kauwhata School078263564078263850U480100
38513#DIV/0!Huntly2046Waerenga School078267871(07)-826-7831U210100
38661#DIV/0!Huntly2052Waikaretu School092333014(09)-233-3017U1010
38711#DIV/0!Huntly2060Waingaro School078254714078254713U110100
38861#DIV/0!Huntly2070Waiterimu School078285806(07)-828-5806U1010
Sheet

#### Peter_SSs

##### MrExcel MVP, Moderator
Here is an example of the data. Could it be that there are some #DIV/0 results in there that are stuffing up the result?
Yep, that's exactly what I was suggesting ...
I would suspect that one (or more) of the cells in the data that this formula refers to contains a division formula that is resulting in a #DIV/0! error.

#### BTL

##### New Member
Didn't quite understand you first time. Anyway, thanks heaps for your help.

#### BTL

##### New Member
I used ISERROR successfully to remove the #DIV/0 results in column A. This in turn changed the #DIV/0 result to one of 0. The only problem is, using the data I posted, the result should be 4. Any suggestions?

Replies
9
Views
404
Replies
3
Views
254
Replies
2
Views
444
Replies
9
Views
701
Replies
6
Views
569

Threads
1,190,677
Messages
5,982,216
Members
439,769
Latest member
trungminh2802

### 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

### Disable AdBlock

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