Free Microsoft Excel 2013 Quick Reference

- Excel 2000 conditional formatting query
- Calculating overall % estimate
- Counting occurances with "IF" statement
- Combing two Worksheet_Change subs into one
- Hangs when saving spreadsheet using macro
- Script to Merge Muliple Data to one cell
- Macro to repeat a formula a set number of rows apart
- Simplifying Formula (Part 2)
- Can two or more functions run in one cell? I need to count text survey data..
- Sum of Cells if Row Contains a Specific Variable
- Average If Adejecent Cells Greater Than Zero
- VLOOKUP performance issues
- Count unique values based on matching criteria
- Not enough power, RAM or what?
- Count values in a cell
- Annual Leave calculator
- Help..Which Function
- Using formula to tell when client last ordered
- Minimum along rows:creates an array
- Userform Dependant Combobox

I have a spreadsheet set up (Excel 2000), which contains multiple dates in each column, in the format dd/mm/yyyy. Where no date was available, I have entered <undated>.

I’ve used the conditional formatting command: =COUNTIF($B$9:$B$427,B9)>1 to highlight any duplicates within each column.

My problem is, that it will successfully highlight duplicates of <undated>, but it will not highlight duplicate dates.

Can somebody please tell me what I’m doing wrong?

If you need more info on the spreadsheet, just ask!

Thanks,

Michael

I have a table which has planned hours and an overall estimated % completed. For multiple items how do I find the overall % complete.

.........................Wk 1........Wk 2..............etc etc

Planned .............10...........10

Actual ...............10............0

Estimated %........40%

Planned ..............5............25

Actual ................5.............0

Estimated %........19%

Planned ..............4.............16

Actual ................4..............0

Estimated %.......20%

I need a function which will multiply each estimate by its associated planned hours and then multiply the total planned by the resulting composite %.

ie.70/(20*40%)+(30*19%)+(20*20%) = 0.253.

I have tried various sumif and countif functions but can't get them to work.

As the spreadsheet will be populated by non-excel techies, the function must be able to be copied when new columns and rows are added.

Thanks in advance

Spoed

I have been racking my brain on this one for a bit, and I am sure there is an easy solution. I just haven't put 2 and 2 together to get it... :-)

Here is my situation:

I have a spreadsheet that has one column with dates and blank spaces, then another row/s with numbers in them. Some rows will have multiple numbers in them. What I need is a formula that for instance can look at the data in cell A1, count all of the occurances of the identical data in range of columns, but omit any that have data in another column range, but just in that range.

For instance:

A1= 12575252

Date:+++++++++++++++Model #+++++++Model #+++++++Model #

1/1/05++++++++++++++12575252+++++12575251

+++++++++++++++++++12575252+++++12587556++++++45456666

+++++++++++++++++++12566886+++++12575252

1/5/08++++++++++++++15654657++++++87979797+++++12575252

End result should be = 2

I am thinking that it needs some sort of countif, but with some sort of row function in it, but I just can't figure out how to get it to omit any data in rows that have a date in them. Any help would be greatly appriciated.

Thanks

Code 1 - Actual code that I am currently using and it works fine on its own:

Option Explicit ' Developed by Contextures Inc. ' www.contextures.com Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next Dim ws As Worksheet Dim str As String Dim i As Integer Dim rngDV As Range Dim rng As Range If Target.Count > 1 Then Exit Sub Set ws = Worksheets("Lists") If Target.Row > 1 Then On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo 0 If rngDV Is Nothing Then Exit Sub If Intersect(Target, rngDV) Is Nothing Then Exit Sub str = Target.Validation.Formula1 str = Right(str, Len(str) - 1) On Error Resume Next Set rng = ws.Range(str) On Error GoTo 0 If rng Is Nothing Then Exit Sub If Application.WorksheetFunction _ .CountIf(rng, Target.Value) Then Exit Sub Else i = ws.Cells(Rows.Count, rng.Column).End(xlUp).Row + 1 ws.Cells(i, rng.Column).Value = Target.Value rng.Sort Key1:=ws.Cells(1, rng.Column), _ Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End If End If End SubCode 2 - This is the original code. Modified version is below:

