SUMIF: Value Is Greater Than Zero
Oct 20, 2009
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.
View 9 Replies
ADVERTISEMENT
Feb 23, 2014
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] ....
View 3 Replies
View Related
Jul 2, 2008
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
View 9 Replies
View Related
Oct 21, 2008
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.
View 6 Replies
View Related
Jul 18, 2006
I 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.
View 2 Replies
View Related
Aug 25, 2009
I have to make a table that shows that a if someone purchases
less than 5 items they receive no discount
5-10 items they receive 2% discount
11-20 items they receive 5% discount
21-50 items they receive 8% discount
over 50 items they receive 10%
and it has to be done in a way that the discount rate can be calculated using Vlookup I am struggling to find the best way to write this table. i tried numbering 1 to 50 and writing the corresponding discount rate in the second column but this looks untidy and can't calculate greater than 50 as i am not sure how to write it in the cell so it reads as >50 and not just 50.
View 4 Replies
View Related
Oct 25, 2007
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.
View 9 Replies
View Related
Sep 17, 2009
I need to perform 2 SUMIF's on 2 columns of data to return a result and I'm not quite sure the best way of doing this. I'll give an example below.
I have 2 columns of data, both numeric and the SUMIF needs to say if H1:H100="10" and also if J1:J100="907". I can perform one or the other but not both.
View 6 Replies
View Related
Apr 21, 2009
I have many kitchens using the same recipes. I need to distill information down until I've got a summary of how much is being made. Uploaded is a condensed version of the point in the process I'm having difficulty with. This workbook will pull information from 8 other workbooks and give me excatly what everyone made on any weekday.
And from there, with the kind help of this forum, I figured out how to do a SUMIF based on the recipe number. And it summed up all instances of 'Recipe X' being used. However, it continues to SUMIF itself all the way down the page... which is good, because of how recipes are chosen for each kitchen. However, I only need to report one instance of each recipe.
In the uploaded example (and I apologize for the colorful sheet, but it helped me double check what I was working on.) ... I only need to report the PURPLE results elsewhere... the first instance of each SUMIF.
View 5 Replies
View Related
Feb 27, 2012
I am wanting to use these two formulas in one cell. Is there anyway to do this? If "AD3" is 0 I want this =SUM(X3:AC3) and then if cell "AD3" is greater than 0 I want to basically use this formula
=SUM(AH3,X3:AC3)-AD3.
Is there anyway to merge these two formula's?
View 2 Replies
View Related
Jun 11, 2009
I have a Macro of 55 Columns and 2000 Rows I need to change the value in several Cells of the row in which the value of AZ is 200.01 or more. I need the following if ANY Cell in AZ2:AZ2000 is equal or greater than 200.01 then the following Cell in that Row will equal the following:
AG = 20
AW = 11
AX = " " (BLANK)
BC = N
I have attached an example of the spread sheet with Macro embeded and how it should look after the above is run. I do not know if or how to I need to tag the macro within the attachement.
View 3 Replies
View Related
Dec 8, 2008
I'm struggling to complete this formula. No matter what the entry in M30 is
(which is a concatenated formula from another sheet), I only get the highest response, which is 18.
I'm assigning a risk score based on a dollar amount. The formula is: ....
View 7 Replies
View Related
Jan 5, 2013
This is what I am tyring to do. I would like B7 to show $60 once A1 is greater then 25.
View 3 Replies
View Related
Nov 14, 2006
Say I need to figure out bonuses based on income.
If the income is less than 100,000 then the bonus is 5%.
If the income is between 100,000 and 249,000 the bonus is $5000 + 6% the amount above 100,000.
If the income is between 250,000 and 499,999 the bonus is $14000 + 7% the amount above 250,000.
If the income is over 499,999 the bonus is $31,500 + 8% the amount above 500,000
What is the formula I need to enter to make this work.
View 9 Replies
View Related
Feb 5, 2007
Need to sum that won't work.
i'm using:
A B C
00:00 07:00 =if(A1>0,B1,"N/A")
07:00 04:00 =if(A2>0,B2,"N/A")
00:00 07:50 =if(A3>0,B3,"N/A")
00:00 06:50 =if(A4>0,B4,"N/A")
This doesnt seem to work though using time formats. Column C just brings through Column B no matter what is in Column A. I've attatched an example below.
View 7 Replies
View Related
Aug 7, 2007
I am trying to create a formula that will automatically calculate greater than or less than and then add x or y depending.
I am shipping some items and we can fit 100 or less in a small box that weighs 3oz and 100+ in a bigger box that weighs 6 oz.
I want the formula to look at the quantity and determine if the quantity is 100 or less it should add 3 and if greater than 100 then add six.
I tried tons of google searches but can't seem to figure it out. This is the first forum I could find that I figured might be able to help.
I have been doing this all by hand in multiple columns...
View 9 Replies
View Related
Dec 9, 2013
Here is my code as it is right now (With the correct range, and yes its a messy range :
[Code] ......
That is my range and thats the range I have to use (The stats are spread out in those rows. How I can get this to disregard 0's? Right now it seems to fail on the third rangevalue (For example in the range above it fails and highlights Y88), but if i remove that value it does the same on the next one instead, always the third.
I have tried =AVERAGEIFS(Range,">0") versions and also ones with /COUNTIF after and others but cant get this to work.
View 4 Replies
View Related
Jan 30, 2008
I have a list of dates B2:B248. All the cells have been formatted to date type dd/mm/yyyy.
I'm just trying to count the number that are earlier than a certain date.
I thought a COUNTIF function would work! I've tried DATE, DATEVALUE etc.
I can work with the dates ie B2 (which would have 23/08/2007)-1 comes out as 22/08/2007. However when I try if(B2<B3,"lower","higher") it gives me a false reading - even when B2 is earlier than B3.
How can I check if my list of dates is earlier than a specified date?
View 12 Replies
View Related
Aug 10, 2009
I want to do is count in a column numbers greater than 13 but less than 20.
I am also trying to write another formula that counts numbers equal to or higher than 1 but less than 12. In other words,
I do not want this count to include any cells that contain 0.
View 2 Replies
View Related
Sep 17, 2009
I have this table with min and max amounts that requires a fixed amount when when the condition is met. How do I write a formula for this. If result is >$0 but < $100 = $15 and so on. I canlt get it right..........
View 3 Replies
View Related
Jan 25, 2005
I have a table populated with equations. I need to write a function to find
the smallest value in that table. However, I want to ignore the zero values.
From a dataset containing 8, 5, 0, 7 I want to find 5, not zero.
View 10 Replies
View Related
Nov 26, 2002
Is it possible to have the condition IF(H9>E9>G9,E9-G9,0) so that the calculation is only done if E9 is between H9 and G9?
View 5 Replies
View Related
Nov 28, 2011
I am trying to use Countif on a range of cells some of which have a value of "< 5" (ie less than 5), but for those cells it returns a sum of 0 (zero).
View 2 Replies
View Related
Dec 6, 2013
I am trying to format a column of data. What I would like to do is move the contents of the cell based on the number in the cell.
I would like to move the content if it is greater than 5, but less than 70. If the content is between these, move it to a given location.
Is this an if/then? Ive got a few programming books here but not sure how to relate what I want to do to an example in the books.
View 4 Replies
View Related
Jan 9, 2007
I have the following values that I need to perform a calculation on, but I am not sure how.
A1 = -.98
A2 = .98
A3 = 1.0
I would like the results in colmun B to be:
B1 = 1.02
B2 = .98
B3 = 1.0
I would like to achieve this with a formula that I could fill down.
View 9 Replies
View Related
Mar 15, 2007
I need a formula, If the number is greater than 1 and less then 30, it returns Yes, if not, then no.
View 9 Replies
View Related
Jun 3, 2008
I have a huge sheet with data. I want to fix all values exceeding e.g. 2000 to 2000.
for instance:
400 --> 400
1600 --> 1600
2300 --> 2000
700 --> 700
3100 --> 2000
etc.
View 9 Replies
View Related
Jul 19, 2006
Here is my formula.
=If(Sheet1!J5=$J$4,0,(Sheet1!$J$4-Sheet1!J5)*Sheet1!$J$1)
It is perfect, except it doesn't have one final step. What I need it to do
is be able to do that ONLY if it is greater than or less than by a specified
amount. So there needs to be a modification of ... Sheet!J5<> (but by 100 or
any other number that I set) $J$4,...
View 4 Replies
View Related
Feb 25, 2007
I want to calculate a mark-up on one cell. If it is under a set amount then I want the mark-up to change. If it is then over a set amount but below another, then I want the mark up to change again, etc. I have got this far (eg. below) but the calculation does not work properly when the value in "A3" is over "1". =IF(A3<=0.49,A3/0.2,IF(A3>=0.5,A3/0.25,IF(A3>=1,A3/0.35,IF(A3>=2,A3/0.5,0))))
View 3 Replies
View Related
Mar 9, 2007
is there any way of using the =SMALL function to rank only numbers above zero so that the zeros don't keep showing up as the smallest figures?
View 2 Replies
View Related
