Countif with subtotal function

U

uswyne

Board Regular
Joined
Jul 27, 2017
Messages
78
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2010
Platform
  1. Windows
HI,

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

Required Answer
10001000
20002000
10001000
100%100%
20002000
100%100%
6002 =SUBTOTAL(9,A2:a7)6000

<tbody>
</tbody>
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
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 /
 
Upvote 0
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)
 
Last edited:
Upvote 0
Thanks you

footoo said:
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)
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

DB73
  • Solved
userform combobox not showing percentage in pulldown
Replies
3
Views
296
dmt32
D
C
  • Question
VBA: Filter specific data from table then make formula and put result to summary workbook
Replies
1
Views
400
HighAndWilder
H
C
  • Question
Issues subtotaling and breaking down the subtotal outline to obtain records for copying.
Replies
0
Views
432
Chris101
C
D
  • Question
Nesting FILTER function inside of the AGGREGATE function?
Replies
1
Views
1K
Domenic
D
A
  • Question
code to copy last row & exclude zero value of the data in last row for each name
Replies
0
Views
137
Abdo
A

Forum statistics

Threads
1,215,375
Messages
6,124,576
Members
449,174
Latest member
chandan4057

Share this page

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?

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
Back
Top