Option Explicit ' Developed by Contextures Inc. ' www.contextures.com Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String Dim strSep As String Dim rngEdit As Range 'Check "Edit Entries" box, to make changes ' to multiple selection data validation cells Set rngEdit = Worksheets("AdminNotes").Range("EditMode") strSep = Chr(10) 'line break separator If Target.Count > 1 Then GoTo exitHandler On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitHandler If rngDV Is Nothing Then GoTo exitHandler If rngEdit.Value = False Then If Intersect(Target, rngDV) Is Nothing Then 'do nothing Else Application.EnableEvents = False newVal = Target.Value Application.Undo oldVal = Target.Value Target.Value = newVal If Target.Column = 2 Or Target.Column = 4 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & strSep & newVal End If End If End If End If End If exitHandler: Application.EnableEvents = True End SubCode 2 - "Modified" - This does not work when i put it on the worksheet.

' Developed by Contextures Inc. ' www.contextures.com Private Sub Worksheet_Change2(ByVal Target As Range) Dim rngDV1 As Range Dim oldVal As String Dim newVal As String Dim strSep As String Dim rngEdit As Range 'Check "Edit Entries" box, to make changes ' to multiple selection data validation cells Set rngEdit = Worksheets("AdminNotes").Range("EditMode") strSep = Chr(10) 'line break separator If Target.Count > 1 Then GoTo exitHandler On Error Resume Next Set rngDV1 = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitHandler If rngDV1 Is Nothing Then GoTo exitHandler If rngEdit.Value = False Then If Intersect(Target, rngDV1) Is Nothing Then 'do nothing Else Application.EnableEvents = False newVal = Target.Value Application.Undo oldVal = Target.Value Target.Value = newVal If Target.Column = 3 Then 'Should only apply to column C If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & strSep & newVal End If End If End If End If End If exitHandler: Application.EnableEvents = True End Sub

To explain better, I should explain what the spreadsheet and macro do.

Basically, it is an excel spreadsheet which open on a PC that I remote desktop to that contains an Excel Table which links to our database every morning to refresh, this table shows our multiple clients with several lines per client. This works fine and dandy.

The aim of the macro is to create a separate work book for each client called by that client name. There are around 500 clients which makes in unmanageable to do manually and the clients are not always the same.

The macro creates a second table on sheet 2, and this table gives the first and last instance of the client name, this in turn helps identify which rows the macro copies and then pastes into a new workbook. The final part is to save the newly created workbook with the client name, then the loop kicks in and it continues to the next client until they are all done.

The problem itself as I said before is during the saving phase of the new workbook. For about 99% of the workbooks they save fine, but for about 3 or 4 whilst it’s saving, it hangs when you have the option to cancel the saving, (even though it has actually saved) and this stops the whole process. this only happens when using the VB script to open the spreadsheet and run the macro.

Without the problem this takes about 15-20 minutes, with the problem it takes until someone clicks cancel on the bit where it gets stuck.

Any help would be appreciated. (I have attached an example spreadsheet and the code below)

Sub ABCMacro() Application.ScreenUpdating = False Application.DisplayAlerts = False Dim Counter As Integer Dim Match As String Kill "P:Shared ReportsLiveClients*.*" ' delete all files in the folder Counter = 2 Sheets("Sheet1").Select Range("D12").Select Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False Columns("R:R").Copy Sheets("Sheet2").Select Range("A1").Select ActiveSheet.Paste Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ActiveSheet.Range("$A:$A").RemoveDuplicates Columns:=1, Header:= _ xlYes lastrw = Worksheets("Sheet2").Cells(Rows.Count, "a").End(xlUp).Row Range("B1").Select ActiveCell.FormulaR1C1 = "Matches" Range("C1").Select ActiveCell.FormulaR1C1 = "Instances" Range("D1").Select ActiveCell.FormulaR1C1 = "Row Ref" ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$d$" & lastrw), , xlYes).Name = _ "Table11" Range("B2").Select ActiveCell.FormulaR1C1 = "=MATCH(RC[-1],Table1[Alt_CustCode],0)" Range("C2").Select ActiveCell.FormulaR1C1 = "=COUNTIF(Table1[Alt_CustCode],RC[-2])" Range("D2").Select ActiveCell.FormulaR1C1 = "=RC[-2]+1&"":""&(RC[-1]+RC[-2])&"",1:1""" Counter = 2 On Error Resume Next Do While Len(Worksheets("Sheet2").Cells(Counter, 4).Value) > 0 Match = Worksheets("Sheet2").Cells(Counter, 4).Value ' row ref Sheets("Sheet1").Select ActiveWindow.ScrollColumn = 1 Range(Match).Copy Workbooks.Add ActiveSheet.Paste Columns("A:R").Select Columns("A:R").EntireColumn.AutoFit Range("A1").Select ActiveWorkbook.SaveAs Filename:= _ "P:Shared ReportsLiveClients" & Range("R2").Value & ".xls", FileFormat:= _ xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False ActiveWindow.Close Windows("Separate reports 2.xls").Activate Counter = Counter + 1 Loop Application.ScreenUpdating = True Application.DisplayAlerts = True Sheets("Sheet2").Select Columns("A:D").Select Range("D1").Activate Selection.ClearContents Range("A1").Select Sheets("Sheet1").Select Range("A1").Select End Sub

