Column B contains Units (DPO,ECU,AN-2) column C contains Program Names (RCM, RBM). I want to count number of times a Unit has Program.

Result example DPO has 2 RCM, 3 RBM

Can you do this using a pivot table where columns B & C are Row Fields and Count of B&C is data fields.

Posted on May 22, 2009

Conditional formating should be able to this. But how is your data organized? (Column headers, Row headers etc.)

Oct 20, 2014 | Microsoft Excel for PC

Merging Columns In Excel
Now that we've clarified what merging columns actually means, we can explore how to do it. The first step is to perform the merge for the first cells. Let's go back to our first example and suppose that we are merging column A that contains first names with column B that contains second names. We'll put the merged columns into column C. To merge cell A1 with cell B1 we woul type the following into cell C1:=A1&" "&B1

paste this into C1 (or where needed)

=A1&" "&B1

paste this into C1 (or where needed)

=A1&" "&B1

Jun 15, 2010 | Microsoft Office Excel 2007

Use the COUNTIF command. The COUNTIF command can count the criteria for a range of cells. Since you can only use it for one range of cells or criteria, you simply add another criteria to the formula as follows: =COUNTIF(AG1:AG5,"X")+COUNTIF(Sheet2!L1:L6,"X")

Apr 10, 2009 | Microsoft Excel for PC

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.

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)

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).

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).

Dec 21, 2008 | Microsoft Excel for PC

create a dummy column (columnC) containing columnA&columnB

use countif at columnD to count the number of observations per combination of columnA and columnB (in particular those with blank entries in columnB).

use countif at columnD to count the number of observations per combination of columnA and columnB (in particular those with blank entries in columnB).

Sep 15, 2008 | Business & Productivity Software

Use Pivot table, it might help you reach your target..!!

Aug 12, 2008 | Microsoft Excel for PC

Jul 15, 2008 | Microsoft Excel for PC

The solution I've used in similar situations is to create a 3rd column C with the items in column A and column B concatenated.

C2 = A2 & B2

C3 = A3 & B3

C4 = A4 & B4

etc.

Then use COUNTIF function: =COUNTIF(C:C,"FredRed Ball")

Hope this helps.

C2 = A2 & B2

C3 = A3 & B3

C4 = A4 & B4

etc.

Then use COUNTIF function: =COUNTIF(C:C,"FredRed Ball")

Hope this helps.

May 27, 2008 | Microsoft Excel for PC

hi this my id :dadu_mf@rediff.com plz send excel material

Mar 25, 2008 | Microsoft Excel for PC

Jan 28, 2016 | Microsoft Excel for PC

245 people viewed this question

Usually answered in minutes!

The expected result in column D would be the number of times a Unit came up with a certain Program. If we look down columns B & C I want to know how many times, as an example, does DPO show in column B, while RCM shows in column C on the same row.

can you explain a little more?

×