ANmarS - Search results

Excel/VBA expert since Sep 1997 (20 years 9 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

Mon
Nov 21
2011

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?


Answer

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
Sat
Nov 19
2011

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

Saturday 11/19/2011 8:42:40 PM
Sun
Nov 13
2011

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


Answer

Called router
There are a lot out there, wired, wireless or both wired and wireless at the same time
something like this
http://www.buy.com/sr/searchresults.aspx…

usually the wired only are much cheaper
http://www.buy.com/sr/searchresults.aspx…

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

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?


Answer

Yes, sure
Most wireless routers enable you to control that using its IP (like 192.168.0.0)
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
Fri
May 14
2010

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

Friday 5/14/2010 1:52:10 PM
Wed
Apr 28
2010

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

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

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


Answer

You have very interesting request

Check out this file
http://www.File1.net/Links/NewList.xls

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
Name

Let me know if it helped or not

VBAXLMan

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

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


Answer

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

http://office.microsoft.com/en-us/excel/…

VBAXLMan http://office.microsoft.com/en-us/excel/…

Monday 4/5/2010 9:10:47 AM
Thu
Apr 23
2009

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

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

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

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

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

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

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

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

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

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

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

Friday 4/3/2009 12:45:16 AM
Wed
Jan 21
2009

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


Answer

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
Mon
Jan 19
2009

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


Answer

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
Mon
Jan 19
2009

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

Excel Formula


Answer

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
Fri
Jan 16
2009

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

Friday 1/16/2009 6:13:55 PM
Sun
Dec 17
2006

MS Excel fomular for link sht?

=SUMIF(INDRECT(C$4&"!$A:$A"),$6,INDIRECT…
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


Answer

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

mail me

VBAXLMan

Sunday 12/17/2006 10:04:44 AM
Mon
Dec 4
2006

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

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

Tags

History