Free Microsoft Excel 2013 Quick Reference

Countifs and multiple columns Results

Hello all, I’m hoping someone out there can help me, as I’m tearing my hair out (and believe me, I don’t have that much to waste!).

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!

Hi new to the forum, but would appreciate help.

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

Hello All,

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 #

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.


I found two useful codes on CONTEXTURES that I have been trying to combine into one. Based on searching on EF I found you cant have two different Worksheet_Change subs on one page so I need some help combining the two codes:

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

Option Explicit
' Developed by Contextures Inc.

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
    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, _
  End If
End If

End Sub
Code 2 - This is the original code. Modified version is below:

Option Explicit
' Developed by Contextures Inc.
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
      Application.EnableEvents = False
      newVal = Target.Value
      oldVal = Target.Value
      Target.Value = newVal
      If Target.Column = 2 Or Target.Column = 4 Then
        If oldVal = "" Then
          'do nothing
          If newVal = "" Then
          'do nothing
          Target.Value = oldVal _
            & strSep & newVal
          End If
        End If
      End If
    End If
End If

  Application.EnableEvents = True
End Sub
Code 2 - "Modified" - This does not work when i put it on the worksheet.

' Developed by Contextures
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
      Application.EnableEvents = False
      newVal = Target.Value
      oldVal = Target.Value
      Target.Value = newVal
      If Target.Column = 3 Then 'Should only apply to column C
        If oldVal = "" Then
          'do nothing
          If newVal = "" Then
          'do nothing
          Target.Value = oldVal _
            & strSep & newVal
          End If
        End If
      End If
    End If
End If

  Application.EnableEvents = True
End Sub

I currently have a spreadsheet which every morning runs a macro. This used to run perfectly and quickly, but in the last month or so, it's started playing up during the saving phase and I don't know why.
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

    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveSheet.Range("$A:$A").RemoveDuplicates Columns:=1, Header:= _
    lastrw = Worksheets("Sheet2").Cells(Rows.Count, "a").End(xlUp).Row
    ActiveCell.FormulaR1C1 = "Matches"
    ActiveCell.FormulaR1C1 = "Instances"
    ActiveCell.FormulaR1C1 = "Row Ref"
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$d$" & lastrw), , xlYes).Name = _
    ActiveCell.FormulaR1C1 = "=MATCH(RC[-1],Table1[Alt_CustCode],0)"
    ActiveCell.FormulaR1C1 = "=COUNTIF(Table1[Alt_CustCode],RC[-2])"
    ActiveCell.FormulaR1C1 =

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
ActiveWindow.ScrollColumn = 1

    ActiveWorkbook.SaveAs Filename:= _
        "P:Shared ReportsLiveClients" & Range("R2").Value & ".xls", FileFormat:= _
        xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
Windows("Separate reports 2.xls").Activate

Counter = Counter + 1
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

I have had a script written previously to take the data from Column C where Column A had the same unique number and populate Col D with the text from Col C. I need to know how I could separate each of the line values with a semi colon once the data is moved to Col D.

Sub Combine()
  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
      Cells(outrow, "D").Value = strr
      i = i - 1
      Cells(i, "D").Value = Cells(i, "C").Value
    End If
   Next i
End Sub
I have attached an example of the spreadsheet.

Hi guys, does any body have a macro that would repeat a formula a set
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

Similar to my last post.
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...

I have thirteen different surveys with multiple questions. Three of the Q's
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.


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.

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:

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

Anyone have some suggestions?


Hi Everyone. I've found tons of help in these forums in the past, and
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.

I have a have multiple worksheets that contain data i would like to calculate
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
howver the count function still counts cells that have zero values, I would
like the average of only the non-zero cells. Any ideas????

I designed a spreadsheet which uses a lot of VLOOKUPs and it takes a very long time to recalculate. If there is anything you can suggest to speed up the processing I would greatly appreciate it. I don't know if there is another way I could do this, but this just seemed to make sense and it works just the way we want it but it just takes too long.

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.


We have no IT people where I work, so basically no one has the knowledge to answer this.

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?

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<

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

i need a formula that can tell me the last time that a client placed an order
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


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

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.