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

20 Q & A posts tagged with IF

Nov 21

How do i get the total of one column based off of another column in microsoft excel 2010?

I do not want to "add" the numbers. Basically i have one column of numbers lets call it column (B) and another column (C) with one character it might be an "x" and it might be a "1" anyway. I need a formula that will tell me the total number of info in column B if there is a character in column C. Is this enough info to get help?


Yes sure
Paste this in cell in column D

=SUMIF(B:B, C:C, "X")

This one will get the total of column B, if its equivalent cell in column C is "X"

You can change "X" to be "1", or 2 , etc

Let me know

Monday 11/21/2011 5:58:10 PM
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 13

What device do I get if I need to split an ethernet connection from an LAN line from the wall?

An ethernet cable comes out of the wall into a desktop, I need to share that port from the wall, what do I get if I want to split the ethernet connection to that desktop and my computer


Called router
There are a lot out there, wired, wireless or both wired and wireless at the same time
something like this…

usually the wired only are much cheaper…

Sunday 11/13/2011 9:55:16 PM
Nov 13

How do I kick an Xbox 360 from my WiFi network remotely?

I'm trying to torrent and download here but my brothers are hogging all the bandwidth playing on Xbox Live! The Xbox 360 is connected wirelessly to the network so is there a way where I can quietly kick it off the network, secretly allocate all of the bandwidth to myself, or just control my home wireless network without them knowing?


Yes, sure
Most wireless routers enable you to control that using its IP (like
You should have that IP in the router manual
Once there, you can control who gets what
You can create password, refresh IP addresses, restrict access to only certain devices, etc

Sunday 11/13/2011 2:28:12 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
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 12

How do I remove blank (but not necessarily empty) cells from a drop down menu in excel?

I want to have a row of drop down lists. In each drop down I want all the options to exist EXCEPT any that have already been selected. This is my primary problem. The closest I can get is to remove value from the drop down range by an IF function in the source data, so that the list updates based on what has already been selected. This requires extensive functions and also leaves blanks in my drop down menus. I'm keen to have the drop downs only contain the remaining options and no blank gaps. Are there any solutions to either the primary problem, or, failing that, a solution to remove the blank (but not empty) cells from my drop down menus?

Cheers :)


You have very interesting request

Check out this file

It has new list populated every time user selects an item, the new list will have the same as old list without the newly selected one, it was done for tutoring purposes

It should do what you are looking for, you might need some tweaks though
That was done using the following Excel features:
Data Validation
OFFSET function
IF function

Let me know if it helped or not


Monday 4/12/2010 2:35:47 AM
Apr 5

How do I make an abridged frequency table using excel?

I have a large data set, they're ordered but I want to create an abridged table i.e (20-29, 30-31, etc..) Halp. x


You need to do it in formula
If you have Excel 2007, then the function SUMIFS will help you…


Monday 4/5/2010 9:10:47 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

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 21

How do I merge seperate folders into a new one in excel?

each folder has a part # , descrptn , Qty , Unit price , total
fldr 1 bin 1 = 101.00
bin 2 =23.21
bin 3 = 31.01
fldr 2 bin 1 = 21.03
bin 2 = 333.31
bin 3 = 21.13
fldr 3 bin 1 = 31.69
bin 2 = 61.23
bin 3 = 19.61
bin 4 = 21.33
what is the best way if possible to put the three into one with a grand total? Trying to track inventory Thanks for any ideas


Do a forth table (or folder) and put bin 1, 2, 3 and 4
then use the SUMIF function to achieve the total of bin 1 from the other three tables (folders)
Paste this in B2 in Sheet2
=SUMIF( Sheet1!A:A, A2, Sheet1!B:B)
Assuming your first table (folder) is in sheet1 having the bin text in column A and the total in column B
Also you have Bin 1 in Sheet2 A2

do the same for the other 3 tables (folders)

VBAXLMan is back

Wednesday 1/21/2009 3:24:58 AM
Jan 19

Nesting IF and SUMIF?

I'm trying to create a summary document of invoicing for my company and was wondering if Excel has any functionality that allows me to look through a table of vendor invoices that have been paid, identify all invoices that have been coded to a particular project, identify all instances of one particular vendor within the list of invoices for a specific project, and then sum all invoice values that are associated with the instances I identified of the particular vendor.

So, for example, I want to search a table for all occurences of project code "1234", then search the list of occurences of "1234" for vendor "XYZ", then sum the invoice values for all occurrences of vendor "XYZ" within the list of occurences of "1234".


If you have Excel2007, then use SUMIFS like this
=SUMIFS( D:D, A:A, "1234", B:B, "XYZ")

If you have Excel2003, then use DSUM like this
Put the title of project code column in cell F1, put 1234 in F2
Put the title of vendor column in G1, put XYZ in G2
Paste this in H1
=DSUM( A1:E500, "Invoice total", F1:G2)

Assuming, you have the list in A1:E500 with column headers in A1:E1
And you have the column you want to sum named "invoice total"

Do the changes as your table and enjoy it

VBAXLMan is back to feed you Excel needs

Monday 1/19/2009 9:04:24 AM
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 17

MS Excel fomular for link sht?

An expert told me to use the above fomular to link 3 work sht into one main sht for summary purpose. I can't contact him now.
Can anyone tell me :
1) How to COPY the data in the 3 work sht and use the above fomular to pPASTE it on the main sht.
2) please explain the meaning of the fomular in layman term.

Thank you very much for help.

WT Tan


Hello my friend, it is me who send you the formula.
Now what is the problem?

mail me


Sunday 12/17/2006 10:04:44 AM
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