Sub Combine() Range("D:D").ClearContents For i = 3 To Cells(Rows.Count, 1).End(xlUp).Row strr = "" If WorksheetFunction.CountIf(Range("A:A"), Cells(i, 1)) > 1 Then 'there are multiple entries curacc = Cells(i, 1).Value outrow = i While Cells(i, 1).Value = curacc strr = strr & Cells(i, 3).Value i = i + 1 Wend Cells(outrow, "D").Value = strr i = i - 1 Else Cells(i, "D").Value = Cells(i, "C").Value End If Next i End SubI have attached an example of the spreadsheet.

number of rows apart (1200) in a column?There's multiple worksheets

grouped together in a workbook and it would be nice if it would repeat

in the same column same location in each. the start would be L25. The

formula is a countif that refers to data in the d column. Each

worksheet has a different end of data in the d column. Any help would

be greatly appreciated. You guys have been right on everything so far.

THANKS! steveo

Imagine a pivot table (or one that looks exactly the same using Sumproduct)

and on another page, taking information and sorting it. I currently use.

Name - Think Being Sorted - Rank - Countif - Total - Name - Numbers -

Vlookup - Vlookup

So I rank the data, the countif counts if that data is repeated, so if 2

people both have 14 units, it'll put a 1 next to the second one

(Countif($B$2:B4,B5) dragged down) add the rank and the total together so I

have a rank that doesn't contain multiples. Reput the name, in the next

column just the numbers 1, 2, 3 etc. Then do a Vlookup using those numbers

against the Total column, returning the name; so this puts all the names in

order. Then a final vlookup on the first two columns to get the data next to

the names.

So 9 Columns to automatically sort a column of information, which seems alot

to me... there must be an easier way to automatically sort information

without resorting to using a macro...

are the same for each survey. I need to bring the answers from the 3Q's

together in one table and then provide one overall graph.

PLAN:

I created 15 worksheets, one for the graph, one for the overall data

summary, and 13 extras (one for each set of exported/imported survey data.)

The survey application allows me to extract the results in text form, yet I

need numbers for the totals & graphs.

On the summary sheet for SUP Table in a cell by row Q1 and column A1 (answer

1) I'd like to count the number of times a specific word shows up in one of

the columns on the SUP survey results sheet and then put the resulting # in

the cell on the SUP table of the summary sheet. I'd do this for each of the

3q's and each of the possible answers, for each of the 13 different survey

tables. This would bring the #'s of each response to the front sheet. (I

could graph each of the 13 tables as well.)

Once that is done, I'd go up to the overall section table LOGISTICS and do

some counting of the data in each cell of the thirteen sub tables, to bring

a total for the LOGISTICS survey's all in one table, which I can then graph.

STATUS:

I created all the tabs, and tables.

I started with the SUP survey results, and a "countif" function, but the

function is not counting the text string data that I put in the function.

I'd like to try to convert the text string to a specific number and then do

a "count if" for the # and have that total be put in the cell.

So I'd be trying to do two functions... first convert and then countif. Can

that be done?

Sample file attached, if not I can send it to somewhat who requests it at:

thiswillbedeleted@gmail.com

I'm also hoping to get the date field (date the person submitted their

survey response) extractd from the survey program in the near future. I'd

then like to add another function to the two above for each of the cells.

so that the conversion would happen, and the count if would happen based on

a certain date range, & the results from those three functions would be the

data for one Qtr in the fiscal year. The funtions would help to sum the

data from each survey and put the totals in the rows and columns for each

quarter.

Anyone have some suggestions?

Mr.

am hoping I can again. I've put together a spreadsheet for sales

tracking, and need to sum a group of multiple cells in multiple rows,

based on a value in a specific column. An example:

ColumnA ColumnB ColumnC ColumnD

Row1 John 2 3

Row2 Edward 3 5

Row3 Frank 1 2

Row4 John 2 0

Row5 Frank 0 5

I need a formula that will sum the totals from columns B and C, for all

rows that contain "John". Once complete, the above example would look

something like this:

ColumnA ColumnB ColumnC ColumnD

Row1 John 2 3 7

