I need a formula that calculates the values of 5 seperate cells located in different places throughout my sheet. The value will either be numerical or display a text value (normally "RD"). If it displays text I do not want that value counted. So it literally just totals the number values.
View 7 Replies
I'm trying to sum up the total of a list excluding a list like this:
Column B
012*
019*
1758*
70228
70229
70230
70241
In my worksheet 1, it contains e.g.
Column AC
012-something
012-text
019-jumbo
016-things
70228
70221
Column W contains the numbers I want to add up:
$15
$12
$10
$19
$15
$11
My current formula is:
=SUMPRODUCT(--ISNA(MATCH(Sheet1!AC$4:$AC$157,B2:B7,0)),Sheet1!W4:W157)
It has managed to exclude the numbers (e.g. 70228, 70229, etc) but not the text strings (e.g. 012*) when summing it up
I have a speadsheet where I have a cell that reads a named range via OFFSET. The range consists mainly of numbers but a few cells contain the text UL (Unlimited)
The cell that "reads" the OFFSET is then used in another cell where it is multiplied by either 2 or 3 depending on user input.
The problem occurs where the cell attempts to multiply the text "UL" which results in "#VALUE!"
How can I have this cell with the multiplication bypass the multiplication and simply return the text "UL" in the case of reading text from the named range.
I've used the sumproduct formula very sucessfully in a workbook. The workbook is used to monitor discrepancies routed to other departments. Column U has the status of the discrepancy (Open, Closed, Cancelled etc). The below formula returns the number of discrepancies raised to a particular department. Now I need to tweak the formula to exclude values "Cancelled" found in range $U$119:$U:417.
=SUMPRODUCT(--(Register!$I$119:$I$417=$A4),--(Register!$C$119:$C$417=B$2),--(Register!$B$119:$B$417))
Is there a way to do a Formula (SUM) but exclude cells with invalid values? I would like to add up all values in attached spreadsheet for cells A14 to A28 and again for A49 to A63, as well as get an average C-14 to 28 and again for C49-63.
As some of the cells contain text rather than numbers, the formula doesn't work. how to exclude these cells?
Question is...in the below formula the red are pointing back to name ranges in a different tab on my spreadsheet. On that spreadsheet I also have a name range called DTCCSUBSUPPREF
I would like to add within my formula, a string which will point to that name range and exclude values if they fit my criteria. Basically I need to see values only where comp or tri dont show up. comp or tri could be the only value in the cell, or could be in the beginning of a string, at end, or in between. Below examples. I have tried (--(DTCCSUBSUPPREF<>"*comp*")*(--(DTCCSUBSUPPREF<>"*tri*") , and I would have thought the * characters would have served as a catch all.
Below is the entire forumla just in case.
11111111FT04comp
comp
tri
11comp
11tri
tri11111111FRP06
=SUMPRODUCT((--(DTCCSUBTRANSTYPE<>$G$2))*(--(DTCCSUBEVENTTYPE<>$I$2)*(--(DTCCSUBBACKLOAD<>$I$3)
*(--(DTCCSUBINPUTSOURCE<>$I$4)*(--(TEXT(DTCCSUBTRADEDATE,"MMM-YY")=$D$2))))))
having the formula exclude counting text in cells.
=IFERROR(SUMPRODUCT(--(INDIRECT("'"&TEXT(B$2,"mm-dd-yy")&"'!A4:A44")=Tracking!$A3)*(INDIRECT("'"&TEXT(B$2,"mm-dd-yy")&"'!D4:J44")>8)),"")
I would like it to only count numeric values over 8.0. Currently if I have text in a cell such as "PTO" it returns as a added value.
i have a worksheet filled with numbers in each cell (the worksheet might have a total of 150 rows and 150 columns of numbers).
the numbers typically range from 1-3 or 1-9 or 1-150.
however, there are also multiple 997, 998, and 999 numbers in the cells.
i need to identify the highest number in the worksheet that is not 997, 998, or 999.
I'm trying to figure out a formula to use that will exclude negative numbers. I have two dates. I'm trying to find the difference between the two dates assuming there is a date in the first column. At times there will be a situation where the date in A1 is before the date in A2 and it returns a negative number. I'd like to return a blank if there is a blank cell in column A and also return a blank if the return number is a negative.
Example:
A1 = 1/15/2013 B1= 1/10/2013 C1(formula) =IF(A1="","",(A1-B1) C1 will then reflect 5
A2 = (cell is blank) B2= 1/18/2013 C2 (formula) =IF(A2="","",(A2-B2) C2 will then reflect a blank cell
A3 = 1/5/2013 B3 = 1/15/2013 C3 (formula) =IF(A3="","",(A3-B3) C3 will then reflect -10
I'm trying to figure out a formula that would also allow C3 to reflect a blank cell since the formula returns a negative.
I am using Excel 07 with Vista, and creating a spreadsheet in which users will be inputting a record into every row.
There is a row that requires a number to be entered, where some of the numbers will begin with 0 or 00 or even 0000. I want to make sure no-one incorrectly enters a letter, however with data validation set to whole numbers, it formats the cell to change an entry of "005667" into just "5667". There is no set length for the field.
The only way to have "005667" is the change the column to text, but then obviously that defeats the whole purpose!
I'm trying to set up a dashboard with a table and some formulas so that I can just copy a data export into one tab and have the table perform various Vlookups, charts, etc. The trouble I'm having is that some of my cells contain a mix of text, and I'm looking for specific text only. Example:
Address |Tags | Result
Email 1 |t1 t2 t3 t4 | Email 1
Email 2 |t1
Email 3 |t2 impact1 | Email 3
Email 4 |impact2
Email 5 |impact2 t2| Email 5
My data source has thousands of rows, and I'm looking at the Tags column. I want to use a Vlookup that gives me the email when [Tags] contains "t2", but my search is getting confused with "impact2". Is there a formula to search for "t2", but not get confused with "impact2"? I considered using =NOT, but I don't want to exclude cells just because they contain "impact2". If there was a formula for =IGNORE, that would be marvelous. My formulas are getting longer & longer, but they always seem to break in the end.
Basically I'm trying something like:
=IF(OR(ISNUMBER(SEARCH("* t2*",Table1[Tags])),SEARCH("t2*",Table1[Tags])),"Yes","No")
(Once I get the IF figured out, I'll use Vlookup to bring out the Address).
I have an existing formula in a cell linking back to a start date only. determining how to add an additional formula to exclude weekends, and possibly holidays.
View 2 Replies View RelatedHow do I adjust this formula so it copies & paste special values rather than copying formatting etc? I am very new to this and I have looked at other examples but have found it hard to adjust my code using those examples.
VB:
Sub PrepayjournalKW()
'
' PrepayjournalKW Macro
'
Range("A6", Range("A" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Journal").Range("A1")
Range("B6", Range("B" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Journal").Range("C1")
Range("AB6", Range("AB" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Journal").Range("D1")
Range("AF6", Range("AB" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Journal").Range("E1")
End Sub
I have looked on here and I can find the minimum value in my range but what I am trying to do is find the minumum two values. Or rather, i want to find the average of the remaining values.
If the lowest value happens THREE OR MORE times, i still only want to exclude two of them. Same thing goes if the second lowest happens more than once, i only want to exclude at most two values from the avg.
currently, i have 12 values in my list.
example:
list --> one in each cell going across
25, 50, 87, 56, 99, 80, 81, 82, 50, 100, 98, 99
i have the answer as 83.2 doing it by hand. the 25 and one of the 50s would be dropped and the rest would be averaged.
I have a scenario where I have a rolling list of sales figures which get added to each week that passes.
I need a formula that will calculate the last 5 weeks of sales and generate an average - which I think I have an idea how to do.
The sticking point is that so as not to skew the averages, when there has been an exceptionally busy or quiet day for a reason we know about I exclude the sales from that week.
This then interferes with the averages as it either takes it as a zero and lowers it or seems to stop formulas from working.
So to summarise:
Average of last 5 weeks sales
Excluding any blanks
Dynamic enough to always pick up the last 5 values in the list (i.e. the last 5 weeks)
is there a formula to exclude #div/0! from a max value. say you have a column with a few #div/0! is there any way for the max not to be #div/0!
View 12 Replies View RelatedI have the following:
cell B2 value = "P1'08"
cell C2 Value = "P11'09"
cell H1 Value = "P4'09"
The value stands for the Periodnumber and a Year so P1'08 stands for period 1 in the year 2008.
In cell H2 I want the following:
If value in cell H1 is the same as B2 or C2 or is in between these periods then the value in H2 should be the value of cell D2. If not the H2 should be empty.
I've setup a macro button to sort some stockpile items from A-Z so entering quantity on hand can be done more easily. Each cell is a drop-down selection so at various times, there can be different stockpile items in the list, thus why, I setup a macro to quickly sort the list.
However, I've got some text (Select Stockpile Item) that I wish not to be included in the A-Z sort as this might put items starting with T or later at the end of the sort.
Sub SortMySpecialStockpileA2Z()
If MsgBox("Are you sure?", vbYesNo + vbQuestion) = vbNo Then
Exit Sub
Else
Range("K4:L110").Select
ActiveWorkbook.Worksheets("MyStockpile").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("MyStockpile").Sort.SortFields.Add Key:=Range("K4") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("MyStockpile").Sort
.SetRange Range("K4:L110")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If
End Sub
For example the formula below is in cell AU6. I want to exclude A6 from the array. Row AU7 would need to exclude A7 when I drag it down.
=MAX(IF($A$6:$A$493=AT6,$AS$6:$AS$493))
I need to count values of numbers in a row next to text(True ot False) Like:
A1____A2
True | 150
True | 66
False| 40
True | 78
False | 56
Output:
True: 294
False: 96
I have a document passed to me that includes information pulled from an internal system.
I want to use some basic formulas to show Totals, averages etc.
However, the SUM function does not work on the data provided unless I select the cell (As if to edit the text) and the press enter.
Is there a quicker way of selecting all of the cells in Column A for example and performing this function on mass, rather than selecting each cell individually.
I have attahced a similar sample copy - My SUM formula is show in cell B15.
I am trying to find a formula that will count the number of unique entries there. I have tried the solutions posted on various websites to no avail (most recently:
Code:
=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1:A10,A1:A10,0))>0,1))
).
The answer should be 4,457.
Ticket Number
T20110819.0527
T20110830.0339
T20110901.0060
T20110901.0060
T20110907.0042
T20110907.0042
T20110908.0186
T20110908.0186
T20110908.0186
T20110908.0186
[code].....
Another spreadsheet I use has a score within a text and i want to know how I can add up the scores easily.
In the example below I want to know If a Formula can return '9 out of 12'. I know this looks easy so why bother, but my sheet has far more rows/columns of data.
I thought of using Left and Right formula's to strip the numbers out.
Name
Score
A N Other
3 out of 4
A N Other
2 out of 4
A N Other
4 out of 4
I have been asked to create a worksheet to calculate the percentage of the utilisation of a number of loan cars per month. Each of the cars may be used on each and every day of the week but the percentage utilisation is to be calculated on working days only. However it is essential to record the name of the driver of the car at weekends and holidays. Column A is populated with the dates of the month. Colum B is populated with the relative days of the week. In column C and subsequent columns I record the name of the driver of a particular car registration. Is there any way that I can count the number of drivers of each car on working days only and not weekends or bank holidays?
View 5 Replies View RelatedNames
Quarters
Nickels
Dimes
Pennies
Record exc 0s
John
0
1
2
3
[Code] ........
In my table above, I would like to use the countif formula to count records based on the person, but I don't want the count to include values if the sum of quarters, nickels, dimes, AND pennies equal 0.
I have 16 columns in a single row that I am trying to apply to an array formula. That works, but I need to exclude all cells with the number "0" from the array range. I think I can do this myself in VBA but I don't want to run a macro every time something changes. Is it possible to do this in excel?
{=10*log(10^(A1:A16/10))}
It is possible that A1:A4 is a non-zero range and A12:16 may be a non-zero range. (Zeros will always be grouped in 4s)
I know I can use text to columns to do this. I have horse results listed in column A as follows 3.5L (i.e. 3.5 lengths), 4L and so on. Every number ends with an L. I want a formula in column O that just gives me the number as below:
3.5L 3.5 (in column O)
4.5L 4.5
and so on.
In A1 I have the following text: Distance: 457 meters
I want a formula N1 that isolates the numbers so it shows 457 only
I have below formula which gives me the due status.
[Code] ......
How can I modify the code so it excludes Saturday and Sunday.
The 3 vlookup will be in a single cell for concatenation like :
=vlookup1&"-"&vlookup2&"-"&vlookup3
Problem the are 3 vlookups where it will return names (with format -> Fname, Given Name Middle) and probably blank returns.
I have problem with the replace since there are also spaces between the Fname,Gname and Mname sample name with spaces.
