ANmarS - Search results

Excel/VBA expert since Sep 1997 (21 years 2 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

17 Q & A posts tagged with count

Nov 19

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?


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

Then fill down

Saturday 11/19/2011 8:42:40 PM
Nov 10

Is there a way to change the auto-assigned value of empty cells in excel?

In excel, empty cells are treated as though their numeric value was 0, and as though they had no text value.
I'm using a custom function that runs through a range of data (which contains 0 values) and gives me a result based on the range's findings.
Currently, there are no blank cells before the end the range. Therefore, I can use CountA(range) to tell my function how many times to iterate (i.e. to continue until it reaches the end of the range).
I wish to instead specify to the program that it must continue to the end of the range (another problem) and ignore blank spaces.
Rather than re-writing all of my functions to ignore these empty cells, is there an excel option that will allow me to force excel to treat empty cells as a value other than 0?


If I got you correctly you can use the formula
When you put it inside IF, you will will get if the cell is blank

CountA will not count blank cells either

let me know if that solves it or not

Thursday 11/10/2011 3:34:45 AM
May 14

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


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:

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


Friday 5/14/2010 1:52:10 PM
May 4

How to import into MS Word ,data from two worksheets from Workbook of MS Excel file ?

I have MS-excel Data with a customer sheet with address details on one sheet & Items bought by a customer on other sheet,by using mail merge I am able to generate a letter for each customer by using customer sheet,I want to include details of items bought(from that items sheet).


I suggest you having a third worksheet that list the customers and their buying details
Then mail merge it

You might need to use formulas to do so, like VLOOKUP, MATCH, INDEX, IF, COUNTA, etc
use the customerID to grap these info


Tuesday 5/4/2010 3:40:57 AM
Apr 28

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.


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)


Wednesday 4/28/2010 12:53:03 PM
Apr 4

Create an automatic table based on the past three months?

hi, i have an excel sheet with the past five years data on it. Each row represents a month so each month it gets one row longer.

I already have a defined range that selects the most recent 3 months, but i need to know how to display the last 3 rows and the field headings on another sheet in a specific position by the click of a button(macro).

I can make a line graph using the last three rows of data with just a click of a button and now i want to show the data used underneath the chart.

Thanks for all answers


You don't need a macro to do that
You can make it in Formulas which means faster, auto updated, etc

Let's start with some assumptions:
- You have two sheets (Sheet1 has the full data and Sheet2 to have the last three months)
- In Sheet1, your data starts from cell A1 along to V1, with headers in first row and the data below it.
- In Sheet2, you want the last three months to be in A2 to V2, A1 to V1 has the row headers

Now, in Cells A1:V1, just copy the headers (you need to do that once)
In cell A2, paste this
=OFFSET( Sheet1!$A$1,COUNTA( Sheet1!$A:$A)-ABS(ROW()-1),COLUMN( )-1)

Then copy A2 along to V2, then copy Row2 into Row3 and Row4

These three rows will bring you the last three rows from your table in Sheet1 assuming you don't have any empty cells in that table in column A
And when you add new values at the end of that table in Sheet1, these will automatically updated

Does that make sense?

let me know


Sunday 4/4/2010 11:13:43 AM
Apr 23

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!


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


Thursday 4/23/2009 4:19:56 PM
Apr 13

How to add Counter to an excel workbook?

How to add Counter to an excel workbook, such that we can track how many times the excel had been viewed. The countability can be tracked?


- Press ALT + F11 to open VB Editor
- Double click on the "Workbook" object in the Project Explorer window
- Paste this in the white code area
Private Sub Workbook_Open()
Sheets( "Sheet1").Range("A1") = val(Sheets( "Sheet1").Range("A1")) + 1
End Sub

- You need to make sure that your sheet is named "Sheet1", if not, just put the name of your sheet instead of Sheet1 in this line
Sheets( "Sheet1").Range("A1") = val(Sheets( "Sheet1").Range("A1")) + 1

Good luck


Monday 4/13/2009 7:45:11 AM
Apr 13

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,

in case of making chart c1:true or false
in this example=T


in case of makin chart c1:true or false
in this example=f


You can use COUNTIF, like this
To count the number of times that the cell A1 showed in the range B:B

Or may be like this
To get True or False

That is what I could understand from your foggy question


Monday 4/13/2009 3:44:05 AM
Apr 9

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.


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

Paste this in a cell


Thursday 4/9/2009 8:23:55 AM
Apr 9

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.


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


Thursday 4/9/2009 5:20:30 AM
Apr 7

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?



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

Tuesday 4/7/2009 5:22:21 AM
Apr 3

CountIF, DCount or DcountA, please help?

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


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


Friday 4/3/2009 12:45:16 AM
Jan 19

Excel % formula non-blanks?

I have an xls where I want to enter a formula that counts the # of non-blank cells & divides by # to get a % result. i.e 56 non-blank cells/735=8%. Can anyone show me how to do this?


Sure, COUNTA will do it for you
like this

=COUNTA( A:A)/735

Format that cell as Percentage to get the %

VBAXLMan is back

Monday 1/19/2009 2:57:55 PM
Jan 19

How can i sum or count Yes or no in a column and categorized them in Product group im just one formula?

Excel Formula


SUMIF will do only one criteria, so you will need SUMIFS, but this will work in Excel2007 only
If you have Excel2003, then you can do DSUM

DSUM is criteria based sum, but you will need to build range of cells as your criteria range

You didn't give that much info, otherwise I could do it for you

Mail me here with more info or some sample file, and I can do it for you for free

VBAXLMan is back

Monday 1/19/2009 4:34:55 AM
Jan 16

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 ??


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

Friday 1/16/2009 6:13:55 PM
Dec 4

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!!


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

Monday 12/4/2006 12:50:00 PM