count results of formula, not text in formula

I would like to count the results of a formula, format is a mix of numbers and text.

       A        B      Formula in Column B is    
1      8    (8),(7)    ="("&A1&"),("&A3&")" 
2     10      (10)     =+A2, with custom formatting of (0) to show brackets
3      7    (8),(10)   ="("&A1&"),("&A2&")"  
4     14  
5      9  

There are numbers in Column A in rows 1 to 5, there are formulas in
Column B.  The actual formulas are the column I need to count the
requirements.  In this example, I need to count the occurrences of 8,
7, and 10.

Countif does not work as it looks for numbers or text not displayed values.
e.g.  COUNTIF(B1:B5,10) results in 1
       COUNTIF(B1:B5,"10") results in 1
       COUNTIF(B1:B5,"(10)") results in 0
       COUNTIF(B1:B5,"*(10)*") results in 1
       COUNTIF(B1:B5,"*10*") results in 1

There are actually two showing
0
bill.shut (7)
5/5/2004 8:22:56 PM
excel 39879 articles. 2 followers. Follow

4 Replies
971 Views

Similar Articles

[PageSpeed] 57

Hi
the format of your second row is not seen by COUNTIF. That is the value
in B2: (10) is internally stored as 10. So looking for (10) won't find
this value. So you have to use
=COUNTIF(B1:B5,"*(10)*")+COUNTIF(B1:B5,"10")

--
Regards
Frank Kabel
Frankfurt, Germany


Bill wrote:
> I would like to count the results of a formula, format is a mix of
> numbers and text.
>
>        A        B      Formula in Column B is
> 1      8    (8),(7)    ="("&A1&"),("&A3&")"
> 2     10      (10)     =+A2, with custom formatting of (0) to show
> brackets 3      7    (8),(10)   ="("&A1&"),("&A2&")"
> 4     14
> 5      9
>
> There are numbers in Column A in rows 1 to 5, there are formulas in
> Column B.  The actual formulas are the column I need to count the
> requirements.  In this example, I need to count the occurrences of 8,
> 7, and 10.
>
> Countif does not work as it looks for numbers or text not displayed
> values.
> e.g.  COUNTIF(B1:B5,10) results in 1
>        COUNTIF(B1:B5,"10") results in 1
>        COUNTIF(B1:B5,"(10)") results in 0
>        COUNTIF(B1:B5,"*(10)*") results in 1
>        COUNTIF(B1:B5,"*10*") results in 1
>
> There are actually two showing

0
frank.kabel (11126)
5/5/2004 9:08:32 PM
You didn't like my response from a couple of weeks ago?

http://groups.google.com/groups?threadm=408846F7.8AB59893%40msn.com



Bill wrote:
> 
> I would like to count the results of a formula, format is a mix of numbers and text.
> 
>        A        B      Formula in Column B is
> 1      8    (8),(7)    ="("&A1&"),("&A3&")"
> 2     10      (10)     =+A2, with custom formatting of (0) to show brackets
> 3      7    (8),(10)   ="("&A1&"),("&A2&")"
> 4     14
> 5      9
> 
> There are numbers in Column A in rows 1 to 5, there are formulas in
> Column B.  The actual formulas are the column I need to count the
> requirements.  In this example, I need to count the occurrences of 8,
> 7, and 10.
> 
> Countif does not work as it looks for numbers or text not displayed values.
> e.g.  COUNTIF(B1:B5,10) results in 1
>        COUNTIF(B1:B5,"10") results in 1
>        COUNTIF(B1:B5,"(10)") results in 0
>        COUNTIF(B1:B5,"*(10)*") results in 1
>        COUNTIF(B1:B5,"*10*") results in 1
> 
> There are actually two showing

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/6/2004 12:08:41 AM
Dave,

I did a search of the group and only found the first reply, which
didn't work, so I replied to it, and couldn't find your reply, only
the first one.  There must be a way to find subsequent posts, but I
missed out.

Dave Peterson <ec35720@msn.com> wrote in message news:<40998209.354D585@msn.com>...
> You didn't like my response from a couple of weeks ago?
> 
> http://groups.google.com/groups?threadm=408846F7.8AB59893%40msn.com
0
bill.shut (7)
5/8/2004 4:03:26 AM
My post showed up at the bottom of the thread when I clicked on this link:

http://groups.google.com/groups?threadm=408846F7.8AB59893%40msn.com

Here it is again:

Your formulas returned:

-- ------- -- ------------------------
 8 (8),(7)  0 =COUNTIF(B1:B5,10)      
10 (10)     0 =COUNTIF(B1:B5,"10")    
 7 (8),(10) 1 =COUNTIF(B1:B5,"(10)")  
