Another question about IF(COUNTIF) checks in Excel

<Tiff1618@discussions.microsoft.com> wrote in message 
> news:219EB429-F90A-406E-A208-5C787ED70467@microsoft.com...
> > Hey again,
> >
> > Is there a formula I can use to figure out if there is one specific phrase
> > in a selection?
> >
> > I'm updating the attendance prgram at the school that I work at. Every
> > student has their own attendance sheet in Excel, and each sheet sort of 
> > looks
> > like this:
> >
> >             |Monday|
> > Period 1:|     A    |        (A=Absent; S=Seat time)
> > Period 2:|     S    |
> > Period 3:|     A    |
> >
> > So basically, if they have one or more "S"s, we can count them as being
> > present for the day. I need the formula to find out if there is an "S" in
> > that column, and if there is, I need it to type an "S" in the attendance
> > report, which sort of looks like this:
> > DATE:             |1|2|3|
> > Student Name |S|  |  |
> >
> > Sorry if that got a little complicated. Is there any way to do this? 

Oh, one more question. This is rediculously complicated, I know, but...  when 
you use the IF(COUNTIF) formula, can you put another IF(COUNTIF) check in 
the "value if false" spot? Because if there isn't an "S" in the column, it 
needs to be able to check for a "P" or an "N" (independent study or not 
enrolled) and mark 
that instead. 

I've been trying to figure it out with the =OR formula but I keep getting 
errors. This is what I've been typing that's not working and I don't know why:

=OR(IF(COUNTIF(AA8:AA10,"S"),"S",(IF(COUNTIF(AA8:AA10,"P"),"P","A"))))

Any ideas?

0
Tiff1618 (10)
9/3/2005 11:29:02 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
632 Views

Similar Articles

[PageSpeed] 38

Maybe...

=IF(COUNTIF(AA8:AA10,"S"),"S",IF(COUNTIF(AA8:AA10,"P"),"P","A"))

would do what you want.


Tiff1618 wrote:
> 
> <Tiff1618@discussions.microsoft.com> wrote in message
> > news:219EB429-F90A-406E-A208-5C787ED70467@microsoft.com...
> > > Hey again,
> > >
> > > Is there a formula I can use to figure out if there is one specific phrase
> > > in a selection?
> > >
> > > I'm updating the attendance prgram at the school that I work at. Every
> > > student has their own attendance sheet in Excel, and each sheet sort of
> > > looks
> > > like this:
> > >
> > >             |Monday|
> > > Period 1:|     A    |        (A=Absent; S=Seat time)
> > > Period 2:|     S    |
> > > Period 3:|     A    |
> > >
> > > So basically, if they have one or more "S"s, we can count them as being
> > > present for the day. I need the formula to find out if there is an "S" in
> > > that column, and if there is, I need it to type an "S" in the attendance
> > > report, which sort of looks like this:
> > > DATE:             |1|2|3|
> > > Student Name |S|  |  |
> > >
> > > Sorry if that got a little complicated. Is there any way to do this?
> 
> Oh, one more question. This is rediculously complicated, I know, but...  when
> you use the IF(COUNTIF) formula, can you put another IF(COUNTIF) check in
> the "value if false" spot? Because if there isn't an "S" in the column, it
> needs to be able to check for a "P" or an "N" (independent study or not
> enrolled) and mark
> that instead.
> 
> I've been trying to figure it out with the =OR formula but I keep getting
> errors. This is what I've been typing that's not working and I don't know why:
> 
> =OR(IF(COUNTIF(AA8:AA10,"S"),"S",(IF(COUNTIF(AA8:AA10,"P"),"P","A"))))
> 
> Any ideas?

-- 

Dave Peterson
0
petersod (12004)
9/3/2005 12:30:29 PM
Perfect. Thank you so much!

"Dave Peterson" wrote:

