Question about Microsoft Excel for PC

1 Answer

Count how many times a value appears in a column, based on anothe

In a cell on worksheet 1, I want it to look to worksheet 2, column C, and count how many times the word 'EME' appears in column A, BUT ONLY if the value 1 appears in the adjacent column N on the same row.

Posted by on

1 Answer

  • Level 1:

    An expert who has achieved level 1.

  • Contributor
  • 1 Answer

Go to the cell you want this total in.
Type this formula:
=SUM(IF(Sheet2!C1:C10="EME",IF(Sheet2!N1:N10=1,1,0)))
make sure you end the formula with CTRL - SHIFT - ENTER which makes it an array formula. If you forget, go back to the cell with this formula and press F2 (to edit the cell) and press CTRL - SHIFT - ENTER to convert it to an array formula (Excel will show a little {...} around the formula).

Posted on Dec 26, 2008

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

1 Answer

Sum numbers in a column down until you come to a blank, then start again


Sounds like your issue can be solved readily with a pivot table.
If you give the columns of names a title like "NAME" and the number columns a name like "VALUE". All other columns should contain a unique name.

Then select the entire table, including the labels at the top ensuring that the range extends entirely over the column you need to sum the values of.

In Excel 2013, choose the INSERT menu and select the PIVOT TABLE command.
Insert the Pivot table into a new worksheet.
A new sheet will open with a strange-looking control panel on the right of the window.
Make sure that the box for NAME and VALUE (only) are checked

You will notice that Excel assumes that you want the SUM of the values for each NAME summed. The results are in the leftmost area of the worksheet.

If this works for you, please vote my answer as "helpful".

Sep 03, 2014 | Microsoft Excel for PC

Tip

How to find no. of rows and columns in Worksheet.


Hello everybody, this would be my first tip on FixYa.com. Number of people might not be aware how many rows and columns are there in Microsoft Worksheet.
This is how you can find out.
1. Select A1 cell in the worksheet
2. Now press Ctrl + down arrow from your keyboard, that will take you to the bottom of the row. You can find the number on the left side.
3. Again select A1 cell in the worksheet and press Ctrl + left arrow from your keyboard, that will take you to the last column of the worksheet. Now to number, just type "=column() " , without quotations, that will give you the number of the column.
Microsoft Worksheet columns is number from A to Z, again from AA to AZ, again from BA to BZ and so on till it reached IV in Excell 2003 and earlier version.
Microsoft Excel 2003 and old version has 16,777,216 cells per worksheet (65,536 rows * 256 columns).
Excel 2007 has 17,179,869,184 cells per worksheet (1,048,576 rows * 16,384 columns).


on Jul 27, 2010 | Microsoft Excel for PC

1 Answer

Cell freeze 3 rows together at a time.


Freeze a Row in Microsoft Excel
Microsoft Excel 2010 can freeze, or lock, a top row as you scroll down the worksheet.
For example, you may need to keep the top row of column titles visible at all times.
The "View" tab on the command ribbon contains the "Freeze Panes" button in the "Window" group.
A single row or a range of rows can lock through the "Freeze Top Row" or "Freeze Panes" options.

Open the Excel worksheet.
Click the top row heading.
The row heading displays a number just left of the first column of cells. The selected row appears shaded.


Click the "View" tab on the command ribbon.
Click the "Freeze Panes" button in the "Window" group.
A list of options appears.

Click the "Freeze Top Row" option.
A black horizontal line appears on the worksheet.
This line indicates the locked row that stays on the screen as you scroll down the worksheet.

http://office.microsoft.com/en-us/excel-help/freeze-or-lock-rows-and-columns-HP010342542.aspx?CTT=1
Freeze or lock rows and columns
also
Use Freeze Panes in Excel
Scrolling down to look at a number and then scrolling up to make sure the number you looked at is under the header you expected is not an efficient way to view a spreadsheet.
The Freeze Panes feature of Excel allows you to freeze the labels of your data in place while you review the data.
Follow the instructions in Section 1 to freeze the top row or the left column.
Freeze multiple rows, multiple columns, or rows and columns, by following the instructions in Section 2.Freeze the Top Row or Left Column
1
Open the Excel spreadsheet.
2
Navigate to the "View" tab on the top menu.


3 Click on "View," then click on "Freeze Panes." A drop-down menu opens.

4

Select the "Freeze Top Row" option to freeze the top row.

5

Select the "Freeze Left Column" or "Freeze First Column" option to freeze the left column.

6

Freeze the top row by using the keyboard and sequentially pressing the keys "ALT, W, F, R." Ignore Steps 3 through 7 if using this choice.