14          2 =COUNTIF(B1:B5,"*(10)*")
 9          2 =COUNTIF(B1:B5,"*10*")  

When column B was formatted as text.  If I want to count the number of -10's (no
matter how they're formatted, I could use:

=SUMPRODUCT(--(B1:B5=-10))
(This ignores the text -10's.)

I think I'd use the sum of a couple of formulas if I wasn't sure what was in
those cells (and how they were formatted):

=COUNTIF(B1:B5,"*(10)*")+SUMPRODUCT(--(B1:B5=-10))

I used that formula when (10) was text and when it was -10 (formatted) and got 2
both times.


Bill wrote:
> 
> Dave,
> 
> I did a search of the group and only found the first reply, which
> didn't work, so I replied to it, and couldn't find your reply, only
> the first one.  There must be a way to find subsequent posts, but I
> missed out.
> 
> Dave Peterson <ec35720@msn.com> wrote in message news:<40998209.354D585@msn.com>...
> > You didn't like my response from a couple of weeks ago?
> >
> > http://groups.google.com/groups?threadm=408846F7.8AB59893%40msn.com

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/8/2004 11:56:21 AM
Reply:

Similar Artilces:

grade sheet formula
im a teenager trying to do a project for school on excel. i need to re-do my grade history sheet on excel. this is fine, i've done it all, and i'm fine on that part. where i'm lost at is the points part. the thing is for every class i have a grade. for every grade there are points (A=4, B=3, C=2, D=1, F=0). that and also, if one of my classes is honors gifted, a bit higher in level, you get an extra point (A=5, B=4, C=3, D=2, F=1). and if you're in an AP class, advanced placement, it's 2 more points (A=6, etc.). what i need to know is a formula so that whatever grade...

Pivot table and counting.
I have a pivot table that gives me the number of rides done by a van. I can have the pivot table return the number of rides, but what I need is the number vans that participated for a certain day. Example, it is possible to have 27 trips done by 18 vans, how can I count the number of vans and not addup the van number? Any help appreciated. Thank you, Joe Hi Joe We need a bit more information on how your source data table is set up. What information do you have in each column? -- Regards Roger Govier sacredarms <sacredarms@discussions.microsoft.com> wrote: > I have a pi...

Import text file from MS Word into MS Publisher.
I have created a text file in Word and a photo file in Publisher and would like to merge the two. When I imported Word, it seemed to be totally separate from the Publisher document - 7 pages of Word and 8 pages of photos. I want the Word pages to precede the Publisher pages. How is this done? >-----Original Message----- >I have created a text file in Word and a photo file in Publisher and would >like to merge the two. When I imported Word, it seemed to be totally >separate from the Publisher document - 7 pages of Word and 8 pages of photos. > >I want the Word pages ...

Cycle Counting
Hello, I am looking for ideas on how to cycle count serial number tracked items without increasing workload. Our serialized items are barcoded but the problem is the are situated on the pallet incorrectly and the pallet is placed on a third level of a racking system. If we could just cycle count based on item count and not include the serial number it would make it easy. Is this possible in Great Plains. We are running Great Plains 8.0. Any suggestions are greatly aprreciated. Thank You, Brian Morris Database Administrator ...

Formulas don't work in certain cells #3
One formula is: =SUM(C10:C15) another: =SUM(O16/6363) another: =SUM(C19:C36) these same formulas work in the cells right next to these wit different cell references. However, no matter which cell I put th above formulas in, the result is the same - either blank with a dash i the cell or zero. I checked all the cells that the formulas refer t and none has any errors. -Jorda -- kalik24 ----------------------------------------------------------------------- kalik247's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1594 View...

Adding Text to Charts
I have created a chart in Excel that tracks one of the stock indexes. I have been able to point arrows to specific days and add text so I know what was the reason the stock index was up or down on that day. However, as my chart expands the arrows and text are no longer associated with those day I initially intended it for. Is there any way in Excel to associate text with specific day and have it attached so that if the chart grows or shrinks it will always be associated with that particular day. Thanks Hi Roy, The trick is to group the graphic element and the chart. When adding a graph...

Count
Hi, I would like to know how which formula I should use to count some codes & to ignore the duplicated codes, for example in column A: Code 1234 4321 4321 1234 1234 3214 Now, I want Excel to count & the result is 3 which are: 1234-4321-3214 Yes I can use PivotTable, but I would like to use a formula because I have to deal with this every day. I appreciate your support. -- MFS22 COUNTIF will do it. =COUNTIF(Rng,Rng), where the criterion is the same as the range: e.g =COUNTIF(A3:A8,A3:A8) HTH Peter "MFS" wrote: > Hi, > I wo...

configure text box
I recently upgraded to Publisher 2003. I can't find how to configure text boxes to overlap each other and keep the text in the same position. In my old version, there was a "check" box in the format text box to allow for this. I've tried different wrapping options in 2003, but can't figure it out. You never have been able to do this in any version of Publisher. You are dreaming about a Text Box - never existed. You either have to place one Text Frame on the Background (Ctrl+M) or convert one to a graphic. -- "If you don't know where you are going, any road...

How do I count the # of times a value reoccurs and plot it over ti
I wish to plot a number of errors made by each users and compare it over time. I have a column of user ID's and a column of dates. I want the chart to count the number of times each user ID appears and plot against it's corresponding date. However, each time I try to do this, the chart appears wrong. Any ideas? In article <9595B2A1-8282-4AB3-8511-A6776E108164@microsoft.com>, Stephen@discussions.microsoft.com says... > I wish to plot a number of errors made by each users and compare it over > time. I have a column of user ID's and a column of dates. I want t...

Text to Columns 06-01-10
Hi. I have a column where I have names and surnames. I want to put name in one column and surname in anoter column. In order to do this, I use Text to Columns, but I have the following issue: if the cell contains for example Juan Pérez, it works ok, but when the cell contains Juan De los Santos, it assumes there are three surnames, and then put me one word in each column. This is the result, with the example mentioned: A B C D Juan Pérez Juan De los Santos How can I indicate that...

count number of characters in rich edit control
How can i count number of characters in rich edit control 2 If u mean controls of type Rich TextBox control, try RichControl1 RC1; CString Mes=RC.GetText(); int n=Mes.GetLength(); "Rahul" <Rahul@discussions.microsoft.com> wrote in message news:7743023C-EA83-4ACA-AB67-F1AE7279F1EB@microsoft.com... > How can i count number of characters in rich edit control 2 EM_GETTEXTLENGTHEX, WM_GETTEXTLENGTH, WM_GETTEXT, EM_STREAM* "Rahul" <Rahul@discussions.microsoft.com> wrote in message news:7743023C-EA83-4ACA-AB67-F1AE7279F1EB@microsoft.com... > How can i co...

not calculating formulas
in some excel sheets, after a while it stops calculating formulas. if i copy the hole sheet and paste it into another, everything works fine. Any chance that on the: tools|options|Calculation tab| calculation was set to manual? doros wrote: > > in some excel sheets, after a while it stops calculating formulas. if i copy > the hole sheet and paste it into another, everything works fine. -- Dave Peterson No. I have allready checked that. "Dave Peterson" wrote: > Any chance that on the: > tools|options|Calculation tab| > calculation was set to manual? > &...

Simple...Update Text Box in Chart
Hello, I have a chart in an MS Excel worksheet and I would like to change the value/caption of a textbox( Text Box 20). I have only been able to change the title of the chart with the following: With MyChart.Workbooks(1).Worksheets("Chart").ChartObjects(1).Chart .HasTitle = True .ChartTitle.Text = "Summary of Report" & date() 'Add Today's Date End With Do I have to reference the chart or do I have to reference the textbox itself? Hi, If the shape is actual embedded in the chart object then something like, ActiveChart.Shapes(&quo...

Line count
I am working with a program called Dictaphone. We use Word 2007 in this program. The line count on Dictaphone and the line count in Word 2007 are different even though the Dictaphone text is retrieved from Word 2007. Why would they be different? What is the difference between virutal line count and physical line count? ...

Is there any way to use longer strings in formulas?
In Excel 2002, the longest string I can use in a formula is (to my mind) ludicrously short. I have a workbook that requires HUNDREDS of English sentences like: ="Just to let y'all know, thanks to all the swell work by our sales staff in 2007, and despite our having to fire half of them for insubordination, and despite our chairman's having run off to a region in Asia which we believe is officially called the Glorious Former Soviet Autonomous People's Provisional Revolutionary Socialist Republic of Trans-Caucasian Equitorial Guinea, our company's annual return on investme...

Counting sales
I have two different datasets. Each with a common identifier. I need a formula that will reference the identifiers in both datasets and count the number of sales a particular agent made. Example. look up this identifier in the other dataset and then give me the value that is 2 columns over from the identifier in the second dataset. See if this helps: http://contextures.com/xlFunctions02.html -- Biff Microsoft Excel MVP "Dave" <Dave@discussions.microsoft.com> wrote in message news:E7245B37-033F-400E-9CEE-9822BE7CB0F5@microsoft.com... >I have two...

Pasting text from Word 2008 into dreamweaver
It doesn't work. This is a major problem and jams my workflow. Yes I can drag text from word to DW or I can paste in code view but this kills all formatting. This makes 2008 a definite no no for purchase as far as I am concerned and I am sure many other people.<br> <br> K <kokney@officeformac.com> wrote: > It doesn't work. This is a major problem and jams my workflow. I remember other people posting about this in the group. As I remember it, Office places multiple formats fo the text in the clipboard and it's up to DW to decide which one to take. Apparentl...

How to size static text box?
I've got a relatively simple dialog that includes some static text along the border containing a mini-help message. On my two systems here with Win2K and WinXP at both 1200x1024 and 1600x1200 resolution the text displays fine. At another location with both WinNT4.0 at 1024x768 and Win2K at 1200x1024 part of the text is cut off because the box is too small for it. It's possible, I think, that the problem isn't the resolution of the displays but the size of the "standard text" font, but I don't want to start changing a dozen different desktop fonts until I find which ...

Counting Cells with Conditional Formatting
Is there a way to look at a row of data that is either highlighted yellow or red and to count only the data that is highlighted yellow and give me that number? None of my data is numerical....just one letter data, i.e.: "S" or "U" I just want to count how many of the cells are highlighted yellow in a specific row, if that makes more sense. Here's a UDF (Put in a Standard module) and use it in cell A1, like =CountByColor(A8:H8,6) << where 6 (at present) = Yellow = change to =CountByColor(A8:H8,3) to get the reds HTH Function CountByColor(InRa...

COUNT ? need formula
Hi, I want to track the results of my teams sales performance. I record if it is a sale, no sale, cancelled etc in column M. I thought I'd be able to use the COUNT function/formula to be able to search for all the SALES in column M and place the result in a cell ( column O )but I can't work it out. Any ideas? Damian Hi! Try this: =COUNTIF(M1:M100,"sale") Biff "Diamond Jones" <kwanzaNOSPAM@optusnet.com.au> wrote in message news:43a0e897$0$17704$afc38c87@news.optusnet.com.au... > Hi, > > I want to track the results of my teams sales perf...

Count Function
I am creating a report for the number of pupils being collected at a certain collection time e.g. 14:00hr and 15:00hrs using the formula =Count([Child Last Name]) in the collection time footer However when i try to count the overall number of pupils in the footer using the same formula, 'Error' appears on screen? Thanks There are potentially a dozen footer sections in a report. Which one is giving you a problem? What is the name of the control? What is the exact formula? -- Duane Hookom Microsoft Access MVP "ELo" wrote: > I am creating a r...

disappearing text
Version: 2008 I am working on a word Project Gallery and one page in the doc. has text that is not recognized in the toolbox and it disappears as I scroll down the doc. I can't seem to locate it to edit or wrap around a graphic. I need help ASAP because i have have it done by monday. help please. bob What do you mean "working on a word Project Gallery"? The Project Gallery is a setup of starter templates. Are you working on a set of starter templates, or are you writing a document based on one of those templates? What do you mean "text not recognised"? ...

Help to modify a formula
This is my formula I need help with, in cell I10 this is entered =IF((I7-I6)*24>4,(I7-I6)*24-0.5,(I7-I6)*24) Problem is when cell I6 & I7 are blank cell I10 shows #value I need it to be blank or 0 any ideas? thanks all. Bob Bob, You should be getting zero when I6 and I7 are empty. It sounds as if there's a space or something like that in the cell. Some folks do that to clear a cell. -- Regards from Virginia Beach, Earl Kiosterud www.smokeyl.com ----------------------------------------------------------------------- "Bob Bob" <NOeMAILl@mail.com> wrote in me...

Need help on this formula!
Hello, I need help with a formula I'm trying to create. I have data in column but the data is scattered. For example, there will be a numbe in row 20 then it's blank until row 35, then blank until row 68, and s on. I need a formula that will copy the data to the rows that are blan below it. For example, if row 20 has a number in it I want to cop that number in the blank rows until it reaches the next number. An once it reaches row 35 it will copy that number to the rows below i and so on. I would do this manually but the spreadsheet is huge. Thank you for your help, Olimits...

Help with Formula 12-07-09
Hi, I posted this yesterday and did not get much response. I wonder if it cannot be done. Any way please read and help me with a formula. Thank you. Ok. I will use a better example. Let's say my sheet 1 shows car sales like Honda, Ford, etc. The rows are months like Jan, Feb, etc. and the columns are dates (1-30 or 31). Of course there are many empty cells because there are dates that I did not sell any car. (To keep it simple, I sell only one car if there is any sale on a day. If I sell Honda, I put "Honda" in my cell, and Ford, "Ford," etc.) The sheet ...