Row2 Edward 3 5 8

Row3 Frank 2 2 9

Row4 John 2 0 7

Row5 Frank 0 5 9

I've tried various iterations of formulas using =COUNTIF, =SUMPRODUCT,

=SUBTOTAL - but so far haven't found a solution. Any suggestions or

hints would be greatly appreciated. Thanks in advance.

some averages for. Down one column is BF, LN, DN, SCK, TOT repeated, and the

adjecent cell contains a value I currently have the average for Breakfast as

=(SUMIF(August!$J$2:$J$91,"BF",August!$K$2:$K$91))/COUNTIF(August!$J$2:$J$91,"BF"),

howver the count function still counts cells that have zero values, I would

like the average of only the non-zero cells. Any ideas????

Here is what the spreadsheet is designed to do:

We have data that is pasted into excel from another source. The data is broken up into account number, date, check number, and amount. Multiple payments from the same account can occur in one month so the data must be totaled. There is a custom function called concatif which works just like sum if but concatenates text. There are VLOOKUPs performed on the account number and the corresponding date, check number, and amount are inserted in the table.

Thanks in advance for your help.

I realized the vlookups were not the problem it was the VBA code.

Here is the concatif funtion

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _ Optional Delimiter As String, Optional NoDuplicates As Boolean) As String ' code base by Mike Rickson, MrExcel MVP ' used as exactly like COUNTIF() with two additional parameters ' of delimiter and "no duplicates" as TRUE/FALSE if concatenated values ' might include duplicates ex. =ConcatIf($A$1:$A$10,C1,$B$1:$B$10,", ",True) Dim i As Long, j As Long With compareRange.Parent Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1"))) End With If compareRange Is Nothing Then Exit Function If stringsRange Is Nothing Then Set stringsRange = compareRange Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _ stringsRange.Column - compareRange.Column) For i = 1 To compareRange.Rows.Count For j = 1 To compareRange.Columns.Count If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j)) End If End If Next j Next i ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1) End Function

I'm stuck on a formula and would really appreciate some help.

I have a worksheet with two tabs.

First Tab

Account Name

Account Number

Second Tab

Account Name

Account Number

Account Ship Location Number

On the first sheet each account name and number only appears once.

On the second sheet there may be multiple entries for each Account Name and Number.

On the first tab for each row I want to compare the Account Number column to the Account Number column on the second tab. Where I have a match I need to count how many unique Account Ship Location Numbers correspond.

I don't know if this requires a frequency formula or a countif or... and I'm really confused.

Thanks

My computer is less than a year old, running XP Professional & Office 2000 with SPs updated, a Pentium 4 3.2GHz processor and 3G RAM. PF set to 4606.

My computer frequently stops responding when trying to copy & paste formulas.

(I also have a problem with my Solidworks CAD software freezing up, particularly when using a design table in Excel.)

I recently went from 1G to 3G which reduced the Solidworks crashes from multiple time daily to once a week but the straight Excel performance is still at times less than desirable.

Today my file size before any formulas is 920KB. Basically 4 columns by 15,000 rows of data.

All I am trying to do is copy the simple formula =IF(COUNTIF(D:D,D2)>1,COUNTIF(D:D,D2),0)

down the 15,000 rows

it freezes up every time. I can copy one cell down 7,000 rows but not 15K.

this only takes 30 sec.

and I’d copy cells in adjacent columns down if possible!

Simple solution is to copy in sections, but this thing only getting larger.

Does anyone know what I could do to improve the performance?

In a column where each cell has multiple text values separated by commas, I want to count specific values. Do you know a way to do this?

Background

I'm in charge of a spreadsheet that tracks projects. On the spreadsheet's DATA worksheet, the project names are listed in the first column and the projects' status---In progress, On hold, Red flag, Complete---is entered in the Status column in the same row as the project name. On another worksheet, I use the COUNTIF function to count the data in the Status column (on the DATA worksheet) and build a chart from the results. For example, if the Status column is the E column, I will have a series of functions that say =COUNTIF(DATA!E2:E100,":$In Progress"), =COUNTIF(DATA!E2:E100,":$On Hold"), =COUNTIF(DATA!E2:E100,":$Red flag"), etc, to get the numbers to build the chart. This works fine.

The problem

Another column titled Resources lists, in each cell, the names of individuals working on a project. Typically, data is entered into the cells in this column as "Bill, George" and "Tom, Bill, George", and "Tom, George, Mary, Bill". I want to count the number of times that, say, Bill has appeared in the Resources column for the purpose of counting how many projects Bill is on. To do this, I have tried to use the COUNTIF function in combination with separating the values in the cell with commas. This doesn't work. Is there any way of separating these values so the COUNTIF function can pattern match specific values, to find, say, Bill and ignore the others?

