## 11 Q & A posts tagged with COUNTIF

### What Kind Of Excel Function Do I Need?

Q: In cell B6, insert a function to calculate the total number of units in the apartment complex in A6. Be sure to enter the criteria range as an absolute reference. Copy the function down through B10.

In column A there is a list of different types of apartments and I need to find total units of each apartment column B. what kind of function would I use to count and recognize each number of units for each different apartment?

### Answer

I am not sure what do you have exactly in A6, however, I am assuming only

paste this in B6

=COUNTIF( A:A, A6)

Then fill down

### Can we use excel worksheet to evaluate a multiple choice question answer sheet?

for eg: to a question, 1) Prime Minister of UK is

a) David Cameron b) Nick Klegg c) Gordon Brown d) None of the above.

how to use logical formulas in EXCEL for candidates answering as a/b/c/d and to assign 1 mark for correct answer and 0 for incorrect

### Answer

You need to put the answers in one row or one column

Then use the COUNTIF formula to calculate how many A's there

Something like this:

=COUNTIF(3:3,"A")

Assuming your answers a,b,c,d are wherever in row 3

VBAXLMan

### Is there a formula for a name count within the entire workbook of excel?

I am creating a workbook in excel with January - December Spreadsheets. I need to find out if there is a formula that I can use to count how many times a name is used through out the ENTIRE workbook, not just each sheet. Thanks in advance.

### Answer

You have two options:

1- is use the COUNTIF with multiple-3 dimensions ref

In English, do this

=COUNTIF( January:December!A:IV, "name")

Just make sure that the sheets January to December are in the regular order.

2- Using INDORECT to retrieve the value for each sheet (In a new sheet), then total that

- Put sheet1 name in cell A2 in new sheet

- Paste this in B2

=COUNTIF( INDIRECT( A2&"!A:IV"), "Name")

- Put other sheet names below and fill B2 down

- Total the column B to get the total plus the number for each sheet

Let me know if all these makes sense or not (from my profile)

VBAXLMan

### Excel > Use formulas on the last x rows of data?

I'm keeping track of the total number of wins and losses for a sports team. There's one game a week. I will have 16 rows in my Excel sheet to mark a win or loss. If the team wins that week, I'll put in "W" for that week's row, and "L" for a loss. As you can imagine, a new row will be populated with either a "W" or "L" as the weeks progress.

I'm trying to get a win/loss count of the last 5 games played. So if the team has played 7 games, I want to show the total number of wins and losses of the last 5 games (games 3-7). If 10 games have been played, then show the total number of wins and losses for games 6-10. This is similar to the L10 statistic for baseball, hockey, and other sports, but in this case, I'm trying to do a L5 for the last five games.

How can I achieve this in Excel? I'm going to use COUNTIF for counting the total number of "W" and "L", but I need it to count only the last five rows of data.

Any help is appreciated. THANKS!

### Answer

Do this so that whenever you add new row/week it will automatically updated

Now, if your list of Ws/Ls starts from A2 down, paste this in D1

=COUNTIF( OFFSET(A2, COUNTA(A:A)-6,0,5,1),"W")

To get the number of Ws in the last 5 non empty cells in Column A

And do this in E1

=COUNTIF( OFFSET(A2, COUNTA(A:A)-6,0,5,1),"L")

To get the number of Ls in the last 5 non empty cells in column A

Also assuming you have the cell A1 with text as header

Good luck

VBAXLMan

### MS Excel . I want to search the value of one cell among other cells to check the validity of that cell among o?

Dear Sir/Madam,

To make my question more clear I want to search the value of one cell among other cells to check the validity of that cell among other cells.for instance,

a1:Jack

b1:paula

b2:jack

b3:matue

b4:sara

in case of making chart c1:true or false

in this example=T

a1:Jack

b1:paula

b2:sam

b3:matue

b4;sara

in case of makin chart c1:true or false

in this example=f

### Answer

You can use COUNTIF, like this

=COUNTIF( A1, B:B)

To count the number of times that the cell A1 showed in the range B:B

Or may be like this

=IF( COUNTIF( A1,B:B))

To get True or False

That is what I could understand from your foggy question

VBAXLMan

### Microsoft Excel question?

I have a column of percentages E127:E132 and I need a formula that will return one answer ($250.00) if there are three or more items in the column above 98% and another answer ($0) if there aren't.

### Answer

=IF( COUNTIF(E127:E132,0.98)>=3,250,0)

Paste this in a cell

VBAXLMan

### How do I create a formula that pulls information from one excel spreadsheet to another?