7

Freeze the left column using the keyboard by sequentially pressing the keys "ALT, W, F, C." Ignore Steps 3 through 7 if using this choice.

8

Unfreeze panes by repeating Steps 3 through 5 and selecting "Unfreeze Panes" or sequentially press the keys "ALT, W, F, F."

Freeze Rows and Columns, Multiple Rows, Multiple Columns, or Multiple Rows and Columns
9

Open the Excel spreadsheet.

10

Freeze column(s) and row(s) at the same time by selecting the cell to the right of and below the location you want to freeze.

11

Freeze multiple rows only by selecting the cell in the left (first) column below the rows you want to freeze.

12

Freeze multiple columns only by selecting the cell in the top row to the right of the columns you want to freeze.

13

Navigate to the "View" tab on the top menu.

14

Click on "View," then click on "Freeze Panes." A drop-down menu opens.

15

Select the "Freeze Panes" option. You have now frozen the columns or rows, or columns and rows you designated.

16

Freeze panes using the keyboard by sequentially pressing the keys, "ALT, W, F, F." Ignore Steps 5 through 8 if using this choice.

17

Unfreeze panes by repeating Steps 5 through 7 and selecting "Unfreeze Panes" or sequentially press the keys, "ALT, W, F, F."



http://office.microsoft.com/en-us/excel-help/freeze-or-lock-rows-and-columns-HP001217048.aspx
Freeze or lock rows and columns
http://office.microsoft.com/en-us/excel-help/demo-hide-or-unhide-rows-and-columns-HA010241040.aspx
Hide or show rows and columns

Aug 14, 2013 | Microsoft Office Business & Productivity...

2 Answers

I get an error when I tally cells that have errors because of dividing empty cell by empty cell.


Hi I dont really have much to do with Excel as I essentially build and repair and install, gosh I still type with two fingers!! Now below I have provided a link to Microsoft . Ways to count values in worksheets etc. If the problem persists try to update Office with what ever sercice packs are required and see if this helps. the link is http://office.microsoft.com/en-au/excel-help/ways-to-count-values-in-a-worksheet-HA010287849.aspx and I do hope it helps.
Cheers

Oct 28, 2011 | Microsoft Excel for PC

1 Answer

Need an excel formula.


The crude solution is:
=IF(Sheet3!I2="WA",IF(Sheet3!H2="Vancouver","X",IF(Sheet3!H2="Camas","X",IF(Sheet3!H2="Ridgefield","X",IF(Sheet3!H2="Washougal","X",IF(Sheet3!H2="Stevenson","X",IF(Sheet3!H2="Hockinson","X","?City?")))))),"?State?")

Where ?City? appears when the city referenced in H2 is not part of the lookup
and ?State? appears when something other than WA appears in I2.

But there is probably a better way to do this using an array of valid values like the one below:

AL NY TX WA Birmingham Albany Abilene Camas Huntsville Buffalo Galvaston Hockinson Russell New York Houston Ridgefield Stevenson Vancouver Washougal

In the scenario you could stuff the array in another worksheet and use the HLookup function to find the "Sheet3!I2" value in the first row of this array to determine which column to look in, then VLookup "Sheet3!H2" in the column of that array to see if the city referenced exists. Of course this is a much more complex formula, but it would be easily extendible without changing the formula every time.

For mor info, see "Lookup and Reference Functions" in the Excel Help.

Jun 11, 2009 | Microsoft Excel for PC

2 Answers

Ranking values based on the number of times they appear/repeat


