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,
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 count the number of rows that have values greater than 10/01/2008 in either of two fields. I tried following formula but instead of giving total number of rows, it returns a random date.
View 2 Replies View RelatedI have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.
I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.
Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.
I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.
I need to create a formula that takes into consideration 9 possible scenarios using IF and AND. I have 3 performance measures (exceeds requirements, meets requirements, improvement needed) and 3 potential measures (low, medium, and high). I have a spreadsheet where each individual is rated for both. Each combination correlates to a numeric rating on a 9 box grid. I need to include every option in my formula so the correct rating is determined for each individual. I've tried several versions on my own, and can't get past one set of conditions.
Performance PotentialRating
Exceeds ExpectationsLow4
Exceeds ExpectationsMedium7
Exceeds ExpectationsHigh9
Meets Expectations Low2
Meets Expectations Medium5
Meets Expectations High8
Improvement NeededLow1
Improvement NeededMedium3
Improvement NeededHigh6
Unacceptable Low1
Unacceptable Medium3
Unacceptable High6
I'm looking for a formula which will filter the area expiring in any given year per property. An example would be (from the attachment) - what percentage of area of Property 2 is expiring in 2010? Answer is 50.94%.
View 2 Replies View RelatedI 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'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 modify an existing nested vlookup formula to include one more condition. I attached the excel data file. There are two tabs:
Tab #1 - Performance
Column B (Email Send Date): can be a repetitive date, something like
Row 2. 4/25/2014
Row 3. 4/25/2014
Row 4. 4/25/2014
Row 5. 4/25/2014
Row 6. 5/2/2014
Row 7. 5/2/2014
Row 8. 5/2/2014
Row 9. 5/9/2014
Row 10. 5/9/2014
Row 11. 5/9/2014
Column F (Product ID): can be same product for different Email Send Date. For instance, Row 2 & Row 9 have the same product ID - 128 and Row 5 & Row 10 have same product ID - 131.
Row 2. 128
Row 3. 129
Row 4. 130
Row 5. 131
Row 6. 567
Row 7. 897
Row 8. 987
Row 9. 128
Row 10. 131
Row 11. 234
Column R: Units Sold - need to retrieve the units sold value from Column D - Units Sold in UnitsSoldOnlineVlookup tab.
The formula needs to lookup the Units Sold from a table in a different tab, named UnitsSoldOnlineVlookup. This table contains the following columns:
Tab #2 - UnitsSoldOnlineVlookup
Column A - Email Send Date
Column B - Product Description
Column C - Product ID
Column D - Units Sold
Before Product IDs were different for each Email Send Date and I successfully used this formula:
=IF(ISERROR(VLOOKUP(F2,UnitsSoldOnlineVlookup!$C$2:$D$31000,2,FALSE)),0,
VLOOKUP(F2,UnitsSoldOnlineVlookup!$C$2:$D$31000,2,FALSE))
Now I need to embed one more condition to this formula - lookup Units Sold for the Product ID as well as the email date:
lookup Units Sold for a Product ID for a corresponding Email Send Date in UnitsSoldOnlineVlookup table and return Units Sold into the corresponding cell in the Performance tab.
I thought to use MATCH function in addition to IF and ISERROR functions but I it doesn't work - I know it is wrong.
=IF(MATCH(B2,UnitsSoldOnlineVlookup!$A$2:$D$31000,0),
ISERROR(VLOOKUP(F2,UnitsSoldOnlineVlookup!$C$2:$D$31000,2,FALSE)),0,
VLOOKUP(F2,UnitsSoldOnlineVlookup!$C$2:$D$31000,2,FALSE))
I am needing a formula to sum info from 1 book to another based on two lookup criteria. Its the sort of info you can easily get with pivot tables or filters but my colleague needs a copy and pastable formula so it can be automated.
There are two workbooks Order Index example.xlsxCash Flow book example.xlsx; one called "Order Index" has a list of orders placed along with supplier name (Col C), date due for payment (Col H) and value of order (Col I). The other workbook, used by a colleague is called "Cash Flow" and contains a list of supplier names (Col B) with row 1 containing months (1st Jan 2014, 1st Feb 2014 etc).
Under each month on the "Cash Flow", I need to show the sum of orders due for payment within that month, for each supplier named (in Col B). In other words, sum the figures from the "Order Index" based on supplier name and date due for payment.
Often, on the Order Index there is more than one order per month from a supplier and the dates due are specific days (10th Jan, 21st Jan etc) and these need summing up for the 1st Jan 2014 Column in the Cash Flow book.
I have 2 columns of data being populated by vlookups
Column H is both numbers and text. Column I is Text and blanks. I need to be able to find only numeric values in column H greater than 0 and compare those occurrences with the corresponding cells in column I and if column I has a text entry (not a blank space) than to count that and at the end give me a total number of times these 2 criteria are met. As an example.
If column H has a text entry then don't count it.
If column H has a number less than zero then don't count it.
If column H has a number greater than zero but column I is blank then don't count it.
If column H has a number greater than 0 and column I has a text entry then count it.
I've tried using many variations of a sumproduct formula and none of them work.
This formula counts all instances where column I has a text entry without checking column H for a number greater than 0.
=SUMPRODUCT(--(H2:H110>0),--(I2:I110<>" "))
Or it's possible that the formula is counting the text entries in column H as a number greater than 0 but I'v tried excluding text using this..
=SUMPRODUCT(--(H2:H110>0&<>"*"),--(I2:I110<>" "))
but this causes an error in the formula somehow that I can't figure out. I even tried this
=SUMPRODUCT(--(H2:H110>0&"*"),--(I2:I110<>" "))
and I get a formula that counts only the times text appears in column H and column I together which is not what I want either.
I'm self-taught on Excel so I know there's a lot I'm not understanding about creating formulas like this but I need to have this working by Friday and I just want it to work.
Can anyone tell me what is wrong with the formula on page 1 cell F56? it is not adding the 666 gallons at the end of column F for 426 - cc. Any ideas?
View 11 Replies View RelatedHow could I add ">0" to the below formula array?
{=IFERROR(AVERAGE(IF(Reports!$A$1:$A$4000=A5,Reports!$H$1:$H$4000/86400)),"-")}
Reports!$A$1:$A4000 = column A lists multiple agent's names
A5 = a specific agent's name
Reports!$H$1:$H$4000 = column H contains a number which has been converted from time to a number
86400 = number of seconds in a day (the cell where I am placing the formula converts the number back to a time format)
The average I'm looking for will give me an average of all times which are greater than zero.
Attempting to calculate assignment hours based on three fields -
A - Date
B - Txt
c- Txt
D - Time [Start]
E - Time [END]
F - Number [Off]
G - Number [Travel]
H- Number (Calculated Total Project Hours)
I HAVE THE CALCULATION WORKING WITH THIS FORMULA
=(E5-D5)*1440/60-F5+G5
My goal [If Activity is NOT NULL and Start Time is NULL] Time would Default to 8.0 - ELSE use the formula to calculate [ Some projects I don't have a set start/end - and default to 8.0 hrs.
I hope someone can help me with adding an additional criterion to the formula below, which works and provides an accurate answer. The array formula is:
{SUM(IF(FREQUENCY(IF('Sheet1'!$O$5:$O$315="",IF(Sheet1'!$B$5:$B$315"",MATCH("~"&'Sheet1'!$B$5:$B$315,'Sheet1'!$B$5:$B$315&"",0))),ROW('Sheet1'!$B$5:$B$315)-ROW('Sheet1'!$B$5)+1),1))}
Where Column O has either a date or is blank.
Where Column B has a unique ID number which occurs more than once.
What I need to add is one more criteria from Sheet1,
Where Column P has a unique names which occurs more than once.
I tried two changes:
1. Adding an additional IF statement as follows: SUM(IF(FREQUENCY(IF('Sheet1'!$P$5:$P$315=A$8,IF('Sheet1'!$O$5:$O$315="", ... Which I couldn't get to take; and
2. Adding a IF AND statement as follows: SUM(IF(FREQUENCY(IF(AND('Sheet1'!$P$5:$P$315=A$8,'Sheet1'!$O$5:$O$315=""), ... Which took but gives me a #VALUE error.
I am looking for a formula that averages the numbers in an array if they match the row and column text-based criteria. Based on another thread, I found and edited the following formula. However, it is giving me incorrect numbers.
[=AVERAGE(IF(($A$3:$A$275=$P6)*($B$2:$M$2=Q$4),$B$3:$M$275))]
I have attached a sample workbook that includes the broken formula.
Average Formula Error.xlsx
I need an array formula that can take average based on multiple criteria present row wise and column wise.
Check the attached sheet for detail. I need array formulae in yellow space. I have given one result that I need.
Average.xlsx
In the attached I have a pricing list on sheet 2 based on various criteria and on sheet 1 dropdown lists to match the criteria.
In cell B12 i have an index match that I can not get to return a value.
Is Index Match the correct method of returning a result? Or have I just got the formula wrong?
What I require : Column F to be blank as long as the following is true:
1) Column A (Order) cannot be blank.
2) Column E (Status) has to be equal to either "Completed" or "Successful".
3) Column D (Sales) has to have the same date as Column C (Contact), but due to bad data quality each order number can have several rows and as long as the Sale date from one of the other "Duplicate" rows equals the contact date it needs to be counted. This is as long as there is not already a row for the Order that already has Sales / Contact day matching.
In the attached spreadsheet, I have highlighted the rows that should be blank in column F in green. My attempts at formulas to count this are in the columns highlighted in red.
WORKAROUND.xls
Fixed data labels over a number of columns and i want to count the number of times 2 of these appear however when ive tried to use the countifs function it will only count if in the first colum of the range selected and for example if A2 says 'FRANCE' and D1 'RED'
Ie i have been using COUNTIFS('Datasheet'!BB:BE,A1,'datasheet'!BE:BH,D1)
In the attached vacation accrual spreadsheet, I created two tables: Exempt hours earned, and also, Non Exempt hours earned. I have all employees on one worksheet. The hours accrued (Vacation Level) are different for Exempt and Non Exempt. I need: What is the best formula to autofill the Vacation Level column (H):
It needs to choose exempt or non exempt from column (C) then reference the # Years of Service column (F) to find the correct vacation days earned as defined in the two tables on the right, or those numbers could be used in the formula itself. . . I JUST DON'T KNOW! Nothing I am trying is working!
create a formula! Use the spreadsheet to maybe make name ranges? nested IF statements, or LOOKUP, or even VLOOKUP??? Or something that works. not to repeat, but. . . Nothing I am trying is working!
Is it possible to use an Index/Match formula that looks at multiple criteria?
View 1 Replies View RelatedI'm working on a formula for calculating shipment weight per city it goes to per weight scale of the shipment pricelist. Although we have 4 customers with customer codes which we ship with our own trucks and some customers pick up there goods.
Therefore, my sumifs formula currently looks like this:
=SUM(SUMIFS('Freight SH 13'!$R$7:$R$14176,'Freight SH 13'!$R$7:$R$14176,"
I am trying to do a sum of data that contains three requirements - The country has to match either the USA or Canada and then it needs to pull for only a certain month (i.e. Jan, Feb, Mar) and then for that month only pull either Airfare, Hotel, Per Diem, or Car Rental. Basically looking for a way to Sum only the specific category for a specific time frame.
View 9 Replies View RelatedI am creating a summary page to analyse my golf scores. I have come to a dead end when looking at the Par statistics. Does anyone fancy a challenge and try to fill in some of the blanks on the summary tab. I am trying to use array formulas based on multiple criteria. for instance under the Best Par 3 section, it would look at all the Par 3's for whichever golfer is selected and then return the lowest number.
View 4 Replies View RelatedI am trying to get my INDEX & MATCH formula to retreive data from my table.
This is what I can do so far:
Jan-07Feb-07
100 12
250 45
=INDEX(table,MATCH(B13,balance),MATCH(C13, date))
But I am trying to get it to get another row to look up as well.
I want it to look up the color then the 100 or 250, then the date.
Jan-07Feb-07
red100 12
red250 45
blue100 78
blue250 1011
=INDEX(table,MATCH(B13,balance),MATCH(C13,date))
I think i need to insert another match code in the row section but cant seem to get it to work.
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.
Attached worksheet which is an sales incentive plan. I need cell B69 to represent the amount in b68 unless neither b30 or b99 are > 0. In other words if even 1 of the cells b30 or b99 come back positive, you get the amount in b68. If it comes back 0 in both cells b30 and b99, then they get 0.
View 4 Replies View RelatedHow to formulate results of students in excel sheet.
From the attached picture (capture1.jpg) of the excel sheet - The rules of exams are:
1. if candidate scores 50% in all 4 papers, PASS and proceed to next year
2. if candidate scores 50% in 2 or 3 papers + borderline fail in 1 or 2 papers, VIVA VOCE exam for the borderline failed paper (Definition of borderline fail is candidate scoring 45 to 50 marks)
3. if candidate scores 50% in upto 2 papers + borderline fail in more than 2 papers, RE-EXAM
4. if candidate scores 45% in 2 or more papers, FAIL and repeat the year
The rules are in the attached picture flowchart.jpg
I am unsuccessful in writing a formula for such multiple criteria...
I am trying to populate some results using countifs formula with multiple criteria. below is the formula am using and the last two criterias are dates
=COUNTIFS('Unproccessed CIT'!$B$5:$B$411,Summary!$A$4:$A$35,'Unproccessed CIT'!$G$11:$G$417,"<>"&"closed",'Unproccessed CIT'!$A$5:$A$38,">="&$C$2,'Unproccessed CIT'!$A$5:$A$38,"<="&Summary!$E$2)
Results:
#VALUE!