Count on Filtered results not working

[BTL]

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.

 

[shemayisroel]

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]

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]

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
	A	B	C	D	E	F	G	H	I	J	K	L
3	SS	PT	Area	Branch	Site	ID	Phone	Fax	U GRADE	SS Mem	SS Non	%
369	1	2	#DIV/0!	Huntly	1723	Glen Massey School	078248423	(07)-824-8473	U2	2	0	100
370	2	#DIV/0!	#DIV/0!	Huntly	0119	Huntly College	078287152	(07)-828-7153	Sec	11	3	79
371	1	2	#DIV/0!	Huntly	1751	Huntly School	078287198	(07)-828-6154	U4	9	0	100
372	1	2	#DIV/0!	Huntly	1752	Huntly West School	078288371	(07)-828-8545	U4	3	0	100
373	1	2	#DIV/0!	Huntly	1777	Kimihia School	078287495	078287695	U5	7	0	100
374	3	2	#DIV/0!	Huntly	1856	Ohinewai School	078288919	(07)-828-8918	U3	2	1	67
375	3	2	#DIV/0!	Huntly	1869	Orini Combined School	078244847	(07)-824-4605	U3	1	1	50
376	1	1	#DIV/0!	Huntly	1905	Pukemiro School	078284769	(07)-828-4969	U1	1	0	100
377	#DIV/0!	#DIV/0!	#DIV/0!	Huntly	1917	Rakaumanga School	078289013	(07)-828-8080				#DIV/0!
378	6	#DIV/0!	#DIV/0!	Huntly	1919	Rangiriri School	078263654	(07)-826-3654	U1	0	1	0
379	1	2	#DIV/0!	Huntly	1937	Ruawaro Combined School	078266706	(07)-826-6939	U2	3	0	100
380	1	2	#DIV/0!	Huntly	1943	St Anthonys School Huntly	078288516	(07)-828-8509	U2	3	0	100
381	1	1	#DIV/0!	Huntly	1987	Taupiri School	078246713	(07)-824-6535	U2	2	0	100
382	6	3	#DIV/0!	Huntly	1998	Te Akau School	078254777	078254776	U1	0	1	0
383	4	#DIV/0!	#DIV/0!	Huntly	0115	Te Kauwhata College	078263715	(07)-826-3714	Sec	4	9	31
384	1	2	#DIV/0!	Huntly	2005	Te Kauwhata School	078263564	078263850	U4	8	0	100
385	1	3	#DIV/0!	Huntly	2046	Waerenga School	078267871	(07)-826-7831	U2	1	0	100
386	6	1	#DIV/0!	Huntly	2052	Waikaretu School	092333014	(09)-233-3017	U1	0	1	0
387	1	1	#DIV/0!	Huntly	2060	Waingaro School	078254714	078254713	U1	1	0	100
388	6	1	#DIV/0!	Huntly	2070	Waiterimu School	078285806	(07)-828-5806	U1	0	1	0
Sheet

 

[Peter_SSs]

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]

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

 

[BTL]

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?