Finding and Counting the same value?

[aidan80]

I have a question and I hope this is the right area to post this question. I’m a fairly good (general everyday use) excel user but..

I have an excel document that has information in 1000’s rows spread over a few dozen columns. What I’d like to do is figure out the quickest and easiest way to add a formula that will could the total values and put that number where I want it.

Example A1 to A2500 has codes in each row.. 100, 358, 262, 835, 595, 101.. etc right up to 999. The codes are not in order but correspond to other information in the spread sheet. I could sort them and count myself but that takes a long, long time! How can I get excel to search these rows in that column and tell me how many codes between 100 – 150 there are or how many between 100 – 200 there are. I can get it to count up how many 100 or 101 or 102.. etc but not a range of codes over a range of rows.

I hope this makes sense, it’s a fairly complicated spreadsheet but I think if I can get excel to do the hard work for me it’ll make life easier!

[GuruWannaB]

For example 100-150

Please Login or Register  to view this content.

This will tell you the occurrances of numbers greater than 99 and less than 151 (giving you the range of 100-150).

Hope that helps,

GWB.

[aidan80]

For example 100-150

Please Login or Register  to view this content.

This will tell you the occurrances of numbers greater than 99 and less than 151 (giving you the range of 100-150).

Hope that helps,

GWB.

That is excellent, there just isn't any words to describe how brilliant that is! Perfect.. I owe you a beer or 2!!!, now I can make a couple of formulas and once in excel will do all the hard work!..

Which makes me think... (cracks whip at excel..) is there a way for us to have excel recognize names?..

Example.. I have a very large spreadsheet with names, it's a payroll report. I have a name in A1 Bob for example.. under it in B2, B3, B4..etc is a code VAC = Vacation, SL = Sick, HD = Holiday.. etc in C2, C3, C4..etc is the amount of time used for each code.. 24, 1.25, 2, 9.. etc Then there are a few spaces and it starts all over again for the next person on the list. Can I get it to pickup that.. bob has used 8 hours of sick and 2 hours of holiday time and stick the correct codes with the correct figures in another area..

I have another spread sheet that I have put simple formulas into that add, divides and multiply the figures from the payroll report. If I could have the payroll report import the figures into the correct places it would basically do the rest itself! At the minute I manually add them in which takes time and adds human error into the equation!

Thanks again for that formula.. that really will take hours off my workload!