Assist me in getting the following statement into Excel 2003 format - it is working OK in Excel 2007.
=COUNTIFS('Sheet 1'!$AB:$AB,"True",'Sheet 1'!$F:$F,'Sheet 2'!B13,'Sheet 2'!$J:$J,">="&H2,'Sheet 1'!$J:$J,"
I am looking for a replacement to the function COUNTIFS that will work with Excel 97-03. I have attached a sample of the document which will hopefully demonstrate what I am trying to achieve. On the sheet labelled 'Data' there is four headings (MerRefs, JCPRefs, Self Refs and Other) for the results beneath these headings I have used the Countifs function. I have attempted to use the SUMIF as an array formula but couldn't get this to work.
Also in the hope of killing two birds with one stone, I created this in Excel 2007 and in the past when I have saved documents as 97-03 documents it used to warn me of any incompatible functions included within the doc and now it doesent. Where can I switch that setting on?
I have the following excel formula:
=LOWER(IF(($H2&":"&$M2&":"&$N2&":"&TEXT(COUNTIFS($H2:$H$2,$H2,$M2:$M$2,$M2,$N2:$N$2,$N2),"0000"))=": ::0000","",$H2&":"&$M2&":"&$N2&":"&TEXT(COUNTIFS($H2:$H$2,$H2,$M2:$M$2,$M2,$N2:$N$2,$N2),"0000")))
I need to convert the COUNTIFS into something that is Excel 2003 friendly.
I am having trouble converting the following Countifs formula developed in 2007 to work in 2003 Excel.
Basically, I want to count the frequency of "N" in 'Data_Q2 2008'!$S$2:$S$9806, where 'Data_Q2 2008'!$B$2:$B$9806 = 'Metric Upload'!C2.
'Data_Q2 2008'!$B$2:$B$9806 is a list of physicians. 'Metric Upload'!C2 is a pick list for selecting the physican that is in the larger data set using Data Validation.
I have attendance tables that has column Y showing the shift to be worked and column Z showing any absentees. I used the following formula to count the number of people off per period.
{=SUM(COUNTIFS($Y$5:$Y$29,AM,$Z$5:$Z$29,$W82),(COUNTIFS($Y$36:$Y$60,AM,$Z$36:$Z$60,$W82)))}
in the above formula AM relates to a list of morning shifts.
The problem I have now encountered is that the system at work is still using excel 97 - 2003 which does not recognise Countifs.
way to adapt the formula to be compatible with Excel 97.
I created the following in Excel 2007 but need to come up with a replacement formula in 2003.
=COUNTIFS('Data High'!AM2:AM999,"0",'Data High'!AN2:AN999,"Non Corp",'Data High'!AL2:AL999,"No")+COUNTIFS('Data Medium'!AI2:AI2000,"No",'Data Medium'!AJ2:AJ2000,"0",'Data Medium'!AK2:AK2000,"Non Corp")+COUNTIFS('Data Low'!AA2:AA5000,"No",'Data Low'!AB2:AB5000,"0",'Data Low'!AC2:AC5000,"Non Corp")
With this spreadsheet i pull data straight from a database and place it in to 3 separate worksheets. The first worksheet which is the summary sheet then updates all the totals of certain occurrences. The above formula is the same format for all fields in the summary with a few variations. But they all have the following in common. They draw data from all 3 worksheets and total it all up, and each countifs looks at 3 different cells.
In terms of replacing it i have tried to use something like =SUMPRODUCT(--('Data Low'!AA2:AA5000="No")*('Data Low'!AB2:AB5000="0")*('Data Low'!AC2:AC5000="Non Corp")) but don't seem to be getting it right.
I have a formula to get unique values from a column....Can anyone help me to re write with the funcations in attached file...
Formula...
=IF(ROW()-3>$B$22,"",VLOOKUP(ROW()-3,$B$4:$C$22,2,FALSE))
Macro is not allowed...
I'm trying to take a random sample without replacement. The sample is also partially representative, e.g. if a certain proportion of the data are Class A and a certain proportion is Class B, that proportion is maintained in the sample. After the proportions are set, the observations within each class are randomized.
Is there anyway to sample a number of rows, where the number of rows is based on the value in another cell, without having to use a lookup function or index match? Sorry if the question is a little unclear; please let me know if you need any clarification.
I have several walking routes that are labeled A through H. Each walking route has a different distance. .18 miles, 1.45 miles, etc. What i would like to do is be able to enter what routes are walked in a day and have it display the milage in a total cell. Example......
View 4 Replies View RelatedIn the attached spreadsheet, I want to do a running average of the "To" column (column G) for each company throughout the 8 year period. The only problem is I want to do it with replacement based on column D, meaning if the same brokerage firm comes out with a new announcement, it will replace the value of the old one in the running average calculation.
Here is an illustration of what I mean:
Looking at Company A between row 2 and 12, the value for Consensus in row 2 should be the average of Column G in row 2, row 3 is the average of Column G in row 3...row 11 is the average of Column G in row 2 to row 11, but row 12 will be the average of Column G in row 2 to 12 be excluding column 5's value because both column 5 and 12 are issued by the same brokerage.
I want excel to replace new recommendations by brokerages over the old ones in the running average calculation.
What can I use to replace the portion in red because it is volatile?
=IF(A3="N","",RANK(B3,$B$2:INDIRECT("$B$17"),0)+COUNTIF($B$2:B3,B3)-1)
We have two characters : One debit shown as Dr
Another Credit shown as Cr
we have an excel sheet containing a problem as under:
row 1 row2
column 1 Sales 345670 Dr
column 2 Receipt 340000 Cr
column 3 TDS 5670 Cr
We have replaced the Suffix so that it looks as under:
Sales 345670
Receipt 340000 Cr
TDS 5670 Cr
Wewant the last two lines to have figures in the negative ie - 340000
- 5670
i.e. cr suffix change to negative figure -340000
I have a cell with a url in it, Say in A1 I have the following url:
mrexcel.com/forum/forumdisplay.php?f=10
I want Cell B1 To copy what's in cell A1 but delete everthing after the backslash"/" In B1 want it to show: mrexcel.com
What I have now doesn't work, which is:
SUBSTITUTE(A1,"/*","")
An attempt to use a wildcard (*) to remove everything after the backslash. Doesn't work
What's the correct formula that will allow me to do this automatically?
I have some code to insert a picture based on the value of a cell. The user would select the name of the item by selecting the cell and then clicking a macro button.
In my code I need to delete the previous picture first. I used the following
Sub get_system_picture()
Dim pic As Shape
Dim picname As String
picname = ActiveCell.Value
On Error Resume Next
'exit sub if selection is not a system number
If IsEmpty(ActiveCell) Then
MsgBox "Please select a system number first!"
Exit Sub
End If
If Not ActiveCell.Column = 2 Then
MsgBox "Please select a system number first!"
Exit Sub
End If
The problem is this also deletes my macro button! Any suggestions?
I compile ratings for racehorses and am currently extracting the data I need using an array formula. Unfortunately there are are many thousands of lines of data and it can take quite some time for the data to pull through.
Here is a sample of the formula's I am currently using. I cannot take credit for the formulas by the way I found them on the web.
IF(ISERROR(INDEX('Database 2012'!$A$1:$B$300,SMALL(IF('Database 2012'!$A$1:$A$300=Ratings!$A3,ROW('Database 2012'!$A$1:$A$300)),1),2)),"",INDEX('Database 2012'!$A$1:$B$300,SMALL(IF('Database 2012'!$A$1:$A$300=Ratings!$A3,ROW('Database 2012'!$A$1:$A$300)),1),2))
Basically I have 2 sheets (Database 2012, Database 2011) with the horse names with ratings next to them for each run, One sheet for this season one for last season.
These are sorted in date order. then I have a ratings sheet that shows the horse name and then to the right it has columns for each of its last 4 ratings.
The formulas I use look for the horse name on the database sheet then put the rating in latest rating in the first column then the 2nd latest in the next column and so on until the last 4 ratings are shown. If there are not 4 ratings to find the is an iserror at the beginning of the formula to populate that cell with a blank so that the errors dont show.
What I want to know is there a way of doing what the formula does using VBA and more importantly would it be quicker than using array formulas which are very slow to recalculate when there are 1000's of lines.
My boss has made it clear I can't require our people to use the add-in.
I have searched under WORKDAY as well as under Barry Houdini, since someone said he has come up with plenty of replacements for the Analysis add-in... but I am not finding it.. can someone help?
Using the add-in, this is what I have:
myCell.FormulaR1C1 = "=WORKDAY(RC[1],-RC[2])"
I am trying to search a folder that is selected by the user for all .jpg files, and then place all those pictures onto a certain spreadsheet within the excel file. It also formats the pictures. Following is the
Private Sub BatchProcessThumb2x3()
Msg = "Select a file containing the photos you want to insert."
Directory = GetDirectory(Msg)
If Directory = "" Then Exit Sub
If Right(Directory, 1) "" Then Directory = Directory & ""
On Error Resume Next
With Application.FileSearch
.NewSearch
.LookIn = Directory
.Filename = "*.jpg"
.SearchSubFolders = False
.Execute
' Select begining range
Worksheets("Thumbnail (2x3)").Select
Range("B4").Select
' Loop through all files and process them
For i = 1 To .FoundFiles.Count..............
I was wondering if is possible to replace SUMPRODUCT with code. Right now I have formulas like these throughout my sheet and I think they are causing it to be slow:
=SUMPRODUCT(--($B$4:$B$1002<=B4),--($M$4:$M$1002="PROD"),--($O$4:$O$1002="O"))
I also have the following code in my worksheet. I don't know if anything here is causing it to take so long to "calculate cells." Also my code just stopped coloring the row ranges and I not sure what cause it to stop working.
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "O:O"
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
'Begin coloring row ranges based on these requirements
If .Row > 3 Then
If Me.Cells(.Row, "O").Value = "" Or Me.Cells(.Row, "O").Value = "O" Or Me.Cells(.Row, "O").Value = "H" Then
Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 0
End If.................
Okay so I'm trying to us the SUMIFS formula to add values on a different sheet based on criteria on that sheet but I want to use a text string on the sheet with the SUMIFS formula to reference the sheet with the data on it.
My original formula was:
=SUMIFS(INDIRECT("'"&A2&"'!K4:K500"),INDIRECT("'"&$A$2&"'!B4:B500"),"=LEFT(AA1,2)",LEFT(INDIRECT("'"&$A$2&"'!C4:C500"),3),"=MID(AA1,7,3)")
However I kept getting an error and excel said that INDIRECT("'"&A2&"'!K4:K500") was volatile, so I tried to replace it with INDEX and my formula was:
=SUMIFS(INDEX(A2:A2,1)&"'!K4:K500",INDEX(A2:A2,1)&"'!B4:B500",LEFT(AA1,2),LEFT(INDEX(A2:A2,1)&"'!C5:C500",3),MID(AA1,7,3))
However INDEX(A2:A2,1)&"'!K4:K500" returns the value "1251062 EP.EL+CB-A10'!K4:K500" which wont calculate properly because of the quotation marks but excel is auto inserting them.
How could I have a macro which translates formulas on file open since Excel doesn't do it itself for functinos from add-ins like YEARFRAC?
View 9 Replies View RelatedIn the following thread, richphillips wonders about Application.Filesearch
Application.filesearch In Office 2007
Is there a replacement for this? I have several macros that use Application.Filesearch and I would like it to work not only with Excel 2003 but also with Excel 2007. This is the function
Function CreateFileList(FileFilter As String, _
IncludeSubFolder As Boolean) As Variant
' returns the full filename for files matching
' the filter criteria in the current folder
Dim FileList() As String, FileCount As Long
CreateFileList = ""
Erase FileList
If FileFilter = "" Then FileFilter = "*.*" ' all files
Debug.Print CurDir
strFolder = BrowseForFolderShell(, , , 0)
If strFolder = "" Then
MsgBox "You Cancelled"
Exit Function
End If............
I have the following code written:
If InStr( Cells(i, 3).Value, "Other") > 0 Then _
Cells(i, 3).Replace What:="Other", Replacement:=Cells(i, 4).Value
This seems to work fine for the most part. However, if the value in Cells(i, 4) is too long, I seem to get a Run-time error '13': Type mismatch. Is there any way to rework this code so it can replace even if the string in Cells(i, 4).Value is too long?
Im trying to work out how to write some code to find and replace from a list.
I have key data in column P on sheet 1, and a list on sheet 2 with the same key data in column C.
Basicially, every month the data on sheet 1 changes (there are usually 100 rows of data each month) and what i want to do is, write some code to look down column P on sheet 1 and for every cell, find the corrosponding value on sheet 2 (column C). When it finds the value, copy and paste it onto sheet 3 then delete the row from sheet 2.
I hve written some code that can do the first part (copy and paste) and a second macro to do the deleting....but it is only for 1 value at a time...
I've created a workbook with code to email it to a reviewer then back to the originator when reviewed (along with other editing functions etc.). The problem - The originator is working in Excel 2003, the reviewer in 2007. The macros work great until the reviewer sends it back. I have not been able to figure out which FileFormat:= ???? to use when I SaveAs prior to emailing as an attachment. My Excel 2003 doesn't recognize the .xlsm file and other formats are eliminated the vba/macro coding
View 4 Replies View Relatedhow to write a code where i can open Excel2003 file from Word2003 and delete data from Sheet1 and then close the excel2003.
All this is done from Word file.
I can select a MS Word 2003 Table from my Excel 2003 macro? I keep getting Error 438 as per attached file for the line of
View 2 Replies View RelatedI am using the formula to count if c- is in the cells: =COUNTIF($B2:$U2,"C-")
However, I need to change the formula so rather than just count if C- is in the cells, I need it to count if A*, A+,A, A-, or B+ are in the cell instead.
I'm currently working with the formula
=COUNTIFS(Data[Quarter],"Q1",Data[Country],A3,Data[Sales],">2000")
However I need the formula to count only one for the n possible duplicates in the Data[Name] column.
Say I have a list of part numbers, and each part number has an X or a 0 next to it, depending on my own set parameter.
How do I then report that data on another tab so that it counts how many there are in a set area AND if its an X.
At the moment I have this:
I'm trying to do a COUNTIFS for a set of data. What I want it to do is pull everything that is assigned to a certain person that has no date in one column, and information in another. For example:
Name
Date
Comment
Michelle
4/30/14
Closed
Michelle
Issue
Michelle
4/30/14
Closed
Michelle
4/30/14
Closed
Michelle
Issue
So based on this dataset, I would get the number 2 for Michelle (row 2 and 5 have no date in column B and a comment in column C). I've tried a few variations and can't seem to make it work right.