> Maybe...
> 
> =IF(COUNTIF(AA8:AA10,"S"),"S",IF(COUNTIF(AA8:AA10,"P"),"P","A"))
> 
> would do what you want.
> 
> 
> Tiff1618 wrote:
> > 
> > <Tiff1618@discussions.microsoft.com> wrote in message
> > > news:219EB429-F90A-406E-A208-5C787ED70467@microsoft.com...
> > > > Hey again,
> > > >
> > > > Is there a formula I can use to figure out if there is one specific phrase
> > > > in a selection?
> > > >
> > > > I'm updating the attendance prgram at the school that I work at. Every
> > > > student has their own attendance sheet in Excel, and each sheet sort of
> > > > looks
> > > > like this:
> > > >
> > > >             |Monday|
> > > > Period 1:|     A    |        (A=Absent; S=Seat time)
> > > > Period 2:|     S    |
> > > > Period 3:|     A    |
> > > >
> > > > So basically, if they have one or more "S"s, we can count them as being
> > > > present for the day. I need the formula to find out if there is an "S" in
> > > > that column, and if there is, I need it to type an "S" in the attendance
> > > > report, which sort of looks like this:
> > > > DATE:             |1|2|3|
> > > > Student Name |S|  |  |
> > > >
> > > > Sorry if that got a little complicated. Is there any way to do this?
> > 
> > Oh, one more question. This is rediculously complicated, I know, but...  when
> > you use the IF(COUNTIF) formula, can you put another IF(COUNTIF) check in
> > the "value if false" spot? Because if there isn't an "S" in the column, it
> > needs to be able to check for a "P" or an "N" (independent study or not
> > enrolled) and mark
> > that instead.
> > 
> > I've been trying to figure it out with the =OR formula but I keep getting
> > errors. This is what I've been typing that's not working and I don't know why:
> > 
> > =OR(IF(COUNTIF(AA8:AA10,"S"),"S",(IF(COUNTIF(AA8:AA10,"P"),"P","A"))))
> > 
> > Any ideas?
> 
> -- 
> 
> Dave Peterson
> 
0
Tiff1618 (10)
9/3/2005 7:56:02 PM
Reply:

Similar Artilces:

Userform question 12-24-09
Can anybody explain the differences between showing/loading a userform along with hiding/unloading a userform?? I'm guessing that a "load" will create the form in memory but it remains non-visible?? Does that mean that a call of "Userform1.Show" loads it in memory AND makes it visible?? Show : Calls the userform initialize function the first time and not afterwards and displays the userform Load : Is the same as show but doesn't display the userform. Hide : Exits the userform and keeps it in memory so the next time it is Show/Load is perfor...

Odd question re: workbook file sizes.
I've been tracking the performance of two IRA accounts for almost a year now. The number of stocks is almost identical in each account. I created a set of Excel workbooks for each account: Acct 1-Q1 and 2. and Acct 2-Q1 and 2. are complete. They have the same number of pages and are the same approximate size in kb. I'm still adding to the next two workbooks Acct 1-Q3 and 4 and Acct 2- Q3 and 4, but again they have the same number of pages. I'm just cut and pasting into blank worksheets data from a CSV spreadsheet I download from Fidelity. I just happened to notice the size ...

Decision Trees in excel
Help! How do I make decision trees in Excel? I know it's possible but can't find it anywhere. Thanks... That's not much info to work with, so you get a very generic answer: =IF(condition,result if true, result if false) condition can be something like A1<50 result can be a value or a formula (including another IF() function) I hope this helps. Post specifics with your questions, and you will get better answers. "Dani" <anonymous@discussions.microsoft.com> wrote in message news:077b01c3b6eb$979aa4d0$a001280a@phx.gbl... > Help! How do I make decisio...

dropdown list question
I am using an in-cell dropdown list that is populated from a vba string. I have encountered a 255 character limit for the list of lookup items. Is there any way around this other than using spreadsheet cells to store the list values? Thanks, Keith The data validation list can come from a delimited list or a worksheet range. You could store the list on a hidden sheet, if you don't want users to accidentally change it. keithb wrote: > I am using an in-cell dropdown list that is populated from a vba string. I > have encountered a 255 character limit for the list of lookup i...

matching a column of numbers to another in another spreadsheet
I have a spreadsheet of shortpayment amounts and their invoice numbers on one spreadsheet with a column of credit amounts with their invoice numbers on another spreadsheet. I need to match the credits written with the shortpayments. kinda like this spreadsheet 1 spreadsheet 2 inv # amount date Credit # Amount Date 122334 15.00 11-25-09 675555 15.00 12-10-09 223345 22.00 10-20-09 754444 22.00 111-25-09 naturally...

