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

6 Q & A posts tagged with SUMIF

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