Need to count how many cells are showing the value of 1

michaelsmith559

Well-known Member
Joined
Oct 6, 2013
Messages
881
Office Version
  1. 2013
  2. 2007
I have some data and need to count the cells that are showing the number 1 in them for each row. The actual values is not 1, but that is the value displayed in the cell. Column G should contain the count for the cells showing 1. For the data I will post below, Cell G1 should say 1, G2=2, etc..


Cell Formulas
RangeFormula
A11.00000105596374
A21.00000143152433
A30.999998749143804
A40.999999102130518
A50.999998419223428
A60.999999775769888
A71.00000145200432
A81.0000007567326
A91.00000009061247
A100.999998131726928
A111.00000031010753
A120.999999517792842
A131.0000017445396
A140.999999027853955
A150.999999127243393
A160.999999845166216
A171.00000024692733
A180.999999673579045
A191.00000049628297
A200.999998239295641
A211.00000123436048
A220.999998074248783
A231.0000002199648
A241.00000022307131
A250.999998357931597
B11.00000566471561
B20.999998235458387
B31.00000029667297
B40.999996208731011
B51.00000221510198
B60.999999733676109
B71.00000293638573
B80.999998905200415
B91.00000153711656
B101.00000004790335
B110.999999434094907
B120.999998761917833
B130.999999519681953
B140.999998136316993
B151.00000054453131
B161.00000158837733
B171.00000192728124
B181.00000181505565
B190.999998543489675
B201.0000023297123
B210.999999073880306
B220.999998138395641
B230.99999911399857
B240.999999931654332
B251.0000006901964
C10.999998012162875
C20.999999818453458
C30.999999246696202
C41.0000017978853
C51.00000194067061
C61.00000081261353
C70.99999811817489
C80.999998437490286
C90.99999864769873
C100.999998144414873
C111.00000007914792
C120.999999409280641
C130.999999050770842
C140.999998595172786
C150.999998775482285
C161.00000170261059
C170.999999008969261
C180.999998709395532
C191.00000064093711
C200.999999917940132
C211.00000067512737
C221.00000068242156
C230.999999305672505
C241.00000185055506
C251.00000138490942
D11.00000095315979
D21.00000068349124
D31.00000052678319
D41.00000053113915
D51.00000041746206
D60.999998392245081
D70.999998157355645
D80.999999154930226
D90.999998413774591
D101.00000063993833
D111.00000031953234
D120.999998641497462
D130.999999415081281
D141.00000132390671
D150.999999543479939
D161.00000037976858
D170.999997965234996
D181.00000130466424
D190.999999663996247
D200.999998773746585
D210.999998420958243
D221.00000056405193
D231.00000006315242
D240.999998387560902
D251.00000022144574
E10.999999782888091
E20.999999554306223
E31.00000144624514
E41.00000177841886
E50.999999178332128
E61.00000156111062
E71.00000029021231
E81.0000003390203
E90.999999634081628
E100.999998402892892
E110.999999870328845
E120.999998852779516
E130.999999185037216
E140.999999014419725
E151.00000015111175
E160.999999649394014
E171.00000018764445
E181.00000123840333
E191.00000072799267
E201.00000040550939
E211.00000118841286
E221.00000144007307
E230.999998874735371
E240.999999511431214
E250.999999208542391
F10.99999834404249
F21.00000265825893
F30.999997293187738
F41.0000021244532
F50.999997633781284
F60.999997816301465
F71.00000082649137
F80.999999379527098
F90.999997703680373
F100.999997835560464
F111.00000046304584
F120.999997778489726
F130.999999802244203
F140.999998987934835
F151.00000134086266
F160.999999419961849
F171.00000165159135
F181.00000124387396
F190.999998978431892
F201.00000034084662
F211.0000018782175
F220.999999512405926
F231.00000019986343
F241.00000096272649
F251.00000013093031
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Countif won't be able to look at the formatted value.
You'll have to do countifS with 2 criteria, >0.99999 and <1.000005 (whatever your tolerances are.)
 
Upvote 0
Ah. I missed that. Doh. Perhaps this:

Code:
{=SUM(--(ROUND($A1:$F1,6)=1))}

Array formula, entered with Control-Shift-Enter (don't try and enter the curly brackets yourself)

WBD
 
Upvote 0

Forum statistics

Threads
1,224,928
Messages
6,181,811
Members
453,067
Latest member
mdiz777

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