Countif with subtotal function

[uswyne]

HI,

How to calculate only figure in column not figure with percentage.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Required Answer[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]2000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100%[/TD]
[TD]100%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]2000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100%[/TD]
[TD]100%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6002 =SUBTOTAL(9,A2:a7)[/TD]
[TD]6000[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

A quick way is to find/replace (ctrl-h) the % symbols with a text symbol like ‰, ℅, or a 0 subscript and superscript separated by a /

 

[footoo]

If none of numbers to be sub-totaled is 1 or less, and none of the % figures is greater than 100%, then :

=SUBTOTAL(9,A2:A7)-SUMIF(A2:A7,"<=1",A2:A7)

 

[uswyne]

Thanks you

If none of numbers to be sub-totaled is 1 or less, and none of the % figures is greater than 100%, then :

=SUBTOTAL(9,A2:A7)-SUMIF(A2:A7,"<=1",A2:A7)