COUNTIF Problems

Hi,

I have the following formula:

=IF(ISERROR(COUNTIF(StatusCheck, "Snag Item")/COUNTIF(ElementCount,"Main 
Contractor - Builderswork")),0,COUNTIF(StatusCheck, "Snag 
Item")/COUNTIF(ElementCount,"Main Contractor - Builderswork"))

Originally I had some problems getting ISERROR to work properly - but this 
is sorted now.

The formula is supposed to count the number of times a snagging item is 
added into the spreadsheet (each snagging item has its own row) that is 
assigned a status of "Snag Item" and an Element of "Main -Contractor - 
Builderswork". The formula works fine for one item - but if two or more are 
added the formula returns 1. 

Any ideas why this is not working.?

Cheers,

Phil
0
PW11111 (15)
7/25/2005 10:06:04 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
664 Views

Similar Articles

[PageSpeed] 35

What do you mean by "two or more are added"?

I am guessing you were getting an error because the formula was dividing by 
zero. You could check for zero instead of duplicating the entire formula. 
Not a big deal unless you have a large file with many formula.

Try:

 =IF(COUNTIF(ElementCount,"Main  Contractor - 
Builderswork")<>0,COUNTIF(StatusCheck, "Snag 
Item")/COUNTIF(ElementCount,"Main Contractor - Builderswork"))


-- 
Damon Longworth

Don't miss out on the 2005 Excel User Conference
Sept 16th and 17th
Stockyards Hotel - Ft. Worth, Texas
www.ExcelUserConference.com


"PW11111" <PW11111@discussions.microsoft.com> wrote in message 
news:250D7A36-C816-4B2A-A97B-C4CDBFD54854@microsoft.com...
>
> Hi,
>
> I have the following formula:
>
> =IF(ISERROR(COUNTIF(StatusCheck, "Snag Item")/COUNTIF(ElementCount,"Main
> Contractor - Builderswork")),0,COUNTIF(StatusCheck, "Snag
> Item")/COUNTIF(ElementCount,"Main Contractor - Builderswork"))
>
> Originally I had some problems getting ISERROR to work properly - but this
> is sorted now.
>
> The formula is supposed to count the number of times a snagging item is
> added into the spreadsheet (each snagging item has its own row) that is
> assigned a status of "Snag Item" and an Element of "Main -Contractor -
> Builderswork". The formula works fine for one item - but if two or more 
> are
> added the formula returns 1.
>
> Any ideas why this is not working.?
>
> Cheers,
>
> Phil 


0
dlongwth (59)
7/25/2005 11:09:56 AM
Reply:

Similar Artilces:

IF, COUNTIF
Can someone explain why these two expressions differ referring to the value 5? IF(A2:A10<5 etc. ) but COUNTIF(A2:A10,"<5") requires a comma and quotes Wouldn't it make sense for them to be the same? Just wondering. For us it would seem to make sense, but for the computer not so much :-) My understanding is that in the IF statement the <5 is a Comparison Operation which is a part of the 1st Function Argument whereas in the COUNTIF it is a Criteria Reference supplied as the 2nd independent Function Argument. If it stood alone as simply <5 it would ...

printer problems
white strips down my printed document do i need to clean the contacts shawna That's not a Word issue. Did you try cleaning it? -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "sweetie" <sweetie@discussions.microsoft.com> wrote in message news:129A1B76-9D02-48AE-AB3F-7EBDD6D4DCA2@microsoft.com... > white strips down my printed document do i need to clean the contacts > > > > shawna ...

ArrayList memory problem
Hi everyone, I've got the following problem: In my application I make use of two ArrayList objects like this: Public Sub Main() LoadData() End Sub Public Function LoadData() Dim list1 as new ArrayList() [...Fill the arraylist...] AnalyzeData(list1) End Function Public Function AnalyzeData(list1 as ArrayList) Dim list2 as new ArrayList list2 = list1 [...do stuff...] list2.Clear() End Function Now, this code creates a major memory leak....calling GC.Collect() etc. won't help. Can anyone tell me what happens, when I call list2 = list1 - is the whole list co...

Problem : SHGetPathFromIDList with CSIDL_BITBUCKET
Hi to All, First see my code : LPSHELLFOLDER pDesktop = NULL; LPITEMIDLIST pidl = NULL; HRESULT hr = S_OK; char pBuff[MAX_PATH]; hr = SHGetDesktopFolder(&pDesktop); hr = SHGetSpecialFolderLocation (m_hWnd, CSIDL_BITBUCKET, &pidl); ::SHGetPathFromIDList(pidl, pBuff); My Question is : The value of pBuff comes "" in case of CSIDL_BITBUCKET (Recycle Bin Folder)) and "E:\Documents and Settings\Allen\Desktop" in case of CSIDL_DESKTOP (Desktop Folder) Why pBuff comes blank("") with Recycle Bin Folder ? Regards, Allen On 28 Jun 2005 23:20:07 -0700,...

Problem solved!
I think I've discovered the culprit behind my problems. As I've mentioned, I didn't know that I had to use the Remove Office app to remove the "test drive" version of Office that came with my G5. I just trashed the folder the old-fashioned way. After several posters told me to use the Remove Office app, I ran the app off of the installer CD. The app found the Office parts and supposedly removed them. However, I still couldn't get the Office apps to stop crashing upon launch. Finally, it dawned on me to actually install the Remove Office app on my hard drive and then...

CountIf Statement
Can anybody help? I am creating a formula in a cell of a spreadsheet which will Count all in stances of "distribution centre" within a column This is achieved using the =COUNTIF('calls closesdon 3 - 4 Dec'!G4:G14, "GROUP") I was wondering whether it was possible in excel to use a statemen like =COUNTIF('calls closesdon 3 - 4 Dec'!G4:G14, "GROUP") and (nex expression) any ideas -- Message posted from http://www.ExcelForum.com You can use sumproduct to count with more than one condition multiple ranges =SUMPRODUCT(--(Range1="GROUP&...

Encoding problem
Hi, I've met a problem with Outlook 2003. When I receive message from inernet, containing list of recipients and display name of one of them is in UTF-8, and subject and message body is encoded in other code page (in my case KOI-8R), Outlook 2003 displays correctly list of recipients, subject, but message body it shows in UTF-8 (as in recepients list) Outlook 2003, in Excange 2003 environment, latest patches applied. Any ideas? ...

Windows 7 and Access 2007 ODBC Connection problem
I just created a Windows 7 PC (Enterprise Eddition) and have Office 2007 Pro. Both are fully patched. I have an access application that works on an XP PC but fails on the Windows 7 PC. Both PCs have an ODBC connection and both past the Connection Test. However, when I opend the application on the Windows 7 PC it fails with an: ODBC - connection to 'Calltrack_Access' failed. error. Not sure what would cause this. I've given the file location full access rights in Access including subfolders. Is it a Windows 7 permissions thing???? that sounds great "...

Internationalization over COM problem
Hello All, I have a problem when I load an Excel 2007 (.xlsx) file into Excel 2007 over the COM interface. The file was saved with an Italian version of Excel. I am using Excel.Workbooks.Open to load the file into the same (Italian, same machine, same user), version of excel. The problem is that a number of settings in the file are lost when the file is loaded this way. If I start Excel from the desktop and load the file from the menu the settings are maintained. I've written a trivial C# application (code follows signature) that uses COM to load the file, and the settings are lo...

problem #5
I have a problem trying to backup Outlook 2000 on a XP sys. I create an export pst file and it is saved, but when I try to retrieve this file it says the file does not exist. I want to make a CD backup to protect all the addresses and contacts. callwalt(removethis)@oro.net walt <anonymous@discussions.microsoft.com> wrote: > I have a problem trying to backup Outlook 2000 on a XP sys. > I create an export pst file and it is saved, but when I > try to retrieve this file it says the file does not exist. > I want to make a CD backup to protect all the addresses > and contac...

OpenArgs problem
I have an unbound form with a listbox with the following settings: Row Source Type = Table/Query Row Source = SELECT DISTINCT Code FROM [TEST Master] WHERE Code>=0 ORDER BY Code; Bound column 1 Multiselect is Simple The field value for code in the table is number. The user selects the codes they want from the list box and then I want to open the report rptTesting displaying only the data for the code numbers selected in the list box. Private Sub cmdListboxOfQueries_Click() Dim intCount As Integer Dim varItem As Dim intLength As Integer 'To determine length of...

Begineer with (seemingly) hard problem
Hi Guys, Can someone please help me out with this problem. In the spreadsheet there are 2 main colums I need to count. I have a set of results tables on the right that are lacking results :) So in Q5 I need a function that checks that the subject is a male, and they have Aspergers... count 1, then goto the next row and keep counting down the entire spreadsheet. Then in S5, obviously count all the females with Aspergers etc. etc..... I know it is probably easy and I should learn myself but if someone can tell me the first function or 2 that'd be fantastic. I just don't have time ...

OWA email problem
I have one OWA user, he can access & login OWA fine, but the moment he checks and opens email that email is missing again when he logs off next time. No idea what is the problem.Everything is working fine for all other users. Also his email is working fine when he access mails from outlook 2003. This problem happens only in OWA.PLEASE HELP. Check and see if the user has his Inbox View set to Unread messages. In OWA the "View by" drop down list should be right above the delete, reply and reply all icons. In order to see all of your messages, it should be changed to Messages....

Problems inserting rows
How can I insert a row in Excel when the assistant says it's not possible? The assistant says that inserting rows would shift data off the worksheet, rather into the next blank rows Maybe... XL: "Cannot Shift Objects Off Sheet" Error Hiding Columns http://support.microsoft.com/default.aspx?scid=kb;en-ca;211769 Remember to look for comments and merged cells. KathleenK wrote: > > How can I insert a row in Excel when the assistant says it's not possible? > The assistant says that inserting rows would shift data off the worksheet, > rather into the next blan...

Tab order problem
I have a dialog box (MAIN) with two buttons( Ok ,Cancel) and a tab control. In the tab control I have two tabs with corresponding pages.(page style->child) One of the pages contains three text boxes. My problem is that the tab order runs within the page only and does not go to the Ok button. Similarly, in the dialog box(MAIN), the tab order is omitting the tab control.The focus is between 'Ok' and 'Cancel' only. How to make this work? Thank you... You have to set the tab control style as: MyTabControl.ModifyStyleEx(0, WS_EX_CONTROLPARENT); You also need to modify the ...

CRM Problems
1. Bulk Email (Broadcasting) - We are unable to send a broadcast to multiple users - this must be done off a template, meaning that a new template will have to be created for each broadcast. 1a.This can also be done by using a mail merge, however, we can only mail merge to 100 contacts at a time. We are broadcasting to 700+. See point 2 also. 2. Word to CRM integration - When mail merging into word for various letters, an activity is not recorded against the user. 3. How do emails get into CRM? Is it possible to put all emails that come in, into CRM with their associated contact. T...

Problems sync to MSN
I have my MSMoney 2004 Deluxe set up properly to sync to MSN. After sync with MSN, I continually have two accounts that show up with entries to be acknowledged. The same few transactions show up every time as new, regardless of how many times I 'Accept' them in MSMoney and resync to MSN. Additionally, one of these is a duplicate - shows up twice when reviewing the account details in MSN, shows up once on the provider's site. I've updated the account in MSN, same results. Not a real problem, but annoying enough that I might turn off MSN Sync...I get all the MSN-bas...

Countif ...
I have a column with phone numbers. I want to count how many phone numbers have the area code "214" and "972". Thanks, the first formula worked. "Jason Morin" wrote: > The formula depends on how your phone numbers are formatted and whether they > are text or actual 10 digit numbers. For example, if they are text and you > simply need the first 3 numbers in the cell, try: > > =SUMPRODUCT(--(LEFT(A1:A10,3)={"214","972"})) > > If the area codes are enclosed in parentheses, you could use: > > =SUM(COUNTIF(A1:A10...

Problem using problem reporting
I noticed that windows 7 error reporting no longer works on my machine. It goes through the motions then reports it cannot connect to the error reporting service at this time, try again later. I have tried many times, same results each day. Any clue as to what might be wrong? Anyone heard of this error or know of a fix I'm using Windows 7 64 bit. Seen it reported a few times. The cause has worked out to one of three: 1.) Third party firewall 2.) Horked DNS 3.) Serious infection. Unfortunately, the third is the most common I've seen. -- Charlie. http://msmvps....

Outlook Attachment problem
Hi, I have windows 2000 exchange server with service pack 3 and with all the hotfixes, some of our clients complaigning that thes attachments are corrupted when they receive emails from us , it works perfect some times and some times we need to resend the email to them which is very irritating to client and our users, Please suggest Any AV/Content Filtering/Gateways between your mail system and the external world? I would look there if its indeed happening on your end. On Thu, 3 Feb 2005 13:23:02 -0800, "Deeps" <Deeps@discussions.microsoft.com> wrote: >Hi, >...

help with crazy problem
Hi, I'm building an application using MFC in Visual Studio 2003 on an XP machine. I am building the executable to use MFC as a static library. I use API calls somewhat liberally in the app. When I copy the app over to a Win98SE test system and attempt to run it, a call to CreateFile fails with the error code 87 (ERROR_INVALID_PARAMETER). I have tried every combination of flags that I can think of, and it still fails. I put Visual C++ 6.0 on the 98SE test system (wouldn't let me put VS2003 on there), and created a test app and I'm hard pressed to find a combination of flag...

Outlook Problems #9
I have my computer at home set to also receive my e-mails from work and my computer at work set to also receive my e-mails from my home. However, if I try to send something to myself sometimes it goes and sometimes it does not (from either computer). Do you have any suggestions how I can enter the settings so that I make sure I receive my e-mails. Also, if a third party sends an e-mail to my home and it also comes to work and I open the one at work first it does not appear on the outlook at home. I have earthlink and if I put "leave message on server" then it leaves it ...

Criteria/CountIf and Pivot Table
Hello: My data looks like this ID Date CWA Amount 1 08/12/2006 0 $0.00 2 08/13/2006 1 $10.00 3 08/14/2006 1 $20.00 4 08/01/2006 0 $0.00 Based on the above data, I want to create a pivot, by month (I know I only have Aug here) to show the following: Sum of CWA Count of CWA Percent of cases that were submitted with CWA. The CWA is an indicator field that looks at another field (not listed here) to determine if there is cash in a...

Log on problem
IE 8 will not remember my sign in information. What do I do so I don't have to sign in each time? kit kit1109;156866 Wrote: > IE 8 will not remember my sign in information. What do I do so I don't > have > to sign in each time? > kit Check to see if AutoComplete is turned on. Click Tools --> Internet Options --> Content Tab (from the tabs across the top) --> and Settings for AutoComplete AutoComplete feature lets you more quickly fill out web forms and navigate to websites you have visited in the past. AutoComplete collects and stores in th...

Global Address List Problem
I am using WindowsXP pro and Office 2003 pro. I am having issues sending mail to one of the contacts on my GAL as I get undeliverable message whenever I try to send mail to them. When I look at the properties for this recipient, I see the following in the display field: /cn=***/OU=FIRST ADMINISTRATIVE GROUP/CN=RECIPIENTS/CN=***, on the email tab there is no address listed. This is not the case with any of the other recipients in the GAL. This is causing me problems because even if I type in the full email address of the person in the 'To' field, it still comes up with the undeli...