I have multiple spreadsheets in the same excel file and I am trying to create a formula or a set of formulas that will recognize a number, compute how many of the items have that number and then on a separate spreadsheet, show that sum. What I am working with are 7 spreadsheets- 4 weeks, month, quarter and year. In the week spreadsheets, an incident is labeled with a 1,2,or 3 depending on its severity. I need a formula that would calculate all of the 1s, 2s and 3s separately as their own value and then place that value in my month spreadsheet in the 1, 2 or 3 category.

Thank you in advance for your help.

### Answer

If you have:

1- The week sheet name is "week"

2- The range where the 1s, 2s and 3s are in "week" sheet is A1 to A20

3- The month sheet name is "Month"

4- 1 in cell A1 in "Month" sheet

5- 2 in cell A2 in "Month" sheet

6- 3 in A3 in "Month" sheet

Then paste this in B1

=COUNTIF( Week!$A$1:$A$20, A1)

Then copy it to B2 and B3

VBAXLMan

### Is There A Function On Microsoft Excel Which Allows Me To Highlight Duplicate Records?

What I need to do, is to paste 2 different corporate structures into one spreadhseet and then delete the duplicates. Is there a function on microsoft excel which ahighlights any duplicate records in column A of excel?

Thanks

### Answer

Yes

If you are using Excel2007, the Conditional Formatting in the Home tab has duplicates, go to

Home > Conditional Formatting > Highlight cell rules > Duplicate values

If you are in Excel2003, then you may need to use a function plus the conditional formatting like this

Use the COUNTIF function in an empty column to show 1 if duplicated, 0 if not like this

=IF( COUNTIF( A:A, A1)>0,1,0)

Then apply conditional formatting to that column to color if the cell value equals 1

Enjoy it

VBAXLMan is here to fill your Excel needs

### CountIF, DCount or DcountA, please help?

Hello

How are you? Hope you are fine & will receive this message at your best.

I have a problem & I need your help.

I have created a work sheet in MS excel where I have entered all our transactions during the last year processing by our different sales representatives in different cities of country.

Column D contains CITY which may b like Lahore, Karachi or Islamabad where they have sold the items, & column I contains sales representative name like Ali, Asad, Waqas etc who have sold the items.

I need to do a count of all the transations where the city is "Islamabad" AND the Sales representative is "Ali"

Please help

### Answer

You have these options:

1- If you are in Excel2007, you can use COUNTIFS, which will count based on more than one condition (up to 30)

2- If you have Excel2003, you can do DCOUNTA, since the cells are having strings

3- You can also do DCOUNTA in Excel2007.

4- You can add a column having the criteria you want using simple IF, then count that column, something like this

=IF(AND( A1=T1, B1=U1),1,0)

Assuming

A1 has the city name and the list goes down to whatever

B1 has the sales man and it goes down also

T1 has the city you want to search for

U1 has the sales man you want to search for

Then do a SUM for that column to have the count

5- You still have another option, which is PivotTable, but I don't recommend using that powerful tool for something simple like this

let me know

VBAXLMan

### Excel - How to get the frequency of the words in a para?

The para contains 1000 words. I need to find out the the top 50 words appearing most often in the para together with the number of appearances only using excel commands - can't use VBA or any other s/w!

any help ??

### Answer

The only way to do it is to convert that para into cells (For temporary)

Now, if you have that cell in A1, paste this in B1

=LEFT( A1,SEARCH( " ",A1)-1)

And this in C1

=LEN( B1)

And this in D1

=COUNTIF( B:B,B1)

And this in B2

=MID( $A$1,SUM( $C$1:C1)+2,SEARCH( " ",$A$1,SUM( C1:C1)))

and copy C1 to C2, D1 to D2

Now copy cells B2, C2 and D2 down until you find all the text you have in that para in the cells of column B

Now, the D column will give you the number of any corresponding text found in cell A1

The interesting thing, is that when you change cell A1, all of these will be changed automatically

Enjoy it, Yes, I know I am good at this, I am the VBAXLMan here

### An Excel search question?

I am trying to make an excel worksheet simply count the number of each persons names in a, say 10x10 grid randomly filled with names. Can it be done? And how!? I just want a list underneath of, for example "John - 10; Dave - 3; Kim - 28" depending on how many boxes each person's name appears in. I have tried DCOUNT but probably incorrectly, as it brought back totally random figures that dont make sense. Please help!!

### Answer

You will need COUNTIF

it goes like this

=COUNTIF( A1:F10, "*John*")

I recommand using * to allow finding the cell with John, and cell with John - 10

Enjoy my profile, I am the VBAXLMan