Excel formula help please
 

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

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!

Thanks Zlotti

Just what I wanted

Twodogs

The credit goes to Soho, I just put it in a sheet so that you could understand what he/she was saying.

But thanks for the thanks anyway!

Well thanks to you both Zlotti and Soho!!!

I just couldn't get it to work earlier.

Much appreciated of you both for helping out

Twodogs