Acessing function with a DLL which has be loaded from another dll
I have A.dll and B.dll. Assume that A.dll has following functions: A1() A2() Assume that B.dll has following functions: B1() B2() I am loading B.dll from function A1() in A.dll.After Loading B.dll i make a call to B1().After executing the call the function returns to beginning of A2.dll. why is this so? Define "begining". The word tends to make no sense in this context. What you seem to be saying is that you have void A1() { HANDLE h = ::LoadLibrary(_T("B.DLL")); ...test for h==NULL, deal with error typedef void (*B1)() B1Proc; B1Proc B1; ...

Grabbing a number from another cell on another worksheet
Hello, I have a spreadsheet with a number of different worksheets. All I want to do is if I enter a number into a cell on the 2nd worksheet for example, I want it to automatically show in a specific cell on the first worksheet. So a simple copy and paste but for excel to automatically do it for me! it seems like something so easy that I just cant figure out how to do. Try this... Do this once. Select the cell on the 2nd sheet Right click>Copy Navigate to the other sheet and select the cell where you want the value to appear. Right click>Paste Special>Paste Lin...

Creating column content based on another column's keyword(s) #2
Max, That scriptlet was very useful, and easily tweakable to work in myria applications. Thx! -K -- KHashmi31 ----------------------------------------------------------------------- KHashmi316's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1043 View this thread: http://www.excelforum.com/showthread.php?threadid=27348 Glad to hear that ! Thanks for posting back -- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot>com ---- "KHashmi316" <KHashmi316.1evevz@excelforum-nospam.com> wrote in message news:KHashmi316.1e...

Resizing embedded excel object
I am having problems resizing an embedded Excel spreadsheet in Word 2004 for mac. The object was inserted w/ the paste special command. What I am trying to do is to increase the amount of info viewed from the spreadsheet, not just resize the "ole image". If I grab the handles it only resizes the image, it doesn't expand the "window to excel". I have tried cutting and pasting the new excel range while in the edit-worksheet object mode, but it won't allow paste special. Any idea's? Hi Setter1, Subject to Word's in-built limitations as to how many Excel rows...

A simple Question
I am a little new to macros and I am having trouble using a suggested macro in my spreadsheet. My simple question is what do I need to do to change the following macro so that it will work on my spreadsheet. Sub DeleteEmptyRows(DeleteRange As Range) ' Deletes all empty rows in DeleteRange ' Example: DeleteEmptyRows Selection ' Example: DeleteEmptyRows Range("A1:D100") Dim rCount As Long, r As Long If DeleteRange Is Nothing Then Exit Sub If DeleteRange.Areas.Count > 1 Then Exit Sub With DeleteRange rCount = .Rows.Count For r = rCount To 1 Step -1 If Application.CountA...

Populate data in cell by looking at another cells data
Hi Everyone, Hope I find you well. I'm not even sure how to go about this, so I hope that someone can shed some light. I have a serial number in one cell eg '80199DD270238' where 'DD' is code for another value, in this case 'DD' = 'BLUE'. How can I automatically populate a cell with the value 'BLUE' by looking at the serial number. Many thanks for any help you can provide. Best Regards Gazza Hi if the characters are alsways at the same place use =IF(MID(A1,6,2)="DD","Blue","other color") if they could be at ...

Sum dependant on '1' value in another column
Hi, Let's say I have a '1' or nothing in column 'L' and a time in seconds in column 'B'. The '1' means the time is a legitimate one. Can I do a sum of all the 'legitimate' times in column 'B' based on whether there is a corresponding '1' in column 'L'? Is there a formula that will allow me to do that? Sorry for the ignorance of the question but it is late and I am struggling to make this spreadsheet work for me. Thanks in advance, Dave Hi DL, Look at the SUMIF function in Excel help. Try: =SUMIF(L1:L10...

VBA Excel Application on the Web
I have an EXCEL financial application written in VBA that I would like to publish on the web. Does anyone know a product or way to do this -- and how to protect it from hackers??!! I don't know anything about publishing to the web, but if you're code is written in VBA, then the only way to protect it from hackers is to keep it private. Excel's protection (even project protection) is very weak. You could keep many (most?) out of the project by: (within the VBE) tools|VBAProject Properties|Protection tab (but it won't stop anyone who really cares.) lancish wrote: > >...

is it possible to execute write to the fields in another .xsl form a macro in another .xsl? e.g. some way to load another .xsl into an .xsl macro and write to its data? #2
is it possible to execute write to the fields in another .xsl form a macro in another .xsl? e.g. some way to load another .xsl into an .xsl macro and write to its data? ...

Automatic Link to another workbook
I have a workbook created that, every time I open it, I get the question: Do you want to update the automatic link to data in another workbook? I didn't consciously set up a link and I don't want to get this message. How do I find this link and break it so I will not continue to get this message? Any help is appreciated. Close any other Excel workbooks you have open and do a "Find" for :\ (colon backslash). If you have several worksheets in your workbook, ask Excel to search the entire workbook by selecting find within "Workbook" instead of "Sheet&quo...

Excel Chart Legends need alignment option (rotate 90-180-270 deg)
We need the ability to rotate legends in Excel charts. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=45fc3eb0-0201-42d5-a926-4eb215e72eab&dg=microsoft.public.excel.charting Not an option. -- Bern...

