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 have a problem which is beyond my capabilities. I need excel to sum amount column (See example table below) by unique values in company column then conditionally sum those values based on the year of investment, stage of company and finally, the kicker, the summed Amount by unique companies value has to be less than a certain threshold.
For example, I want companies in Stage Column of Exit only and in year 2004 only BUT only if the sum of the Amounts by unique company values is less than 100. So in the example table this would return 0. I have code and criteria written to sum unique values based on similar criteria such as sum if company Stage is Seed and Year is 2004 but for the life of me I can't seem to make the jump further.
For Unique values I used this function:
=SUMPRODUCT(($B$2:$B2=Year04)*($D$2:$D2=StageS)*($A$2:$A2=$A2))=1
Then:
=DCOUNTA($A$1:$D9999,2, Criteria)
or
=DSUM($A$1:$D9999,2, Criteria)
This along with criteria explained before returns the number of unique companies that are Stage=Seed and Year=2004 or sum of amounts with that criteria.
I am trying to adapt this technique to get what I want but to this point have been unsuccessful. My best guess is that I will need to create an array of the unique companies (New Sheet column A) with the Summed Amounts (New sheet column B) and then run functions again with the easier equations and criteria? I am trying to avoid combining company records as each investment needs to remain individual and there are 16000 records. Any help would be greatly appreciated.
Company Year Amount Stage XYZ 2004 10 Seed XYZ 2004 20 Seed ABC 2004 5 Early ABC 2004 25 Early DEV 2004 14 Later DEV 2004 19 Later TRU 2004 100 Exit TRU 2004 120 Exit TRU 2004 100 Exit
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 have this formula to tell me how many times in a given column the value is greater than zero:
=COUNTIF('Data Entry'!AY3:AY117, ">0")
But I also need to know how many times the value in AY3:AY117 is greater than zero PLUS how many times the value in AZ3:AZ117 is also greater than zero
I've scoured this wonderful website and have got as far as:
=SUM(IF('Data Entry'!AY3:AY117,"> 0"+SUM(IF(AZ3:AZ117,"> 0"))))
but I'm clearly still way off.
Below is my formula that works for a specific date. F$8 is the date.
How could I change this if I want data b/w two time periods.
>=F$6, and <=F$8?
[Code] ....
Lotus 123 had a function where you could graphically, in a cell, show plus symbols(+) or negative symbols (-) for the value of the data. For example if you had a column of numbers that you calculated from the previous year that indicate the change, Lotus, in the next cell would allow you to graphically display the symbols that indicate the strength of the change. So if your result was a positive ten then ten plus symbols would be shown. If it was -20 then 20 negative symbols would be shown. Now the numbers I am looking at could be 600,00 and I believe there was a way to to proportion the symbols for example for every 100,000 put in one symbol so the result would be 6 (+) signs.
View 5 Replies View RelatedI 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.
View 9 Replies View RelatedI am trying to use the SUMIF function in excel where I want to sum a range of cells greater than whatever is in cell D8. Here is what I tried to use, but it doesn't work. SUMIF(A2:A10,>D8,B2:B10)
The content in D8 can change because the user makes a choice from the drop-down list and a number pops up in D8 that is referenced to their choice.
I am importing content control information in a word file into excel by using a macro in excel. The information is coming across nicely but when the information about a checkbox comes across, in excel, that's what you see, a checkbox (☐ or ☒). Is there any way (formula or vba) to replace all the checkboxes with a yes or a no?
I wrote a formula that works:
Code:
=IF(G8="☒","YeS","nO")
But this will add this information to a new column and I would like to replace the information in the existing column (the form has about 200 checkboxes) and I don't want to add a bunch of new columns.
I also toyed with VBA:
Code:
Range("G1:G100").Replace "&", "No"
But when I try to paste the ☐ in the vba code, I get a "?" so I am assuming it doesn't recognize the ☐ as a valid character.
Counting with text and symbols mixed in. This is what I'm wanting to do:
For example:
I have a column of cells that contain a long string of characters....I need to pull out text from this string and put it in subsequent columns. The symbol I am concerned with is >> For example.
If this string is in the first cell in column A:
>>abcd>>defgh>>ijkl
I would want a formula to return abcd in column b, defgh in column C, and ijkl in column d. >>this is a test>>make it work. in this example I would want "this is a test" returned in column B, and "make it work" ruturned in column C
I've attached a sample workbook to show what I am trying to do. I would like the formula to say "if Sheet2!A:A is "MON" and if Sheet2!R:R matches Sheet3!A:A, and if Sheet2!I:I doesn't match any of the values from Sheet1L:L, then I'd like the sum of Sheet2!F:F. It seems pretty simple but I've tried a million different variations of SUMIFS, SUMIF, IF, AND, etc. and I can't figure it out.
View 1 Replies View RelatedI am trying to set up a sumif statement with two criteria where if the second criteria is true, the total in the sum range returned is divided by two.
I currently have this:
=SUMIF($G$6:$G$41,"digital",I6:I41)
I want to add in "digital/creative" as a second criteria (from the same criteria range) but I only want 50% of the result of these to be totalled.
is this possible?
formula stating that:
if a number is greater than 1 add one 1 box (250) and if the number is greater than 9 add 2 boxes and is its greater than 18 add 4 boxes
i want this all in one formula,
So I've got a vertical list of dates in M/D/YYYY format, and I'm trying to come up with a method to pull certain dates that are greater than a given benchmark and paste those values in another column. For instance, if I've got a list with 3/3/2008, 5/5/2010, 6/6/2011, and 7/7/2012 I want to pull only the dates beyond 12/31/2010 (so would be the 6/6 and 7/7 ones).
I'm assuming this isn't doable with one formula, so I would need a macro. But I'm having trouble just even coming up with the logical operator for just one date. I must be doing something wrong here. If my date in B2 is 1/1/2009 and I have a formula as IF(B2>12/31/2009,"True","False"), I just get TRUE, which shouldn't be the case.
Currently, I am using countifs to count data within a specific range if it meets a criteria. Now I want to add to the formula. I need to count the cells within the specific range meeting the existing criteria I already have but only if one cell is greater than another cell.
This is what I currently have: =COUNTIFS(Report!$F$4:$F$1048576,">=8/1/2013",Report!$F$4:$F$1048576,"=8/1/2013",Report!$F$4:$F$1048576,"
I am trying to take a census by date and name for every half-hour using the attached data. So for example, I would like to know how many people Roger had that walked-in and walked-out for each date on the spreadsheet and during what hours. The census would be every half-hour and not double count times. I could actually deal with just the min and max time for the day (by date and name) and having some indicator (say a one or an X) in the cells when Roger had at least someone anytime between walk-in and walk-out. It's kind of like getting a "time worked" based on the data that's provided to me. So I know how many hours Roger worked by day.
View 4 Replies View RelatedIm using a formula to identify a job description then add up the quantity which works fine. =SUMIF(Master!A1:A10,"Bread",Master!C1:C10)
But my problem is, I have a second description in column 'B' for example called 'White'. I've been trying to get the formula to look at column A & B and if they match criteria in formula, then add up C. But can't seem to get it to work with the second criteria.
I have a workbook with several different worksheets. I am attempting to pull data from a worksheet, I will name it sheet1. Sheet1 has 6 columns. The data I am interested in is in B and F. Column B has account numbers ranging from 1 - 1,000,000 and column F has the total account balances. What I am attempting to do is to sum the data in F that falls between a criteria in B. For example I want the sum of F that correspond to accounts 4999 thru 7555.
I have tried several variations of the same formula from my online search and am still unable to get it to work properly. I have tried using ">=4999" "<=7555" and I cannot get it to work properly. It seems fairly straight forward, but I am perplexed as to what I am doing wrong.
I don't know why I can't figure this out, but it has been too long, but I need a formula that looks for yesterday's date and adds up any time that matches that date and that person from the list. So like in this example, for yesterday, my answer would be 1 hour for Ashley.
A B C
1 1/7/09 Ashley 0:10
2 1/8/09 Ashley 0:10
3 1/9/09 Bob 0:10
4 1/7/09 Ashley 0:50
Here is what i want to do. # I have a workbook say 'Template', which has two columns: ColA -> Names , ColB -> Value, his acts as a template, i.e a budget. # Another workbook 'sample' which has similar ColA(may contain additional rows) and corresponding value in ColB.
Here is what i need::
I need to copy the contents of ColA and ColB of 'sample' wholse ColB valules are greater than ColB values of 'Template' for corresponding ColA values in both, into a new workbook. SO basically, template acts as a budget check for the sample workbook, and if any value in ColB goes above the budget in template, it will show up in the new workbook. I have attached both the workbooks. Basically the red highlighted cells in Sample are the ones i want in a new workbook.
I need a formula which can sum the mode of payments separately occurring on different dates.
See the attachment. It is like
table 1
date cash credit card
1-1-14
2-1-14
3-1-14
table 2
product booking date booking amount payment date payment amount form of payment
a 1-1-14 100 2-1-14 100 credit card
b 1-1-14 150 3-1-14 150 cash
c 2-1-14 200 3-1-14 200 credit card
I want to add value in table 1 from table 2. In front of date CELL Both cash and credit card amounts should appear separately.
Book1.xlsx
I'm trying to figure out how to get sumif to sum for non-blank entries- e.g.
sumif(A1:A5,"not isblank", B1:B5).
is there away to do the sumif formula with 2 criteria? See attachment
View 3 Replies View RelatedNormally, sumif formula works well with problems that have one criteria to take into consideration. With the table that I have, I'm unable to accurately use SUMIF. I'm not even sure if I should use it since what I really need is Average. SUMProduct proves to be even harder to use for me. Here's what I have:
ID CODEStatusSTARTDUEENDTURN AROUND TIME (TAT)
122307Closed1/142/81/3112 DAYS
122265Closed1/142/22/214 DAYS
122265Closed1/142/101/3112 DAYS
122307ATD1/131/21----------
122253Closed1/121/221/229 DAYS
122265ATD1/221/30----------
Other details:
I used =SUMPRODUCT(--($B$21:$B$31=C4),--($D$21:$D$31=$D$3)) to count the total number of a specific task status per ID code, say, closed and ATD.
I manually added TAT that has "Closed" status on a per ID code basis.
Ex:
ID Code# of Closed StatusAverage TAT
12265213 DAYS (14+12/2)
Is there a formula that will give me the average TAT taking ID Code and "Closed" status as conditions/criteria? What about Vlookup, average?
In the formula (range, criteria, sum_range), I have a fixed range and a fixed summary range for each column, i.e.: ($F$3:$F$805, "criteria", O$3:O$805).
HoweverI am trying to sum up units by income level (columns D, E, and F) using information from elsewhere on the sheet. I am doing this for each city, which entails changing the criteria for all of the cities I am using three times (once per column).
Is there a simple way to autofill the criteria? They are just names of cities, all in the same column, COLUMN B. Or do I have to type each individual change?
I want a sumif where:
=sumif(f2:f500,
if column f has a 7 in it and if column e is greater than 5
Column A |Column B |Column C
Store_Name| Dates |Revenue_Generated
I want to sum the revenue generated in every row where:
Store_Name = "x"
Dates >= Date1
Dates
I need to add amounts in column B, based on column As account numbers, so I want to add only account numbers, say 17101 & 17201 and nothing else.
View 9 Replies View RelatedI have the following Formula:
=SUMIF('Sheet2'!C:C;Sheet1!B3;'Sheet2'!E:E)
In this way I got the sum of all items located in column E wich have the same value than in B3 in Sheet 1....
How could I change the formula in the way that only returns the Values in Sheet2 E:E when (If equals to B2 and C2 in Sheet 1 then Sumif)
The value which I want to use to filter is in the same columns in both sheets