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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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
285
dmt32
D
C
  • Question
VBA: Filter specific data from table then make formula and put result to summary workbook
Replies
1
Views
378
HighAndWilder
H
C
  • Question
Issues subtotaling and breaking down the subtotal outline to obtain records for copying.
Replies
0
Views
409
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
125
Abdo
A

Forum statistics

Threads
1,214,653
Messages
6,120,750
Members
448,989
Latest member
mariah3

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