Excel macro concatenate each 2 rows data into 1 row for all sheet
Hi all, I have an excel sheet of 12000 rows of datas and I need to concetenate the datas in each 2 rows into 1 row with fixed size and delete the previous ones if possible or to create a new sheet with the concatenated datas to the same workbook. Eg: Before A B C D E ... 1 Data1 Data2 Data3 Data4 Data5 2 Data6 Data7 Data8 Data9 Data10 3 Data11 Data12 Data13 Data14 Data15 4 Data16 Data17 Data18 Data19 Data20 5 Data21 Data22 Data23 Data24 Data25 6 Data26 Data27 Data28 Data29 Data30 After A ...

Budget Forecast question
Hi - I've just started using Money 2000 and I've set up a Budget. My question is that in the Budget Forecast graph that I can display, I want to know how to verify that my Budget is valid. If I hover over the first bar in the Forecast (My graph is set to show 'days' as a level of detail) I see 2 figures - one for Day-to-Day Accounts and one for Occasional Expense Fund. If I add these figures together I get a total. What can I check this total against? I've tried looking at the current balances in my accounts and looking at the expenses I have set in my Budget but ...

Insert blank rows repeatedly between every data row in Excel
Could you guide me please.... I need to insert 5 blank rows repeatedly between every existing data rows for approximately 300 rows. If I go about doing the repeat short-cut "Control+Y", it just repeats inserting ONE row only between the consecutive data row. Is there some command, which helps me highlight all the rows & allows me to insert 5 blank rows between every consecutive existing data row? Thanks in advance for your kind advice. Hi Being very new to excel coding, this code might be lengthy, but it works. Public Sub insert_row() Const TestColumn As Long = 1 '...

Hello, question about software assurance and Office 2008
Does anyone know when Mac Office 2008 will be available for those who have software assurance? I've seen many posts claiming the date is Feb 1st. I have yet to receive any feedback from my retailer. Thanks in advance for any help. No, we don't. Ring your account manager :-) On 11/01/08 6:57 AM, in article c0e5e9b3-9f54-4f57-94da-eaf8192d1ff6@h11g2000prf.googlegroups.com, "jerry.kowalewski@gmail.com" <jerry.kowalewski@gmail.com> wrote: > Does anyone know when Mac Office 2008 will be available for those who > have software assurance? I've seen many posts c...

Checking formulas accross multiple sheets
Can anyone tell me a fast way to check the accuracy of multiple formulas accross multiple worksheets? I have a workbook with about 50 to 60 worksheets in it and I need to check all the formulas for accuracy. Thanks in advance! Rick --- Message posted from http://www.ExcelForum.com/ I have no idea how you would check a formula for accuracy. Can you provide further insight what you mean. What you are looking for, and how you would do this manually. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Sea...

Using check boxes to limit a report.
I have a table which lists all possible items for a vendor. I want to create a form that shows all of the items. I want the user to be able to place check marks next to the items they want. I then want to run a report which shows all of the information about the items, but only for the items that had been checked. What is a good way to approach this? I can't seem to find a way to link the check boxes to the item table and don't know how to create a report once they are linked. Can someone please direct me on how to do this? Or at least to somewhere that I can find out how to do ...

.pst question
Outlook/Exchange novice here.... I have a user that has archived files to different areas on his laptop. He wants them all to appear in his folder list under Archive Folders. If I copy and paste them into docs and settings\%username%\local settings\application data\microsoft\outlook do i need to rename the files so as not to overwrite the one archived file that is in there? In other words, what is the proper procedure to restore these archived files that are not where Outlook 2k sees them so that they can be viewed in his Archive Folders list? Thank you Hey, What I would do is put...

Reposting because this IMF question is really bugging me
I am using Exchange 2003 and have implemented IMF on my SBS 2003 server where Exchange and the SMTP gateway resides. Everything looks ok. I opted to have my suspected SPAM archieved to the archieve folder. When using the IMF Companion and viewing these suspected SPAM emails I've noticed one in there that I do not consider SPAM. I used the option to "unblock" this email. My questions are what happens to this email after I unblock it. Does it get forwarded to the addressee and does IMF assign a lower SCL value to allow it to pass next time. Thanks. I believe that the sender is ...

Import Excel to Access Question
Hi, I need some help on importing data from Excel to Access. The Excel spreadsheet has the same columns, i.e. Date, Description. The problem I am having is that each spreadsheet that will be imported will have some of the same engine numbers, and some new engine number information in the spreadsheet, which is first entered into the DB table. Can Access import different info into the spreadsheet each time if it's in the same table? For example, the table has info Date, Description, A, B, C. The spreadsheet has columns Date, Description, A, E, S. Also, when importing info from an Excel...