I have 2 workbooks linked; one contains raw data and the other is a summary of all the data sorted into months etc.
The formula for linking the two workbooks checks a column containing text (months), and then sums the values of another column if for each different month. This works fine if my column with the month is hardcoded with the text valve e.g.
sumif(Range,"MONTH",sum_range) and MONTH is Jan, Feb, Mar etc.
However, i really want to automatically calculate the data within the month column but as soon as i put a formula in there it fails to complete the sumif calculation in the other workbook because it cant seem to resolve the text valve from the formula....
How do i get round this issue of the text being resolved instead of the formula?
I have a list of Items and the quantities of those items on one sheet. Each item has a section location number as well. (three Columns, Item, Quantity, and Section Location). the list can repeat the same item multiple times.
This is why I then have a summary sheet to uses a SUMIF formula to sum the quantities of a given item that are in a givin section. which my SUMIF Formula works great for. But my problem is some items have the word "LUMP" as its quantity and not a number. I want to add to my array formula so if the item does have a LUMP quantity and the lump quantity is in the section location to put LUMP on the summary sheet. here is my formula
projectInfo is the sheet where the list is. Column AE is the Item column on the projectinfo sheet column B on the summary sheet would be the item that i want to sum the quantities for Column AD is the section location column on the projectinfo sheet the AA$10 is the row and column of the section location i want to limit the sum of the quantities to the formula is in cell AA59 So it will sum all the quantities in the list on the projectinfo sheet if the item and section location match whats specified on the summary sheet.
getting LUMP to display if its a lump quantity. The Lump can only show up if the item on the projectinfo sheet has LUMP for that Item AND in that section location.
EDIT: Column AF on the ProjectInfo Sheet is the quantity column
I have created a spreadsheet containing 10 different worksheets and 11th worksheet is created to show summary results of all first 10 worksheets.
Based on the situation sometimes the summary table (on each worksheet) could have "NA" Not Applicable value/text in the summary table (summary table has three distinct rows showing result of each category.
For resolving the problem please review worksheet # 10 and advise solution:
I have resolved the issue of "NA" for the first row in summary table (c25), but I cannot resolve the formula for C26 and C27. I.e., if there is "NA" in either C26 or C27, the result in C28 comes up as #Value.
Secondly, there is another worksheet named "results". This worksheet is created with an objective to show consolidated results in weighted average(summary results for each worksheet from 1,2,3.....10) but I am lost at which formula to use to get weighted average in this worksheet.
I am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.
I've created an example of what I'm working on since it involves personal information. See Attachment...
ExampleExcel.xlsx
I need to use 2 criteria to determine the Sum value for A2 thru A5. The Account# and the Type# below row 7 need to be the same as those listed to the right of the Sum column (A2:A5). I can change the Account# and Type# to text, but the Sum Range needs to be numbers.
I was able to Sum based on the Account range (=SUMIF(Account,B2,Sum), but I still need to make the Type range part of the criteria.
As you can see in the attachment, A2 = 8 when it should = 2 (as seen in row 8 under Sum Range).
I am trying to sum elements of a column only if the cell in the same row but different column has ANYTHING typed in it. Easy one, but we all have those days
I have a spreadsheet where I need to sum some cells where the cell contains text and a number.
For instance, if the cell contains an "A", then I need to sum the numbers in those cells. If "S", same thing. Is there a way to use a SUMIF with right(text,1) since the number in each cell will always be a single digit?
I don't know how to copy an Excel table to a Post.
ans....>and Good evening. I would like some helps about how to use sumif or any formula that can sum my credit. For example code------credits a12345------1 *b12345-----2 c12345-------1.5 *d12345-----3 The answer is 5 I would like to sum the credits only * infront of them.
How can I do SUMIFs based on columns that contain text? Im having some trouble with them. I just need to check column H for the text "Newham" and then total the corresponding cells in column F
I would like to use a formula that will pull values into a sum where the values are in a column and the category is in another column. The criteria in the text cells could be located anywhere in the cell.
Example: Column A Column B xyzyszt (Baseline) 7890 wrxyz4sdtr (Strategic) 1234567 zecgjioae (Baseline) Infrastructure 71235 qpielcmjj ae (Strategic) New Capability 278332
I'm trying to make a spreadsheet to organise my bank statements. I have raw data from online banking and I want to sum all the transactions that have "SAINSBURYS" in, for instance. I am using SUMIF to add the totals only if "SAINSBURYS" on the totals page matches "SAINSBURYS" on the raw data page. The problem I'm having is that the raw data cell doesnt just display "SAINSBURYS" but "SAINSBURYS LONGWATER BCG 12345678" for example, and so i cant find a way for SUMIF to recognise that it contains "SAINSBURYS".
I have a column of multiple text values I want to pull and then in another column pull the value. I can do it on just one value =SUMIF(Sheet3!$F:$F,"NEX",Sheet3!K:K) but need to include multiple text values. Also if the text is not found I want it to return a "0".
I have the data below, there is alot more but I'll just be specific and i'll expand it myself to include the rest that I need.
I want to do sumif if in Column A that is between range R10 and R99, but I cannot figure out how to get the formula to work right since I have text instead of numbers. Also some of the other sumif's i need to do have only letters (for ex. RAA to RAZ or RDA to RDZ).
I have a need to sum values in the right column of a 2 column table based on multiple criteria of the values in the left hand column. The table has 500 rows.
The two criteria are: 1 - that the cell contains a specific text string ("product a")and 2 - that the cell contains a text string for the month in question ("Jan")
The other limitation I have to work with is that the formula is in a different workbook to the source data. I have a worksheet that lists the external workbook/sheet and have been using INDIRECT in the DSUM.
I read through the examples here: [url]
Basically they recommended DSUM as the most efficient.
I can get the DSUM working, but it needs the workbook to be open before it can evaluate the expression and therefore I get an error when I select to update values upon opening the workbook.
So in summary I wondering if anyone can show me how to write a SUMIF formula based on the above criteria.
I'm trying to get a simple Sumif formula to work. I have used this formula a hundred times before but this time the results are inaccurate. I suspect that it may be because of the math symbols which are used in the criteria text i.e. < and >
My formula is as follows: =SUMIF('Raw Data Export'!D:D,"< No Project >",'Raw Data Export'!G:G)
A data extract from the sheet named "Raw Data Export" is attached.
I am trying to sum a column if Col B is equal to "TM" & Col A is equal to "Launch" then sum Col H. I am sure it can be done and I am hoping the right person reads this posts and enlightens me.
I come across a spreadsheet that is using DSUM formula, I did not understand why someone used DSUM instead of SUMIF formula, because using DSUM formula they had to use two extra cells for criteria, as seen in this example. I like to know is there any advantage of using DSUM instead of SUMIF in this example. The formule they used is =DSUM(J109:K113,2,$M$2:$M$3) and the criteria is G,D so they are adding all Debits in that column. I changed the formula to =SUMIF($J$110:$J$113,"D",$K$110:$K$113) that of course does not need $M$2:$M$3 range criteria.
G 3458224.37 D 3401317.50 C 3401317.50 C 56906.87 D 56906.87
i am using "sumif" formula in cell C5 and C10, the next step is to add up these two cells (sum up the figures of "sumif"), then the answer will be +15+6=21, how to do with using single formula.
The following formula finds the lowest five values in row 6 from column H to W. However if there are less than five values in the row the formula returns 0. I need to tweak it to return the smallest 5 values weather there is one or a full row of 16. Also if there are any of the same values I still want only the five lowest.
I'm trying to do what I think its a simple formula with Excel 03 but can't get it to work. The info is in two work sheets - A&B.
[code]WORKSHEET A WORKSHEET B A COLUMN 1 COLUMN 2 COLUMN 3 COLUMN1 COLUMN 2 B BLANK BLANK 10 BLANK DATE:1/28/09 C NAME DATE WORKED 12 NAME D Jones 1/2/09 13 Bell E Smith 1/6/09 12 Smith F Cox 1/28/09 12 Cox G Cox 1/28/09 10 Collins H Collins 1/28/09 11 Jones
In worksheet B, Column 2, I need to pull the SUM of COLUMN 3 IF THE NAMES IN BOTH MATCH AND THE DATES MATCH
The below formula is giving the desired result and everything seems fine. Need if there is an alternative formula which can give same result in more refined and simplified manner. =K28+SUMIF($E$3:$E$24,$E$7,$D$3:$D$24)/(K23)-SUMIF($B$3:$B$24,$B$8,$D$3:$D$24)/(K23). The formula is based on the following data:
K28 = Last month balance Column E = Type of expenditure Column D = Values Column B = Payments K23 = Currency conversion rate
I am new to using SUMIF. I successfully wrote a formula telling it to sum one column for each row in another column where it equal "Forecast*". Now, I want to write a formula that says to sum a column for everything in the other column that does not equal "Forecast*". I tried using , but either it did not work, or I did not use the right syntax.
It's been awhile since I've used excel formulas so I'm very rusty but I need to count the number of times I see a particular item in a list as long as it has another criteria as well. Example List and Result are below: I want to count the number of AAA Rewards that Alex has....
List #DateEmployeeAmountReward Type 14/17/07Alex$5.00AAA Reward 24/19/07Joe $10.00AAA Reward 34/19/07Alex $5.00 Store Certificate 44/20/07Alex $10.00 AAA Reward
Result Employee # AAA Reward Amount # Store Certificates Amount Alex Brian Joe