If COUNTIF can't be used, I am thinking of trying a series of IF functions to return a value of TRUE if, say, Bill appears, or George appears, etc, but I don't know how to count the results to get the numbers to build a chart.

Any advice is welcome.

Can somebody help me with this (it's driving me round the bend!)

I would like a formula that counts the number of days of annual leave that I have left to take in a year.

cell A1 contains todays date: =today()

cell B1 contains the total number of days of annual leave that I have per year (12.5)

cells A2:A10000 contain a list of dates, where A2 =31/5/2004 and the rest of the cells in column a below that contain =A2+1 etc. The dates are formatted to appear like this: Mon 31-May-04

cells B2:B10000 contain text describing single or multiple events taking place on the corresponding date in column A, for example "TIME OFF" or "HALF DAY" or "BIRTHDAY/TIME OFF/PAY DAY"

So, is it possible to write a formula that counts the number of cells in column B that

include the phrase "TIME OFF" or "HALF DAY" in a given year and subtract this from cell B1?

I have looked at several other threads and experimented with things like:

=B1-(SUMPRODUCT(--(COUNTIF($B$142:$B$10000,"*TIME OFF*")))--($A$2:$A$10000>=DATE(2005,1,1))--($A$2:$A$10000<

DATE(2005,12,31)))

Any ideas? HELP!!

Thanks in advance!

I am really enjoying this forum. I am a newbie but have been helped so much already. I need help again. What I am asking is just for hints...I want to learn on my own but need a direction.

Attached is a zip file with Id #'s, test scores, and different dates.

I need the first worksheet page of "Students" to use the ID number to reference the "English" worksheet and just count up the total dates of appearance for that ID number even if its multiple sessions.

Question: Do I use countif, vlookup, index, match?

Second, I need the highest score column on "Students" to reference the English page scores and judge the highest number in the array and display it.

Question:What fucntion would I use there? an hlookup with some sort of filter?

thanks again for this great learning resource

-Michael

w/ my company

all of our clients place multiple orders with us over the span of the year,

and many clients names are in the workbook. the formula needs to be able to

countif the name of client if correct and then sort the dates that client has

ordered- (another column) to provide the most current date

something like countif j1 - j200 = joe's bakery & then sort g1-g200 for the

most recent date.

I have tried to work out how your formula works and am struggling

>=SUMPRODUCT(Rng,(COUNTIF(OFFSET(Rng,ROW(Rng)-CELL("Row",Rng),0,1,

>COLUMNS(Rng)),"<"&Rng)=0)/COUNTIF(OFFSET(Rng,ROW(Rng)-CELL("Row",Rng),

>0,1,COLUMNS(Rng)),Rng&""))

So far I think it creates an array the size of the range that identifies

(1 or 0) the lowest number (or if n multiples uses a fraction 1/n)

This is then Sumproducted with the original array.

Please could you explain how the two COUNTIF(OFFSET()) functions are

constructed and how they work

many thanks

RES

Further to the question and answers in the thread, link above, regarding Userform Dependant Combobox.

Excellent answer, it does exactly as requested. I am new to forums and am trying to expand my knowledge.

I am trying to do the same sort of thing but with dynamic ranges, variable number of options in each combo box and a group of 4 combo boxes. I.e. one result cascades to the next combo box making sure that an inappropriate result cannot be entered on the userform.

Before trying to work out the code I am working on the excel dynamic ranges using ‘Match’ from the first option to provide the offset reference point for the second ‘Match’.

'=OFFSET((CELL("address",INDIRECT(CONCATENATE("R",ROW($KJ$31)+((MATCH($JQ$25,$KI$32:$KI$131,0))),"c" ,COLUMN($KJ$31)),FALSE))),MATCH($KJ$26,$KJ$32:$KJ$131,0),4,COUNTIF($KJ$32:$KJ$131,$KJ$26),1)

This may be a too complex solution. Perhaps nested 'Match' does not work in excel.

If this does not work I can go back to creating each option list separately, which may be simpler.

I can get four option boxes working sequentially in the excel workbook. However, I am trying to move all user interfaces to userforms.

I know there are effectively two questions here but I am only looking for one solution, which is how to make multiple Userform Dependant Comboboxs of variable length.

All help welcome.

No luck finding an answer? You could always try Google.