Sum The Range Of Data If Two Conditions Are Satisfied
Jul 31, 2008
My data is as follows
MonthMilesAmountLocation
June 8£3.20 Loc1
June 8£3.20 Loc1
June 8£3.20 Loc2
July 8£3.20 Loc2
July 8£3.20 Loc1
July 38£15.20Loc1
July 4£1.60 Loc2
Aug 38£15.20Loc2
Aug 4£1.60 Loc1
Aug 8£3.20 Loc1
Here is what I want to do
- Avoide filtering and manually calculating data for the total expense in the month
I want to SUM all the (3)AMOUNT in the (1)MONTH of JUNE for
(4)LOCATION LOC1
I know its too much to ask for but can you suggest if i can combine SUMIF or SUM(IF) or any other formulas ???
View 10 Replies
ADVERTISEMENT
Mar 15, 2014
I am trying to pick certain data from a table and sum it in order to produce a formatted report.
The data has many different items of which I want to pick certain ones to sum on one line
See attached and some of the formula's that I have used, but aren't quite what I'm after
The formula I want to use is .... VLOOKUP(E5:G5,A14:C23,3,) ......
But it wont reference a range (E5:G5) !!! and I also want the formula to ignore blanks (as blanks in the data file are actual totals).
View 2 Replies
View Related
Mar 24, 2014
I am trying to compare 2 conditions to see if one or other is correct and if so out put ok if correct and wrong if incorrect i tried this formula but does not work.
IF(OR(AND(j3="UP",t3=1),(AND(j3="DOWN",t3=-1),"ok","wrong")
View 4 Replies
View Related
Apr 23, 2009
refer to attached worksheet.
I need a way (Non-Macro please) - where if a condition is met, then fill a range with a particular value. The attached spreadsheet has a sample with explanation.
View 9 Replies
View Related
Jan 22, 2013
I run a small business, and I use Excel to track my books especially receivables.
I have a list of Invoice numbers and Invoice amounts for every customer.
My customers pay periodically (every two weeks, etc), but the tricky part is that the amount paid may not necessarily reflect the exact amount of invoice still being owed. And there may be two, three or five invoices outstanding at any one time.
I have another column which contains payment amount made by customer.
What I would like to do is to highlight the INVOICE NO. cell once the amount of that particular invoice has been bypassed by the total amount paid (i.e. the full amount of that invoice has been paid). Then I can close that invoice.
Here's what my list looks like roughly.
INV NO. - INV AMOUNT - AMOUNT PAID
INV1 ------- 100 ------------- 80
INV2 ------- 250 ------------- 90
INV3 ------- 150 ------------- 80
TOTAL ----- 500 -------------270
View 13 Replies
View Related
Apr 25, 2008
if anyone out there had some code that would delete an entire row if a cell contained a value either = or > 0. I have attached a sample of my spreadsheet it contains two worksheets and I need the same task performed on each. The list may differ from day to day in length so I would need to work from the bottom of the worksheet up and I would only need it to work up to and including cell D4. The column that I need to take the action on is D and the heading is MaxDebit2.
View 6 Replies
View Related
Aug 7, 2014
I am trying to create a graph of my data at the end of each month. The graph will show a count of the number of instances of each category in the data.
For example:
A1 =TODAY()B1 =MONTH(A1)
Column A from A3 down = 1,2,3,4,W,H,L
Column I between I2 and I140 = multiple instances of 1,2,3,4,W,H,L
July
=IF($B$1=7,(COUNTIF($I$2:$I$140,$A3)),"0")
=IF($B$1=7,(COUNTIF($I$2:$I$140,$A4)),"0")
=IF($B$1=7,(COUNTIF($I$2:$I$140,$A5)),"0")
=IF($B$1=7,(COUNTIF($I$2:$I$140,$A6)),"0")
=IF($B$1=7,(COUNTIF($I$2:$I$140,$A7)),"0")
=IF($B$1=7,(COUNTIF($I$2:$I$140,$A8)),"0")
=IF($B$1=7,(COUNTIF($I$2:$I$140,$A9)),"0")
=IF($B$1=7,(COUNTIF($I$2:$I$140,$A10)),"0")
My problem is that when it comes around to August or the next month, I want the final count of July to be fixed/frozen so it won't change from the 1st of the month. I can then use the July column's data for my graph.
Can a formula convert itself into a value or freeze itself after certain criteria has been fulfilled?
View 7 Replies
View Related
Aug 17, 2009
I have random "X"s in a column of cells in a range called "Won". I wish to count these if the adjacent cell in another range alled "Valu" is greater than a value determined in the cell E3. The result to be entered in Cell G4. Can use Excel formulas or VBA.
View 4 Replies
View Related
May 15, 2012
How to get the MAX of a range if 3 conditions are met.
1 after this date
2 before this date
3 Column G is equal to A2
Ranges are on sheet1
Date reference is on sheet2
range to sum is on sheet1
V14.
something like
MAX sheet1!H:H
IF sheet1!$G:$G equals $A2
AND sheet1!$P:$P,">="&EOMONTH($B2,-1)+1
AND sheet1!$P:$P,"
View 7 Replies
View Related
Aug 16, 2009
I have a named range “Value” and wish to count all the values over the value set in cell E3. The result to be entered in cell F4.
I have a second named range “Won”, adjacent to “Value”, and wish to count these values if the adjacent cell in the “Value” range has been counted. The result to be entered in cell G4.
View 2 Replies
View Related
May 15, 2012
How to get the sum of a range if 3 conditions are met.
1 after this date
2 before this date
3 Column G is "OTH"
Ranges are on sheet1
Date reference is on sheet2
range to sum is on sheet1
V14.
Present formula is:
=SUMIF('sheet1'!$P:$P,">="&EOMONTH($A2, -1)+1,'sheet1'!$H:$H)
-SUMIF('sheet1'!$P:$P,">"&EOMONTH($A2,0),'sheet1'!$H:$H)
This works fine but lacks the 3rd condition 'sheet1'!$G:$G ="OTH"
Advise given was to go on SUMPRODUCT but unless with (OFFSET(INDIRECT, SUMPRODUCT wouldn't work on multiple sheets. And this is for version 14...
View 5 Replies
View Related
Aug 10, 2009
I need a macro that will delete a range with some certain conditions. I have a set of dates in column B. The dates are in the format of 2009-01-31 09:15:00.
I need to search this column to find all the dates from the day before the computers date to the first day of the month before the previous days date. For example my computer date right now is August 10, 2009. So i need the macro to find all the dates from August 09, 2009 back to July 01, 2009 and select the entire rows that meet this criteria. Then I need to delete those rows.
Tomorrow when I run the macro it would choose all the data from July 01 - Aug 10, and this will go on each day until the end of december.
View 9 Replies
View Related
Dec 6, 2007
I have written a function below which is supposed to give average of selected range and will ignore #N/A, if any. Naturally, i use array formula:
Function m_avg(mRange As Range)
m_avg.FormulaArray = "=AVERAGE(IF(ISNUMBER(mRange),mRange))"
End Function
the above doesnt work and gives me #VALUE!.
View 3 Replies
View Related
Jul 27, 2012
I have a spreadsheet which has data in rows. I need to sum the first 5 values from the left of the range which are greater than zero. BUT if there are less than 5 non zero values present I want to sum from the right of the range.
First condition:
1 2 3 4 5 6 7 8 9 10
The function would return: 1+2+3+4+5 = 15
Second condition:
0 0 0 4 5 6 7 8 9 10
Function would return: 4 + 5 + 6 + 7 + 8 = 30
Third condition:
0 0 0 0 0 0 0 8 9 10
The function would return: 10 + 9 + 8 = 27
I have had success with:
=B4+SUM(SMALL(IF(I4:O4<>0,I4:O4),ROW(1:5)))
(using ctrl shift enter)
BUT it crashes when faced with the third condition.
I would also like to avoid ctrl-shift-enter functions if I can as I don't have much success copying them around the spreadsheet.
View 3 Replies
View Related
May 14, 2014
1) Input data are static and helper columns can be added if needed.
2) Filter will be dynamic range (in attachement is the filter static), and the count of years can be changed on users request. So there could be only 2011, but also 2011+2012, 2012+2013+2014 etc.
3) In col 'J', the is what I know to do, but I do not want to use SUMIF+SUMIF+SUMIF... for each year (the count of years will change througt time as mentioned above).
View 3 Replies
View Related
Dec 17, 2012
I'm trying to code a clean up of some data ranges ready for priting / reporting. I need to move filtered data from the source sheet, to a new one.
The issue is, that I need to apply some logic to the rows I filter out, not something I can do with autofilter...
- Only copy rows including and BETWEEN the first and last rows where total count in col 8 is > 0
So in example1, I'd want to copy out rows P6 - 1A
BUT
- Always copy at least 5 rows when there is one value > 0 in col 8
So in example2, I'd want to copy out rows P5 - 1C, ideally...
Total
Boys
Girls
Total
[Code]....
View 3 Replies
View Related
Nov 13, 2006
I'm creating a summary sheet that adds up all the data on sheets between start and finish where A2-A25 is SU and B2-B25 is 004*04. Background info: SU stands for SetUp, and 004*04 is a workcenter number (its not 004 times 04) I'm trying to calculate the total number of Setups for each workcenter.
=SUM((start:finish!A2:A25="SU")*(start:finish!B2:B25="004*04"))
gives me a #NAME? error.
Example Data on each spreadsheet:
A B
SU 004*02
SU 004*04
SU 004*04
SU 004*02
SU 004*04
View 9 Replies
View Related
Dec 7, 2006
In the screen shot I'm trying to find the row number where a particular price of an order has been reached. In this case, for the first order, my execution price is 1.8859, my stop loss is 1.8834 and take profit is 1.8884. I need to look and the future prices to determine which event had occured first (either the take profit or the stop loss). I though by using row numbers I would compare and which ever is smallest would mean that it occured first - the profit/loss is then calculated.
The other caveat is that an exact match may not always be available - for example, the second trade is stoped out because the highest price for the 12:35 timeframe exceeds the value I'm looking for. Still it would have triggered a stop loss.
******** ******************** ************************************************************************>Microsoft Excel - Misc.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutH6I6J6M6H7I7J7M7H8I8J8M8I9J9M9I10J10M10I11J11M11I12J12M12=
ABCDEFGHIJKLM3DateTimeOpenHighLow*Order*PlacedOrder*PriceStop*LossTake*ProfitStop*Loss*Row*#Take*Profit*Row*#Profit/Loss42006.11.1512:001.88651.88661.8863*N***these*are*the*cells*that*need*the*formula*52006.11.1512:051.88651.88661.8856*N******62006.11.1512:101.88591.88591.8857*Buy1.88591.88341.88841080.002572006.11.1512:151.88581.88591.8853*N*...............
View 10 Replies
View Related
Sep 5, 2006
I have this macro to copy a range to another worksheet:
Sub transpose_UPCID() ' Hace el copy y paste transposed en BAUCS de los numeros de UPC"s y
' las cajas ATR
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Sheets("Sales-Inventory").Range("UPC", Range("MK_ID").End(xlDown))
Set rng2 = Sheets("Sales-Inventory").Range("Figure", Range("Figure").End(xlDown))
rng1.Copy
Sheets("BAUCS").Range("C11").PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
rng2.Copy
Sheets("BAUCS").Range("C37").PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = True
End Sub
The only problem is that this code copies all the items and I need this code to look into Column A in the Sales-Inventory worksheet. There I have a data validation with a total of six options. I also have six differents worksheets where I need to copy the values based on that data validation. To give you and example, all items containing XX in column A copy to XX worksheet. Something like that.
View 9 Replies
View Related
May 16, 2007
Please take a look at this sample spreadsheet.
[url]
I am trying to find out how to write a formula that will sum the values in one column if the value in a corresponding column is within a range. For example, if the item number in the first column is greater than 2 and less than 7, the sum would be 179. I believe this would be some type of SUMIF but I cannot write it.
View 3 Replies
View Related
Dec 7, 2007
I have a VERY complex custom data validation formula that is getting crazy. I thought it might be easier to use a user defined VBA function to handle it, but was unsuccessful.
I was able to write a VBA User Defined Function & use it within my spreadsheet to derive the value of another cell, as follows: =IF(OR(ISBLANK(B12),myValidation(B12)),"OK","NG")
I tried using it as a Custom Data Validation on the cell itself (=myValidation(B12)), and I get an error: "a named range you specified cannot be found".
Can the function be used in this way or could someone suggest another way of handling this?
View 6 Replies
View Related
Mar 15, 2013
Assume this Data in a spreadsheet
I want to count blank "PI" based on "Product name" & "Product date" occurence together
So I created intermediate field "IS Blank", and I dunno what formula can give me the below results
My obective to get this result
CountBlank for PI = 3
--------------------------------------------------------------------------------
Product Name..... Product Date.... PI...... IsBlank "PI" [Desired Formula output]
xxx .......................ddd ...............Blank.................. 1
xxx .......................ddd ...............Blank.................. 0 (counted above for same xxx&ddd)
xxx .......................ddd222 ..........Blank.................. 1 (PD changed to ddd222)
yyy ......................ttt............... Blank....................1 (another product,yyy)
yyy .....................ttt ...............Blank......................0 (same product and date, so not counting again)
View 9 Replies
View Related
Jan 29, 2009
I want to auto fill certain columns in sheet A from sheet B & sheet C based on conditions
For Eg:
Sheet A:
Position#
Position Name
Personnel Name
Personnel No.
AB1
Manager
AB2
I want to auto fill in Sheet A for the columns:
Personnel Name
Personnel No.
Conditions:
If the employee is active in Sheet B then fill in Sheet A to the related position No.
Else
If the employee is mapped to org. (Yes) in Sheet C then fill in Sheet A to the related position No.
Else
Vacancy
i have written the below formula but not working properly:
=IF(VLOOKUP(SheetA!A4,Direct!A$2:K$16,5,FALSE)="Active",VLOOKUP(SheetA!A4,Direct!A$2:K$16,3,FALSE),IF(VLOOKUP(SheetA!A2,Contractors!A$2:K$15,5,FALSE)="Yes",VLOOKUP(SheetA!A2,Contractors!A$2:K$15,3,FALSE),"Vacancy"))
View 9 Replies
View Related
May 30, 2008
I have a rolling calendar worksheet that I need to create a formula to auto populate the sum of 8 weeks out, 4 and 8 weeks back based on any work week date I may plug in. So on the "summary page" based on a work week date, I need 8 weeks out, 4 and 8 weeks back projections to be auto summed for 10 different part numbers. The "13 week rolling" page will continue to be updated so that everyweek another week is added and the last week will fall off. (this part can be done manually if no function can delete the oldest column) When the weeks are added it must include the workweek in question. i.e. ww25 = (8weeks out) ww25:ww32 / (4weeks back) ww25:ww22 / (8weeks back) ww25:ww18. see example workbook attached.
This has been driving me nuts for a few weeks now.
View 2 Replies
View Related
Apr 23, 2008
I am trying to return a cell address range based on values in different columns. Attached is an example.
In Column A1 I have "Range" and below that a set of numbers from 1 to 31.
In Column B1 the name "Test" is used as an identifier, below that are the letters "a" though to "e". Once the letter "e" is reached the identifier "Test" is moved to column C "C6" and the alphabet continues from "f" down to "j". This process is repeated through the rest of the alphabet and the identifier moved to the next column after every 5th letter.
What I want to do is as follows:-
I would like to know the cell addresses of all instances of "test" from Column A numbers 6 throught to 24. The result should be C7:C12, D14:18 & D20:24. The results should ignore all instances of the identifier "test" and only return the cells which contain the letters of the alphabet.
I have tried using Vlookup which works well if the letters are all in the same column and I have incorporated the Address and Match formulas to return the range if all the alphabets are in the same column, but I do not know how to do this if the identifier "test" and letters move to a differnt column.
View 9 Replies
View Related
Mar 14, 2008
I'm trying to populate a dropdown Combo Box by using conditions.
In the example I attached...I would be trying to use ComboBox1.ListFillRange to populate the ComboBox only with players who have "C" in the Position column and "ANA" in the Team Code Column. Which would give me all the Catchers on the Angels.
I've been trying to use worksheetfuntion.Index.
View 4 Replies
View Related
Oct 17, 2007
I would like to highlight cells is two conditions are met:Cell = 0Offset(0,-1)>0I tried the conditional format wizard and entered a formula: =IF(AND($J2=0,$I2>0)) But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard
View 2 Replies
View Related
May 17, 2014
I'm trying to set up a sales tracker that will list items sold and corresponding account number of each sales call per day .
On sheet 1, I have the "tracker". A1 date, A2 account number (manually filled in). A5 to A15 product codes (numbered). B5 to B15 each product sold for that account.
On sheet 2, I have columns for each day of the month. In row 1 each column has each days date for that month. I would like to be able to pull in data from sheet one (the "tracker"). For example on the date 1/2/2015 each time a sale is made to a different account number that day and entered on sheet 1 it would be automatically entered in sheet 2 in the column with the matching date heading in row 1 (for example 1/2/2015). It would first show the account number, then each product that was sold to that account.
There could be around 20 different accounts per day each with around ten different products sold per account per day. Each time the "tracker" contents on sheet 1 are cleared to make room for a new sale(s) that day, the sale data on sheet 2 would not be over written or deleted. The next sale that day would start under the previous account entry. If each account number could have a colored background that would be useful in quickly locating a specific account number, but not absolutely necessary.
View 2 Replies
View Related
Apr 13, 2014
I am trying to copy data from one spreadsheet which is updated daily to a Master spreadsheet. But when the data is copied to the master spreadsheet it compares the data and if the data is missing from the daily sheet that row gets moved to a 'achieve' tab within the workbook.
Scenario: We get a daily spreadsheet with outstanding jobs this gets copied to the master spreadsheet. But when copying the data to the master spreadsheet it check to see if todays daily sheet matches the master sheet. If any data is missing this mean the job has been completed yesterday so the row needs to be moved to a tab called archive in the master spreadsheet.
trying to automate process: Copy data from daily spreadsheet to master spreadsheet -> vba/macro - compares data copied in to (master spreadsheet) see if any jobs are no long on the daily sheet = (jobs been completed) -> IF this is the case move row of data to achieve tab in the master spreadsheet.
View 1 Replies
View Related
Nov 25, 2006
I have 4 columns of data:
Employee; Invoice #, Invoice Total ($), Month of Invoice
I am trying to create a simple report that will display Invoices, By Employee, for a given month. I have tried using the following VLOOKUP statements within an IF statement:
=IF(VLOOKUP(H2,S1DATA,4,FALSE)="OCT",VLOOKUP(H2,S1DATA,2,FALSE),0)
"S1DATA" is the table with the data I described above. I am trying to retrieve data for the month of October. When I use the above statement I get the information that I want, but when I copy the formula down (matching the employee name in H2, H3, H4... etc I continuously get the same invoice information.
View 9 Replies
View Related
