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

[michaelsmith559]

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..

Excel 2007
	A	B	C	D	E	F
1	1.000001	1.000006	0.999998	1.000001	1	0.999998
2	1.000001	0.999998	1	1.000001	1	1.000003
3	0.999999	1	0.999999	1.000001	1.000001	0.999997
4	0.999999	0.999996	1.000002	1.000001	1.000002	1.000002
5	0.999998	1.000002	1.000002	1	0.999999	0.999998
6	1	1	1.000001	0.999998	1.000002	0.999998
7	1.000001	1.000003	0.999998	0.999998	1	1.000001
8	1.000001	0.999999	0.999998	0.999999	1	0.999999
9	1	1.000002	0.999999	0.999998	1	0.999998
10	0.999998	1	0.999998	1.000001	0.999998	0.999998
11	1	0.999999	1	1	1	1
12	1	0.999999	0.999999	0.999999	0.999999	0.999998
13	1.000002	1	0.999999	0.999999	0.999999	1
14	0.999999	0.999998	0.999999	1.000001	0.999999	0.999999
15	0.999999	1.000001	0.999999	1	1	1.000001
16	1	1.000002	1.000002	1	1	0.999999
17	1	1.000002	0.999999	0.999998	1	1.000002
18	1	1.000002	0.999999	1.000001	1.000001	1.000001
19	1	0.999999	1.000001	1	1.000001	0.999999
20	0.999998	1.000002	1	0.999999	1	1
21	1.000001	0.999999	1.000001	0.999998	1.000001	1.000002
22	0.999998	0.999998	1.000001	1.000001	1.000001	1
23	1	0.999999	0.999999	1	0.999999	1
24	1	1	1.000002	0.999998	1	1.000001
25	0.999998	1.000001	1.000001	1	0.999999	1
Sheet1

Cell Formulas
Range	Formula
A1	1.00000105596374
A2	1.00000143152433
A3	0.999998749143804
A4	0.999999102130518
A5	0.999998419223428
A6	0.999999775769888
A7	1.00000145200432
A8	1.0000007567326
A9	1.00000009061247
A10	0.999998131726928
A11	1.00000031010753
A12	0.999999517792842
A13	1.0000017445396
A14	0.999999027853955
A15	0.999999127243393
A16	0.999999845166216
A17	1.00000024692733
A18	0.999999673579045
A19	1.00000049628297
A20	0.999998239295641
A21	1.00000123436048
A22	0.999998074248783
A23	1.0000002199648
A24	1.00000022307131
A25	0.999998357931597
B1	1.00000566471561
B2	0.999998235458387
B3	1.00000029667297
B4	0.999996208731011
B5	1.00000221510198
B6	0.999999733676109
B7	1.00000293638573
B8	0.999998905200415
B9	1.00000153711656
B10	1.00000004790335
B11	0.999999434094907
B12	0.999998761917833
B13	0.999999519681953
B14	0.999998136316993
B15	1.00000054453131
B16	1.00000158837733
B17	1.00000192728124
B18	1.00000181505565
B19	0.999998543489675
B20	1.0000023297123
B21	0.999999073880306
B22	0.999998138395641
B23	0.99999911399857
B24	0.999999931654332
B25	1.0000006901964
C1	0.999998012162875
C2	0.999999818453458
C3	0.999999246696202
C4	1.0000017978853
C5	1.00000194067061
C6	1.00000081261353
C7	0.99999811817489
C8	0.999998437490286
C9	0.99999864769873
C10	0.999998144414873
C11	1.00000007914792
C12	0.999999409280641
C13	0.999999050770842
C14	0.999998595172786
C15	0.999998775482285
C16	1.00000170261059
C17	0.999999008969261
C18	0.999998709395532
C19	1.00000064093711
C20	0.999999917940132
C21	1.00000067512737
C22	1.00000068242156
C23	0.999999305672505
C24	1.00000185055506
C25	1.00000138490942
D1	1.00000095315979
D2	1.00000068349124
D3	1.00000052678319
D4	1.00000053113915
D5	1.00000041746206
D6	0.999998392245081
D7	0.999998157355645
D8	0.999999154930226
D9	0.999998413774591
D10	1.00000063993833
D11	1.00000031953234
D12	0.999998641497462
D13	0.999999415081281
D14	1.00000132390671
D15	0.999999543479939
D16	1.00000037976858
D17	0.999997965234996
D18	1.00000130466424
D19	0.999999663996247
D20	0.999998773746585
D21	0.999998420958243
D22	1.00000056405193
D23	1.00000006315242
D24	0.999998387560902
D25	1.00000022144574
E1	0.999999782888091
E2	0.999999554306223
E3	1.00000144624514
E4	1.00000177841886
E5	0.999999178332128
E6	1.00000156111062
E7	1.00000029021231
E8	1.0000003390203
E9	0.999999634081628
E10	0.999998402892892
E11	0.999999870328845
E12	0.999998852779516
E13	0.999999185037216
E14	0.999999014419725
E15	1.00000015111175
E16	0.999999649394014
E17	1.00000018764445
E18	1.00000123840333
E19	1.00000072799267
E20	1.00000040550939
E21	1.00000118841286
E22	1.00000144007307
E23	0.999998874735371
E24	0.999999511431214
E25	0.999999208542391
F1	0.99999834404249
F2	1.00000265825893
F3	0.999997293187738
F4	1.0000021244532
F5	0.999997633781284
F6	0.999997816301465
F7	1.00000082649137
F8	0.999999379527098
F9	0.999997703680373
F10	0.999997835560464
F11	1.00000046304584
F12	0.999997778489726
F13	0.999999802244203
F14	0.999998987934835
F15	1.00000134086266
F16	0.999999419961849
F17	1.00000165159135
F18	1.00000124387396
F19	0.999998978431892
F20	1.00000034084662
F21	1.0000018782175
F22	0.999999512405926
F23	1.00000019986343
F24	1.00000096272649
F25	1.00000013093031

 

[wideboydixon]

G1 formula:

=COUNTIF($A1:$F1,1)

WBD

 

[Jonmo1]

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.)

 

[wideboydixon]

Ah. I missed that. Doh. Perhaps this:

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

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

WBD

 

[michaelsmith559]

Thanks, works great.