F2
Oh, for F2’s sake. :smack:
Thanks.
probably a simple one…
Say I have a long list of names in a column. Each cell contains a first and last name.
Say I want to compute how many "Joe"s there are.
Is there a formula?
Thanks.
Counting Joes
The solution I can offer is that you add a column next to the name list, let’s say it is column B, then use this code:
=IF(A1=="Joe",1,0)
Then fill down for the entire height of the table. Then at the bottom of the table, do a sum of the column.
Oh, and of course A1 would change to whatever column letter the names were in.
– IG
=COUNTIF(A1:A10,“Joe”)
Where A1:A10 is the range you want to check.
Thank you, this is good.
Is there any way to ‘loosen’ the parameters? It doesn’t work even if the cell contains "Joe " much less “Joe Smith”
=COUNTIF(A1:A4,“Joe*”)
The asterisk is a wildcard, so anything will match.
Joey
"Joe "
Joe Smith
If you want you can also wildcard the start:
=COUNTIF(A1:A4,“Joe”)
This will also now find.
mynameisjoe
Brad Joenes
I think the problem is you may want intelligence about whether Joe is a Joe or not a Joe and I don’t know who to make that so.
SWEET! Thank you Caught@Work.
Sorry to bump this, but I had an annoying little problem with Excel.
Two seperate sheets, both unprotected. When I select a column (by clicking on the A or B etc) and ask Excel to sort each A-Z, one sheet will leave the top row alone, the other will include the top row in the sorting. I would like it to leave the top row alone in both and not include them in the sort, but I’ve no idea why it does it for one and not the other 
One of the options in the sort menu is ’ Header Row / No Header Row '.
This is shown in the ’ My List Has ’ area.
If you tell Excel that the list has a header row then this will not be included in the sort. If you tell Excel that your list does not have a header then this row will be included with the rest of the data.
Excellent, thanks 
Oh this is perfect! Help me oh Excel God. I am attempting to make a VBA program to automate the routine analysis at my Lab. Bear with me. I’m a n00b programmer. I have had only the most basic formal education with it.
The following does not work.
dim MyNum as Integer
MyNum = 15
ActiveCell.Offset(4, 0).Formula = "=AVERAGE(R[Number]C[-1]:R[5]C[-1])"
ActiveCell.Offset(4, 0).Formula = "=AVERAGE(R[Cint(Number)]C[-1]:R[5]C[-1])"
Neither of the above lines compile. I suspect that Number is being read as a string(a problem I seem to have with ALOT of VB functions). How do I get it to be read as the value?
Harmonix, what is it your are trying to do?
It looks like you are declaring a dimension as an integer and giving it a value of 15 but this does not appear later on in the subroutine.
It also looks like you are trying to use the subroutine to enter 2 formulas into the same cell.
Cheers,
Captain.
Is the “Number” that appears later supposed to be the variable declared earlier as “MyNum” If so, so that’s one problem. Also, VB can get squirrely if you name variables things that are used in the VB language. I wouldn’t name a variable “Number”, for example.
Sorry yes. That was supposed to be myNum in there. I typed it all out quickly then went to bed. It’s not an actual code slice.
I put the one with cint to show that I tried type casting it, but it won’t work.
Oh Holy One, I can’t figure out this problem in Excel / Open Office Calc:
I can write a macro to get the spreadsheet to number one copy, print, number the next copy, print, etc. but this is not very efficient and very slow if I want to print a lot of copies.
I want the square colors to automatically change color based on the date entered and the current date.
So like if something lasts 2 years, and I enter in the cell todays date, then in 22 months, I want the cell to turn yellow and then when it expires, I want it to turn red.
And if I enter a date that was 3 years ago, the square should automatically turn red.
Does this make sense? Is it possible? Can the excel sheet somehow check the computer’s current time and date?
I don’t have Excel installed on my computer right now, but I know you can do this. I think the command for the current date is Date(). To change the cell color, search up Conditional Formatting in the Excel help file. In the Conditional Formatting window, you can enter your formula to change cell colors based on the date.
Yep, that’s conditional formatting. What I would do is type in the date into another cell, format that as number (for example, today’s date comes across as 39108), and then include in the conditional formatting box “Less than 39108”. Make the formatting background red.
Or if you want it variable, you could do =(Today())-730 for two years back. The minus days would depend on how long back you want it to go.
God I’m lost.
Could someone email me an .xls with a cell that is formated like that so I can look at it?
It should be like:
Date entered is more than two years ago = red
Date entered is between 2 years and 23 months ago=orange
Date entered is between 23 months and 21 months ago=yellow
And I need the colors to automatically change. Whenever the file is opened, the colors should reflect today’s date and those parameters.
Any takers?