Good Morning

Could some one help me out with a formula please?

I have 8 columns of numbers 0 to 24 , I want to add a column and it will total the number of columns that a value 1 to 3 is found!! Another words if all 8 columns have a number 1 to 3 in them it will give me a value of 8 in my 9th column.

Thanks
Twodogs

There's probably a better way, but I would add a field at the bottom of each column with a flag identifying if the column has 1, 2 or 3 in it. Then simply total the row. For instance A26 would contain "=if(countif(A1:A24,"<4"),1,0)". Copy this thru remaining 7 columns (B:H)

The 9th column (say I26) would contain a field with "=SUM(A26:H26)"

I am sure there is a more elegant method but this will work.

Good luck.

Twodogs

This one should work for you. To run the formula, make sure you hold down ctrl+shift and then hit enter.

=SUM(IF(A1:H1>0,IF(A1:H1<=3,1,0),0))


Soho

Sorry Soho

It doesn't seem to work

Twodogs

Thanks SeeDee

I was hoping to do it via one formula if possible!! Maybe it isn't!!

Twodogs

No Twodogs. If a column has say 1 AND 2 in it you would add two to your total (or worse, three), when I think you would be looking for one.

SeeDee,

I have about 450 rows of data and I just want to isolate 8 columns for closer inspection. Thanks for your help

Twodogs

Twodogs

If all you want is a simple count of how many columns from A-H contain 1,2 or 3, the formula I sent before will work. Are you after a count or a sum/total?
After using ctrl+shift enter the formula will appear like this....
****=SUM(IF(A1:H1>0,IF(A1:H1<=3,1,0),0))****

Soho

Soho

Just a count. Another words a column that has the total number time 1 to 3 appeared in the previous 8 columns

Twodogs

The formula is working in column 9, isolate your data into your 8 required columns and then cut and paste the formula from this sheet to yours.

The second tab is showing the formula only.

Hope it helps!

Attached Files

Two Dogs.xls (23.0 KB, 622 views)