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

19 Q & A posts tagged with SUM

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
Sun
May 2
2010

Excel help? 10 points?

http://i181.photobucket.com/albums/x93/x…

1. Add the numbers in the Student Number column as labels. (Example: '1203)

What does this question mean and how would I add the labels?


Answer

Labels by default are enabled, means you can use them right away
however, labels are usually the first column (or first row) of a table

It is confusing that you want to ADD labels
may be you need to enable them, if they are disabled
Go to Excel Options > General tab
And search for the "Use labels in formulas" check box, make sure it is clicked

After you enable them, you can use them in formula, just like
=SUM(Student Number)
to sum all the numbers


let me know if it worked or not

VBAXLMan

Sunday 5/2/2010 5:33:27 PM
Sun
May 2
2010

I need to have the auto sum function in excel calculate alphabet characters?

I would like to have the sum function include alphabet when calculating sums. How can I do this?


Answer

Did you try the formula LEN
LEN is the formula to sum how many characters in a cell

So, if you add this in column D (Assuming the column C has the cells you want to sum its chars). paste this
=LEN(C1)
Then copy it down to get the length of all cells characters

Then do a regular SUM on column D

Hope this will help

VBAXLMan

Sunday 5/2/2010 6:53:36 AM
Sat
Apr 10
2010

How do I make a comparison chart using formulas in Excel?

I have an assignment to do where I need to research three different computers and make a comparison chart to find out which is the best computer for the money. The only problem I'm having is that I was never taught how to do this using formulas and/or functions. What formulas would I use to compare different things and find which is the best?


Answer

Your question is so general
But let me try
You need first to build the table
Based on the data you have, you need to study formulas like
=A1/A2
or
=
besides IF, SUM, AVERAGE
most of these formulas are in Insert > Functions (if you have Excel2003 or earlier)
or in Formulas tab > Insert Functions (If Excel2007)

Let me know if that worked or not

VBAXLMan

Saturday 4/10/2010 2:36:50 PM
Thu
Apr 8
2010

How do i do regression analysis with excel 2007 to find the parameters for the equation f.r.=A0x0+A1x2+A3x3?

the x's are given data and i need to solve for a0,a1,a2


Answer

Put A1, A2 and A3 values in cells B2 to B4
Put x value in cell F1

Then in cell C2, paste this:
=SUM( B2*$F$1, B3+$F$1, B4*$F$1)

Cell C2 has the result

Now changing any of the cells B2, B3, B4 or F1 will affect the result instantly, try it

VBAXLMan

Thursday 4/8/2010 5:19:22 PM
Thu
Apr 8
2010

How can I add a column of cells in Excel like C3 +C49 Excel says the formula is wro?

Addition of a Column of Cells

=(C3:C49)
Excel says the formula is wrong why?

= SUM (B1:B3)


123


Answer

I don't understand the problem

Try this though

=SUM( C3:C49)

VBAXLMan

Thursday 4/8/2010 4:50:31 PM
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
Sat
Apr 25
2009

I want to add no. of hours spreaded in multiple rows in excel, can any body tell me the formula for the same?


Answer

If you have these cells values entered as numbers (and not dates), then use the regular sum function, like this
=SUM(A1:A5)

If you have these cells values as times (or dates) then that would requires some work
Try using this one
=HOUR(SUM( B:B)-INT(SUM( B:B)))

Where you have the dates in column B


VBAXLMan

Saturday 4/25/2009 8:50:20 AM
Thu
Apr 23
2009

I need an MS Excel Formula that generates the total time taken from a list of times.?

Example.

I have a list of times (clock times) and I need to get the total time (duration).

5:30 am
5:45 am
6:15 am
6:30 am

The total duration time spent should be 1 hour and 15 mins or 75min.

Can I get an excel formula that does the above?


Answer

I am not sure if I got you correct here, but let me try this...

Option 1:
Now, if you have that list starting from cell A1 down
Paste this in B1
=MINUTE(A1)+HOUR( A1)*60&"min"
Then paste this down to get the others

That is one possibility answer to your question

Option 2:
Another one is this
=HOUR( A1)&" hours and "&minute(A1)&" min"

Option 3:
Or this
=SUM(A:A)


Let me know

VBAXLMan

Thursday 4/23/2009 11:41:34 PM
Mon
Apr 13
2009

Separate English and Traditional Chinese (excel file) into 2 columns without special characters (; or : or *)?

Hello there:

Hope u can help me...

I have a huge list of Buddhist terms (20,000) in Traditional Chinese, together with the English definition for each of the 20,000 entries. Problem: all of the terms are listed in a single column, with the english definition coming before every Traditional Chinese concept: what I try to mean with this is that some english definitions involve more than one single word (some definitions have 23 words as a whole), and the same concept, but in Chinese, might involve more than just one character (up to 7).

I cannot use a "space" to separate the text into different columns, because of the amentioned reason, there are not special characters that separate the English of the Chinese (such as ; or : or *), its all like the following sample:


9-11 am ??
a (walled) city ?
a bed ??
a day without work is a day without food ????????
a heap ?
a ko?i of nayutas ?????
a man ??
a thread, a butt ????
a wild fellow ???

like this, all the way up to 20,000

So, do u know of any way I can separate the whole text into different columns, based on the used script (Chinese and English)?

I asked some Computer science teachers at a local university: they suggested to create a macro that differentiates the script in use according to ASCII (english letters come with very low numbers, while Traditional Chinese must involve really big numbers, or so they said), but, I confess that my knowledge of computing is extremely low (main reason Im contacting u, btw...)

Hope u read this message, understand my broken english, and give me an answer...

Thanks a lot

Cheers


Answer

You don't need a macro to do that
I just fixed something like this (in pure functions) few days ago, it was about changing CamelCase to Proper, here is the file in case you are interested

Modifying that file, you can do something like this

If you have the cell in B4, paste this in E4

=IF(OR( COLUMN()-4>LEN( $B4),SUM( $D4:AC4)>0),"",IF(CODE( MID( $B4,COLUMN( )-4,1))>150,COLUMN( )-4,""))
Then copy and paste up to column AD (to get the possibilities of 26 characters)
Now in cell AE4, paste this to get the left side of the text (Before the first Chinese char)
=LEFT(B4,MAX(E4:AD4)-1)

And this in nAF4 to get the right part (after the first Chinese char)
=MID(B4,MAX( E4:AD4)+1,500)


Good luck with that, let me know if you didn't understand it or you want to edit it

VBAXLMan is here to fill your Excel needs

Monday 4/13/2009 12:09:05 PM
Thu
Apr 9
2009

How do I merge multiple Excel worksheets into one Master sheet that updates?

I am creating a program for the agents in my real estate office. It is a Lead Management program. It has 5 worksheets, one for each different type of customer. I would like a 6th sheet that will contain all the names from the other 5 sheets. I also would like the "Master" sheet to update automatically when a new lead is added or changed. HELP!!!


Answer

Actually
SUM function WILL work fine
It will omit cells with names, or anything else than numbers
So it will sum only the numbers in the range

I would suggest checking out this video lesson, it will help you to do SUM across sheets
http://file1.net/lesson.asp?id=fjame5p10…

Then do the SUM using the same context
=SUM( INDIRECT( bla bla bla))

Lets assume you have the sheet name in cell A1, and you want to sum the row 1 from that sheet.
So paste this in cell B1
=SUM( INDIRECT( A1&"!"&ROW()&":"&ROW() )

Then copy it down to cell B2, B3 and B4 to make these cells get the total of the corresponding rows from the sheets in the range A1, A2, A3 and A4 respectively

Hope this helped

If not, mail me here

VBAXLMan is here to fill your Excel needs

Thursday 4/9/2009 2:01:12 PM
Thu
Apr 9
2009

Can I count number of rows based on 2 critera that are inputted by the user?

I have many sheets of raw data that I am trying to Roll up in a simple Dashboard using excel. So far I have managed fine but have come stuck on this part.
I am trying to count a number of rows based on 2 criteria. The problem is these criteria are inputted via validation list from the user!

The user selects a name of a program and what week number they would want to view a button is selected to run a macro that calculates this information.

The macro is no problem just the input of the search criteria via validation list.
I have tried everything I know but cant figure it out.
Any help is much appreciated


Answer

I would rather you do DSUM
Here is a video lesson on how to do it
http://file1.net/lesson.asp?id=6d2CvCTrt…

You can make the criteria range as the same as the validation cell (Excel will not mind)

Let me know if everything goes well or not

VBAXLMan

Thursday 4/9/2009 12:12:14 PM
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
Thu
Jan 15
2009

I really need help with Excel.?

I have added drop down lists to a workbook. Now I would like to have a total on another work book of things from the lists. Is that possible?


Answer

Yes, sure
When you say total you mean sum of numbers right?
if so, just do a regular sum like this
=SUM( [Workbook2.xls]Sheet1!A:A)
or insert the SUM function in the second workbook (While first one is open) and go back to the first workbook, and select the range you want to sum

If this is not what you are looking for, you need to describe more

VBAXLMan

Thursday 1/15/2009 8:55:41 AM
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

Tags

History