=SUM(IF((Ax:Ay="xxxxx"),1,0) Ax=starting cell Ay=ending cell "xxxxx"=text"

Jun 08, 2009 | Microsoft Excel for PC

1 Answer

Input data


If you want to transfer your data into SAS, SPSS, or some other program, follow these guidelines:
The cells in Row 1 should contain the column's eventual data set name. Each name should be a relatively short and unique acronym that clearly identifies the data. It should begin with a letter and contain only letters, numbers, or an underscore ( _ ) where spaces would naturally fall. Avoid using special characters such as $, &, @, in variable names. Since each row represents the values from one subject, the first column(s) should contain one or more variables that give each subject a unique identifier. They become especially important if you need to merge two or more data files.
In Excel, data formats are defined for a range of cells rather than for a complete column. For this reason it is important that each entire column, including cells with missing or uncollected data, have one, and only one, format. Actually, you do not need to format the entire column, only the portion you will eventually use. Highlight that portion and select the appropriate format from the Format/Cells option. Do not select formats that will enter commas, dollar signs, or other visual enhancements. Numeric, text, and date formats (e.g. mm/dd/yy is often a good choice) are probably the only formats you'll ever need.
The "Split" option (under the "Window" pull-down menu) keeps the row of variable names and the columns of identifiers in view, whatever range of cells in the worksheet you may need to review. First place the cursor at the most extreme upper left-hand corner where data entry begins (e.g., the intersection of Row 2 and the column in the upper left-hand corner where data appear) and then select "Split" from this menu. For any row or column of the worksheet you move to, you'll know exactly which variables you are observing (column names) and their associated ID values (rows).
For versions of Excel later than 4.0, one file can contain multiple worksheets. By default, the tabs at the bottom of these sheets are supplied names ("sheet1," "sheet2," etc.). You can change these names by clicking this space with your mouse and entering a new name. Use the same conventions for first-row variable names: use a short acronym of the page contents that begins with a letter, use only letters or numbers, and enter the underscore ( _ ) where a space naturally falls.

Jan 05, 2009 | Sage Instant Accounts 8.0 (013604ug)

1 Answer

LINE 100


If Sale Line 100 is an Excel Template then it is simple.

The Templates are just preformatted spreadsheets which are edited just like any other spreadsheet.

If you mean that you are using the Template to create another document but wanting all the fields to be the same as the Invoice then I suggest the following method:

Copy the Template fron Worksheet 1 onto Worksheet 2.

Add and remove any fields that you wnt to add or remove (Prices would be deleted on this copy and the title would be changed from Invoice to Delivery Docket, etc)

Then (on Worksheet 2) in the fields that you want to be the same - click on the cell you want the same Data in and hit the = sign. Then go to Worksheet 1 and Click on the cell that you want the data to be the same. Hit Enter. This will send you back to Worksheet 2 and you will see that any data entered into the cell in Worksheet 1 will now also appear in Worksheet 2.

Repeat this process for all the cells you want copied from worksheet 1.

When you complete the data in Worksheet 1 (the Invoice) all the data selected will automatically appear in the cells selected on Worksheet 2.

Try this on worksheet 2 select cell A1, type =, then open worksheet 1 and select B1. You will be returned to worksheet 2 and the formula in cell A1 will now read =Sheet1!A1. Enter anything into Shhet 1 A1 and you will see that it appears in the appropriate cell on Sheet 2.

When you want to print off the Invoice and the Delivery Docket - you only have to open each Worksheet and hit Print and a copy of each worksheet will be printed giving you an Invoice and a Delivery Docket with the appropriate info on each printout (eg if you exclude prices from the Del. Docket then after completing all the details in the Invoice will create a Del Docket with all the relevant information other than the prices.

Does this answer your question?

Apr 25, 2008 | Business & Productivity Software

1 Answer

Exel 2003


You can refer to cells that are on other worksheets by perpending the name of the worksheet followed by an exclamation point (!) to the cell reference. In the following example, the AVERAGE worksheet function calculates the average value for the range C1:C10 on the worksheet named Marketing in the same workbook.

default.aspx?assetid=za010939481033 Refers to the worksheet named Marketing default.aspx?assetid=za010939491033 Refers to the range of cells between C1 and C10, inclusively
  1. Click the cell in which you want to enter the formula.
  2. In the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.) default.aspx?assetid=za060515351033, type = (equal sign).
  3. Click the tab for the worksheet to be referenced.
  4. Select the cell or range of cells to be referenced.

Jan 01, 2008 | Microsoft Office Standard for PC

1 Answer

Using if and then to add values to a cell


Try using the SUMIF function for one condition or multiple conditions. Use the SUMIF worksheet function to create a total value for one range based on a value in another range. For example, for every cell in the rage B5:B25 that contains the value "Northwind", the following formula calculates the total for the corresponding cells in the range F5:F25. Formula:
=sumif(B5:B25,"Northwind",F5:F25)

Here are SUMIF functions for multiple conditions.

=sum(if((B5:B25="Northwind")*(C5:C25="Western"),F5:F25))

=sum(if((B5:B25="Northwind")+(C5:C25="Western"),F5:F25))

=sum(if(B5:B25="Northwind",IF(C5:C25="Western"),1,0)))

Also check your help for SUMIF functions.

If this helps you, Please rate me. Thanks, Patty

Dec 09, 2007 | Business & Productivity Software

Not finding what you are looking for?
Microsoft Excel for PC Logo

1,487 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Business & Productivity Software Experts

Brian Sullivan
Brian Sullivan

Level 3 Expert

27725 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18297 Answers

Sudeep Chatterjee
Sudeep Chatterjee

Level 3 Expert

3267 Answers

Are you a Microsoft Business and Productivity Software Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...