#### How to count cells

```Hi,
I want to put a summary of Job orders in a table. (Excel (Zip) file
attached)
I am having a problem of total.
I want to make a total of only approved job orders not the all job
orders.
Also if the column contains hot / cold type, I need red and bold for
hot job order of complete row.
Also I want to be counted by job type and entity. For example, in the
total approved 7 permits, CONST entity 3 cold jobs and 1 hot job. how
can i display in the abstract table.

+-------------------------------------------------------------------+
|Filename: count.zip                                                |
+-------------------------------------------------------------------+

--
vsr_kmb
------------------------------------------------------------------------
vsr_kmb's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32834

```
 0
3/27/2006 4:04:17 AM
excel.newusers 15348 articles. 2 followers.

3 Replies
430 Views

Similar Articles

[PageSpeed] 52

```One play ..

We're going to use formulas which read straight off
the summary table labels in D21:D25, and in E20:F20

Reference sheet: X in the sample construct at:
http://cjoint.com/?dBhWdb7LSU
vsr_kmb_count.xls

To ensure that the entities listed in D21:D25 would
be consistent with the actuals within the col "Entity",
it's best just to use your DV list within D21:D25.
(This has been done in the sample)

Similarly, we'll also change the labels in E20:F20 to read as just:  H, C

Put in E21:
=SUMPRODUCT((\$G\$7:\$G\$16<>"")*(\$B\$7:\$B\$16=E\$20)*(\$D\$7:\$D\$16=\$D21))
Copy across to F21, fill down to F25 to populate

Then just change G21:G25 to compute the horizontal totals
Put in G21, copy to G25: =SUM(E21:F21)

The above should return the correct figures within the summary table

(Think there were a few inconsistencies described in your post
versus what was in your actual source table)

------------------
> Also if the column contains hot / cold type, I need red and bold for
> hot job order of complete row.

Reference sheet: X (2) in the sample

To conditionally format lines
within the source table (red/bold font)
if the type is "H"

(Remove the current cell formatting so that
we can see the CF formatting work)

Select A7:G16
Click Format > Cond Formatting
Formula is: =\$B7="H"
Click Format button > Font tab > Red/bold > OK
Click OK at the main dialog
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"vsr_kmb" <vsr_kmb.25b8mo_1143432606.344@excelforum-nospam.com> wrote in
message news:vsr_kmb.25b8mo_1143432606.344@excelforum-nospam.com...
>
> Hi,
> I want to put a summary of Job orders in a table. (Excel (Zip) file
> attached)
> I am having a problem of total.
> I want to make a total of only approved job orders not the all job
> orders.
> Also if the column contains hot / cold type, I need red and bold for
> hot job order of complete row.
> Also I want to be counted by job type and entity. For example, in the
> total approved 7 permits, CONST entity 3 cold jobs and 1 hot job. how
> can i display in the abstract table.
> Can anyone help me please.
>
>
> +-------------------------------------------------------------------+
> |Filename: count.zip                                                |
> +-------------------------------------------------------------------+
>
> --
> vsr_kmb
> ------------------------------------------------------------------------
> vsr_kmb's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=32834
>

```
 0
demechanik (4694)
3/27/2006 5:44:41 AM
```Thanks very much and it is excellent.

Thanks a lot.

Regards,
Ravi

--
vsr_kmb
------------------------------------------------------------------------
vsr_kmb's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32834

```
 0
3/27/2006 6:53:11 AM
```You're welcome, Ravi !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"vsr_kmb" <vsr_kmb.25bg9n_1143442504.8625@excelforum-nospam.com> wrote in
message news:vsr_kmb.25bg9n_1143442504.8625@excelforum-nospam.com...
>
> Thanks very much and it is excellent.
>
> Thanks a lot.
>
> Regards,
> Ravi
>
>
> --
> vsr_kmb
> ------------------------------------------------------------------------
> vsr_kmb's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=32834
>

```
 0
demechanik (4694)
3/27/2006 8:21:30 AM

Similar Artilces:

The Sum from 1 worksheet cell to another worksheet cell
the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula qwerty: To sum the value on Sheet1, cell A10 with the cell value Sheet2, cell B20, enter =Sheet1!A10 + Sheet2!B20 (or you can enter '=' sign and click on A10, then enter the plus sign and click on B20) jeff >-----Original Message----- >the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula >. > ...

what is the function and name is of the symbol in each table cell.
Under Paragraph I clicked the Show/Hide Symbol icon so I can now see a symbol at the end of each text within a table cell. I wondered what that is so I tried to use Help to find out. I did find help that mapped a word (like paragraph) into a symbol. But I can't find anywhere where if I know the symbol it will tell me the meaning. Can you tell me how to find such info? Or maybe you can tell me what the function and name is of the symbol in each table cell. Thanks I'm sorry, I meant to sent this to the Word group. Of course, I wouldn't mind getting the info...

Need Formula To Find Blank and NonBlank Cells
I have a worksheet with 6 columns (by Month) Sep Aug Jul Jun May Apr I have to review starting for example with May, I need to find any cell in May range that is null <> where Jun and Apr both are not null <> So if May is null and Jun and Apr are not null than I would count that as 1. If May is null and either Jun or Apr are null then I would not count them. =SUMPRODUCT(N(E2:E100=""),N(D2:D100<>""),N(F2:F100<>"")) "hilltop55" <hilltop55@discussions.microsoft.com> wrote in message news:08D989CB-D1B4-49F...

Need Syntax for "AND" to Evaluate 2 Cells
I need to evaluate 2 cells while inside an "Private Sub Worksheet_SelectionChange(ByVal Target As Range)". I thought AND would work but I cannot get it to work; I receive a syntax error on the AND(Range... line. Can someone please provide me the proper syntax to evaluate the 2 cells? Here's my code... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If ActiveSheet.Name = "Sheet1" Then And(Range("I3") <> "", Range("K4") = "") Then Range("K4") = Range("K3") End...

Enter "1", cell show ".01". Why?
Any number typed into a cell is divided by 100. If proceded by "=" the number is correct. What caused this and how can I fix it? Try this .. Click Tools > Options > Edit tab Uncheck "Fixed decimal" > OK Things should be back to normal now .. (it's a fixed decimal setting !) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Yonian" <Yonian@discussions.microsoft.com> wrote in message news:40499CA4-7FAF-42A6-8B19-A90881735C50@microsoft.com... > Any number typed into a cell is divided by 100. > If p...

selecting a cell
I seem unable to select a single cell, or a single row--click on one in the normal manner, and the two below also highlight, then delete or whatever command is given. If I input a number/text, that just goes into the one cell. tapping F8 increases this to two wide and three high automatically selected. Also, very slow to do almost anything. Thanks Pat, Are you by any chance using Excel 2007? If so there is a known bug that causes multiple cell selection and I understand this has been reported to Microsoft. If you take the zoom level up and down this is reported to cl...

Word 2007: word count wrong?
Hello, I have an issue with some .doc files when opening in Word 2007. In some cases the word count in the status bar is different of the word count of the "Word Count"- window (CTRL+SHIFT+G) or the word count in Word 2003 Example file: http://go.microsoft.com/fwlink/?LinkId=79595 Word 2007 (status bar) show 61019 words Word 2007 (Word count window) show 61010 words Word 2003 also show 61010 words This issue I have not with all documents, but only with some files and it seems that I have this problem only with .doc files but not with .docx files. (installed ve...

cell will not center
Hi. I have a user with an Excel worksheet. There are multiple rows and columns and they are all set on center alignment, (center alignment icon on the toolbar as well as Format Cells --> Horizontal Alignment --> Center.) The alphabetical characters align correctly but the numerical don't, as they will only left align. Format Cells --> Number is set to General, so I don't know why it won't change the alignment. Other than the worksheet being corrupted, I don't know what could be wrong with it. Any suggestions are much appreciated. Thanks! Hilary =?Utf-8?B?SG...

Simple Access counting queries
Hi, hoping someone can help a relative newbie with a pretty simple query. My database (Access 2007) has three tables: Customers Products Purchases (many-to-one links to both of the other tables, this is basically a linking table) I have two simple queries I'd like to get out of this database, but I'm a bit stuck on how to construct the SQL. Any direction you can give me would be helpful. 1. List of all customers who have purchased 2 or more products (or 3 or more products, or 4+, etc.) 2. List of all customers who have purchased both Product A and Product B (or A, B, and C, or B an...

Count # of cells b/w cells ...
Hello, I have the following data in a column: 7 0 0 0 7 0 0 0 0 0 7 0 0 7 0 0 0 0 0 0 7 etc. The number of zero's between the 7's is random. I want a formula tha would count the number of zeros between the 7's. Thanks, Ari Bar -- AriBar ----------------------------------------------------------------------- AriBari's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2504 View this thread: http://www.excelforum.com/showthread.php?threadid=38806 Assume A5:A20 is the data, try this: B5 = A5+B4 (copy formula down) Now make a table with 2 column...

Too many different cell formats #6
I am running into the error message: Too many different cell formats Is there a solution to lowering the number of formats I am using? Just trying to change them to make some consistent gives me the same error message. I tried running the search on the forums on my topic but they have been disabled for a Microsoft upgrade. Thanks! One idea - Rob Bovey's excellent Utilities add-in will list all the formats in use in your workbook, allowing you to manually delete what isn't being used. http://www.appspro.com/Utilities/ExcelUtilities.htm You can also see the source code for ...

format cell #4
In Access, I can set up a field that "forces" the user to enter info - a date, for example - in a certain way, such as 25 Jan 05 or enter time as 12:15 AM. Is there a way that I can "force" this in excel? Thank you. Hello- Without invoking something more technical, you can select the cell(s) and go to Data>Validation and choose what type of entry be allowed in the field. Format the cell in the manner you wish to have the date or time expressed. HTH |:>) "HJC" wrote: > In Access, I can set up a field that "forces" the user to enter in...

count if a match occurs
Hi! I have two rows - say Row 1 and Row 2. In the first row I have a answer key. In the second row I have answers from a student. I would like to write a formula where it counts how many answers student got write. Here is a example: Row 0: Q1 Q2 Q3 Q4 Q5 Q6 Row 1: 1 4 2 3 4 1 Row 2: 3 4 1 3 4 1 So student marked 4 questions correctly: Q2, Q4, Q5 and Q6. Hence, the formula should return 4. I know one way to do it but I am sure there is more efficient way to do it. The way I know: Use if statement to compare two corresponding entries and output 1 if...

Formatting cells and getting pound signs
I am using Excel 2003 with all updates as of 4/28/04 and trying to format a cell using the custom category and choosing the #,##0.00 type. I am trying to add the \$ symbol at the beginning of the type and add text at the end of the type to look like this \$#,##0.00 "text". When I do this however it shows up in my cell on my worksheet as ##########. It does know what the value is and shows as I would expect it to when I place mouse over cell in a balloon If I use only the \$ symbol befor the type it shows fine. If I use only the "text" after the type is shows fine. Using the...

Find and replace with bold in cells
I have a VB6 program that is executing Excel 2007, opening a worksheet, and extracting some of the cells to write data to a text file. Some of the cells contain bold text on some (not necessarily all) of the text in the cell. I would like to do a find and replace on the bold tagging to replace it with something like "<b>" at the start of it and "</b>" at the end of it. How do I set this up in VB6? Thanks! The following function will return a string including <b> and </b> tags from the text of cell R. Function BoldMarkup(R As Range) As...

Selecting cell value for a sum, based on a condition
Trying to come up with a formula or method that will enable me to sum values based on a condition. For example, I have three columns which contain a condition and two amounts. If the condition is of the 'each' variety, one value will be used in the sum. If the condition is of the "square foot" variety, another value will be used. Here is a small diagram that may help visualize this: A B C D 1 Measure Unit Cost S.F. Cost Summed Total 2 Each 3.00 .30 3 S.F....

Conditional Formatting on cells beginning with a hyphen
Is it possible to do conditional formatting on cells beginning with a hyphen? Thanks, Greg 1. Place the cursor in A1 cell and select the Range 2. From menu Format>Conditional Formatting> 3. For Condition1>Select 'Formula Is' and paste the below formula =LEFT(A1,1)="-" 4. Click Format Button>Font>Color select your desired font & Background Color pattern and then give ok Change the cell reference of A1 to your desired cell, if required. But keep in mind that when applying the conditional formatting the Active cell should be in the ce...

Count Age Grouping
I have an access 2k database in which I need to count groups of records of individuals by that age groups such as 14- 20 no of individuals 21-30 no of individuals 31-40 no of individuals 41-50 no of individuals 51-60 no of individuals 61-70 no of individuals 71-80 no of individuals 80+ no of individuals I have both DOB and Age fields in the table I have tried several queries but with no luck and ideas On 19 Mar 2007 16:51:49 -0700, "Nemesis_uk" <nemesis_uk@ntlworld.com> wrote: >I have an access 2k ...

Count the text in a column
I would like to count the text in a column then for it to add a figure in another cell if it meets the text criteria Thanks! Do you mean count the characters? If so =SUM(NOT(ISNUMBER(A1:A20))*LEN(A1:A20)) as an array formula (committed with Ctrl-Shift-Enter) -- HTH RP (remove nothere from the email address if mailing direct) "Peter Curtis" <PeterCurtis@discussions.microsoft.com> wrote in message news:94C093C4-38DC-4989-846A-9352F3298B7C@microsoft.com... > I would like to count the text in a column then for it to add a figure in > another cell if it meets the t...

How do I make X-values of a chart dependent on values in cells?
Greetings. I have a chart which can go from x-value 0 to x-value 200. However I'd like to be able to input min X-value into a cell, and a max X-valu into a cell, and the x-value in the chart changes to reflect that. Is it possible to do that? Thanks for any replies. K -- Message posted from http://www.ExcelForum.com Hi, There is no automatic way to do this but take a look a Tushar's AutoChart Manager for a possible solution. (http://www.tushar-mehta.com/) Cheers Andy Kashgarinn < wrote: > Greetings. > > I have a chart which can go from x-value 0 to x-value 200...