Free Microsoft Excel 2013 Quick Reference

Count number cells based on multiple criteria

I am trying to be as thorough and clear as possible, so my problem statement is kind of long.

I have a very challenging IF, COUNTIF, COUNTIFS, or SUMPRODUCT problem. I need to count the number of cells in a range that either meet or DON'T meet several
criteria. So, I think I may need to add a NOT() to my COUNTIFS/SUMPRODUCT criteria. I've not been able to find anything on the Internet that tells how to
NOT() a criteria within a COUNTIF, COUNTIFS, or SUMPRODUCTS function. It would be interesting to see the syntax for this whether this gets me to my ultimate solution or not.

I tried COUNTIF, COUNTIFS, IF and SUMPRODUCT in one form or another using in Excel 2007 but I can't get the NOT() to get through the syntax of any of these.
NOTE: I have to use a formula because the results of the formula is used for another function in the spreadsheet, so Excel's Conditional Formatting and Filtering features won't work.

I am having a hard time deciding whether to write this statement so that it ANDs together multiple FALSEs or ORs together multiple TRUES. It depends which
syntax will clear the COUNTIF, COUNTIFS or SUMPRODUCT statements, I think.

The criteria for counting a cell is if each of the following is not true. If any one of these exact data types are in the cell, the cell is not counted:
1) cell contains any number (positive, negative or zero)
2) cell is a blank cell [Should I Use ISBLANK() or ="" to test?]
3) cell contains all space characters [Shoul I use TRIM to test?]
3) cell contains the single letter "e", "E", "i" or "I"
4) cell contains the string of letters the first 3 of which are either "inc*", "Inc*", "exc*", "Exc*"

Thanks in advance for your dealing with this tricky question.


Post your answer or comment

comments powered by Disqus
Hello everyone! I just registered and I need some help counting unique values based on multiple criteria. Currently I am using a different program to do this calculation because counting unique values is something that it does very easily and quickly. The problem is that I am using the data in a number of different ways in Access and Excel and I need to be able to reproduce the count on another sheet (in the same file where I am using the data for other things) when the data table is refreshed.

I've done quite a bit of searching but I think I've just confused myself and I'm not sure what the best way to do this would be.

Here's the scenario that I am working with: I am trying to count unique persons associated with a certain program in a certain "area". There are multiple values for each of these different fields and I want to be able to associate unique persons with 1 program and 1 "area". (Programs and "areas" can be mixed and matched as they could be associated with any quite a few different combinations of these")

Sample data layout:

person_____program_____area
Person 1___Program 1___Area AA
Person 1___Program 1___Area AB
Person 2___Program 2___Area CD
Person 3___Program 3___Area CE
Person 4___Program 4___Area ME
Person 4___Program 4___Area EI
Person 4___Program 4___Area LK
Person 5___Program 5___Area PO
Person 6___Program 6___Area TR
Person 7___Program 7___Area EE
Person 8___Program 8___Area QW

Although it looks like we can assume that the same person will always be associated with a specific program, it could change and I would want to identify them uniquely in each program.

Would it be better to create a concatenation of these three fields and then count uniques? I just don't know, I've come up with a few different ways but they have been labor intensive and I can't reproduce them easily.

Thanks!
Clint

I need to get a count based on multiple criteria. I need to count if a
certain text value occurs in a column, but only if it meets a seperate date
criteria.

I have a data entry page with a vendor column and and month column. I want
to count how many times a vendor is listed in a certain month. I have tried
Count and Count IF formulas with out luck.

Hi All,

I have a spreadsheet that's generated from a download. I need to modify one column of cells based on multiple criteria. Started to try with a nested If statement, but soon passed the limits allowed. Not sure if some kind of VLookup or VBA formula could help. Any input that would point me in the right direction would greatly be appreciated. To summarize what I'm looking for: If column A has a value of any of the following (10D, 11Z, 10Z, 2CS), then I need to change the Rate in column D based on the Years left to maturity in column B. The Rate percentage to be used is based upon the table shown in the attached spreadsheet.

In addition, there is an exception if Column A has a value 2CB and an inventory # of 9300276 or 9300277 in column C, then I need to change the rate in column D based on the table as well. Otherwise the rates stay as originally imported.

Hope this makes sense. Again, any ideas would be greatly appreciated.
I have attached a sample spreadsheet.

Thanks a bunch!
JL

I am trying to count rows based on multiple criteria in cells that are non continuous and the criteria may be repeated.
There are 4 output buckets: Not yet called (1), Called but not contacted (2), Callback(3), and Callback with Apt(4). A row with empty criteria cells is (1), A row with only "1" in any of the criteria cells is (2), if any of the cells have "2" they count as (3), and if any of the cells have a "2" and either a "b" or "c" in the adjacent cell, they should count for (4). No rows should be double counted except in the event of (3) and (4).

For example

Row 1 values (non-continuous) are: 1 2 c 1 2 ... The number '2' and 'c' (separate cells) defines the output and this row should be counted once as "Callback with apt"
Row 2 values (non-continuous) are: 1 1 1 ... This should be counted once as "Called but not contacted"

The attached worksheet has an abbreviated data set and the output that I am looking for. I have attempted versions of COUNTIF or array SUM(IF functions and I can seem to come up with the right solution.

I don't know how to write macros, which could very well be the solution.

Any help would be appreciated.

I am trying to create a COUNTIF function in 1 worksheet based on multiple
criteria in another worksheet. Basically, I want to count the number of
cells that the criteria of 3 different columns in a separate worksheet (i.e.
$A:$A="Smith" and $AS:$AS="4/19/2007" and $CL:$CL="$419"). I want to count
the number of cells that meet all 3 of these criteria. Can it be done?

First, thanks in advance for taking the time to read this, so here goes....

I'm trying to total the number of cells(Blank or Not) in a column based on two criteria-
1- one of two types of equipment(A or B)-Column A= Equipment
2- if the equipment is online(Column B= Online Date); once online the words "Not on Line" are replaced with the unit's online date.
I've got the invoicing formula nailed,-we bill commencing 1 year after the online date-
but i need to get totals of online equipment by type under each month. Since we don't bill for a year, somebody suggested I just total the column for a year ahead, but my boss doesn't want that.
Suggestions?
I've uploaded a sample sheet.
Thanks in advance,
Jim B

I need to count and sum a worksheet of over 10,000 rows based on
multiple criteria. 1st is by the employee name, second date range,
they other is product type, but for this example i doubt three is much
different than two criteria's.

Employee Price Qty Date
Tom 69 1 14-Feb
Edgar 34 1 14-Feb
Tom 55 1 10-Feb
David 25 1 28-Feb
Edgar 59 1 1-Feb
David 280 -1 20-Feb
Tom 355 1 15-Feb
Edgar 125 1 17-Feb
Edgar 175 1 3-Feb

How could I count the QTY if the employee is Edgar between 2/14 and
2/28? Any help would be greatly appriciated.

Thanks

I'm not sure this is even possible, but here's what I am trying to do. I want to count the number of unique occurrences based on multiple criteria

Name Title Task Week Hours
Joe Smith Manager Meetings 5/13/05 50.00
Jane Doe Assistant Meetings 5/13/05 10.00
Jane Doe Assistant Administration 5/13/05 30.00
Jenny Dee Assistant Meetings 5/13/05 00.00
Jim Jones Assistant Work Papers 5/13/05 20.00

What I want to do here is return the unique count of "Assistants" that booked "Hours" for "Week" 5/13/05. The answer is 2

Any help would be greatly appreciated!
Thanks,
Max

Hi all,

I'm hoping someone can point me in the right direction on this, I feel as though the answer should be obvious...

I have a worksheet with data in multiple columns (flat-file database structure, each column is a field and each row is a record):

Name | Location | Date | Data1 | Data2 | Data3

(I'm using | to indicate a column break in this example.)

For example:

domain.com | All Canada | Nov-10 | 11123 | 22123 | 33123
example.com | All Canada | Nov-10 | 10321 | 57321 | 29819
another.com | All Canada | Nov-10 | 47123 | 81723 | 19283
onemore.com | Ontario | Nov-10 | 12823 | 123945 | 12362
again.com | Ontario | Nov-10 | 72839 | 81937 | 9135
lastone.com | Ontario | Nov-10 | 92834 | 93241 | 56321

All columns are unsorted. The Name and Date columns have values that repeat. The Location column has two possible values, 'All Canada' or 'Ontario'. There are no blank cells - each row has values in every column.

The 'unique key' is a combination of Name, Location & Date columns: there is only one row in the worksheet containing 'domain.com | All Canada | Nov-10'. (In this example, there may also be a row for 'domain.com | Ontario | Nov-10', which is also a unique record/row on the worksheet.)

I have data going back a couple of years and the number of rows in the worksheet grows each month as I add in the data set (records) for that month. Also, the number of 'names' in the set (number of records/rows) for each month varies month to month, for example: 10 rows of data for Oct-10, 12 rows for Nov-10, 13 rows for Dec-10 etc.

I've set up dynamic named ranges for the data (one for each column, which automatically includes as many rows as have data in that column): Date Location Media Data1 Data2 Data3.

What I'm trying to do is return an array of all the Names available for a given month and location. For example, if 'All Canada' and 'Nov-10' are the criteria, the returned array should be {domain.com, example.com, another.com}. If 'Ontario' and 'Nov-10' are the criteria, the returned array should be {onemore.com, again.com, lastone.com}.

I'm already using SUMPRODUCT in the workbook to return specific data values from the data worksheet based on multiple criteria:

This example returns 33123.

Great for returning a single value, however I need to return an array of values. I just can't figure out how to build an array including all the records available for a month and a location. I don't want to use VBA and I intend the formula to be a named range itself: AvailNames={array returned by formula}.

I've tried a few things like:

 but that throws a #VALUE error.

I hope someone can point me in the right direction. I'm stumped!

Thanks,
Keith

Hi, I am working on a spreadsheet that I need to filter some records based on multiple criteria and copy the resulting records to another worksheet starting from last empty row. Any help of coding this would be appreciated.

Here is what I am trying to do:

Sheet1: I have the following data

ID | Name | Pass/Fail (Yes/No) | Grade
2............Jason............Yes.......................80
67..........John..............No........................45
34..........Mary.............Yes.......................94
49..........Kelly..............Yes.......................N/A
56..........Rey...............Yes.......................N/A

Sheet2: I have the following headings

ID | Reason | Date
78.............Not taken the exam.......2/12/2011
95.............Postpone the exam........4/12/2011

I wanted to filter sheet1 based on “Yes” from Pass/Fail field and N/A in grade
field (Kelly and Rey would result..) and get just the id numbers 49 and 56 add it
to sheet2. But in the copy process I wanted to add reason as “Not taken exam”
and date as today’s date.

After the copy process Sheet2: should look like as follows:

ID | Reason | Date
78.............Not taken the exam........2/12/2011
95.............Postpone the exam.........4/12/2011
49............Not taken the exam........01/4/2012
56.............Not taken the exam........01/4/2012

I am working on following code but I get errors. Is there any other way to do this? Thanks a bunch!!

Dim rngDest As Range
Set rngDest = worksheets(“Sheet2”).Range(“A” & CStr(Application.Row.Count)).End(xlUp).Offset(1,0)

With ActiveSheet

If WorksheetFunction.CountIFS(.columns(3), “Yes”, .Columns(4), “N/A”) <> ) then
.AutoFilterMode = False
.Range (“A15:A250”).AutoFilter Field:=3, Criteria1:”Yes”, Operator:=xlAnd
.Range (“A15:A250”).AutoFilter Field:=4, Criteria1:”N/A”
ActiveSheet.UsedRange.Copy Destination:rngDest
.AutofilterMode = False
.Application.CutCopyMode = False
End If
End With

Hello,

I think the best thing is to take a look at the attached, then read what I'm looking for, as it would make more sense...

That being said: what I am looking to do is change cell L3 based on new criteria in cell M3.

Right now if B3=Stationery World then L3=Stationery.

What I then want to happen is if E3=Toner then M3=67200 which then has L3=Equipment Consumables and NOT stationery.

Is this even possible? Also, is there a term for this kind of thing?

Thanks for the help!

Hi

I am creating a summary page to analyse my golf scores. I have come to a dead end when looking at the Par statistics.

Does anyone fancy a challenge and try to fill in some of the blanks on the summary tab. I am trying to use array formulas based on multiple criteria.

for instance under the Best Par 3 section, it would look at all the Par 3's for whichever golfer is selected and then return the lowest number.

I am hoping to use only formula and not use VBA if i can help it.

Any takers?

Could someone point me in the right direction?
I am trying to delete the contents of a cell based on to criteria.
I could use formulas, but I have 6,000 plus rows.

Cell formula to be typed in cell I26 would be:


	VB:
	
 
=If(And(I26="a",COUNTBLANK(K26)=0),"","a") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I am trying a slightly different approach with the code, but can not get my language right.


	VB:
	
 
If Cells(r, 11).Value  "" And Cells(r, 9) = "a" Then 
    Select Cell (r,9) clear contents 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any suggestions?

Thank you,
-Marc

Appreciate it if anybody can share share some codes on how to merge cells based on some criteria:

Example: There are 7 rows in column A containing 2 rows for "Andrea", 1 for "Bryan" and 4 for "Cloe". I want to have 2 merged cells in columun B for "Andrea", 1 for "Bryan" and 4 merged cells for "Cloe".

Note that the number of times data will repeat in column A will vary.

See attached file for better example.

Thanks!!

Okay here goes;

I have a table that contains training data received from the business. I need to cross reference an employee list with this table and have it return values based on multiple criteria. I've tried combining VLOOKUP and IF, I've also tried using the INDEX, MATCH functions and I've made some unholy combinations of many others.

The one that works the best seems to be a combination of IF & COUNTIFS.

=IF((COUNTIFS('DoC Results'!$C$2:$C$581,A4,'DoC Results'!$U$2:$U$581,"Completed"))>0,"Completed","Incomplete")

True values are returned in all my tests.

First problem;

In the next column (beside the one in my forumla), I'd like to display a specific column value (it's a date) from any records that meet the criteria i.e =1

Second Problem;

Some employees have done the same training twice i.e. >1. I'd like the above formula to return the most recent date of all the records that are counted. I've read about the MAX function but can't use it until I have a formula that returns a value (Problem 1)

I'd love to post a sample of the workbook, but it'll take more time than I'm willing to give to mock-up values. Let me know if you want me to post the actual spreadsheet.

Please help!!!!!!

Hello Excel Gurus,

I am trying to return a value based on multiple criteria using a nested IF statement. Basically, I have a table that contains costs for a direct mail campaign, and I am trying to return the cost of the program based on the values that can be selected in two separate cells which both have drop-down lists. There are 20 different possible values that can be returned based on the possible combination of selections in those two separate cells.

If you look at the attached spreadsheet, the cell that I am trying to return the costs to is cell B4 (Total Program Costs). The possible values that can be returned are all in column G.

The statement should return values based on two criteria:

1) It should compare the value of B2 with the values in Row F
2) It should also compare the value of B3 and compare with the values in Row A.

It should then return the value based on the way the table is set up.

So, if a user selects "Long Mailer" in cell B3, and 100,000 in B2, then cell B4 should return a value of $43,300.

If a user selects "Jumbo Postcard" in cell B3, and 25,000 in B2, then cell B4 should return a value of $11,500.

I attempted to solve the problem using a really long nested IF statement using the AND command as well. I got an error message in Excel telling me that I had exceeded the max amount of levels in a nested statement. I am assuming there is a more elegant way to do this, but I cannot figure it out.

Help!

Thanks.

Hello all, first time poster and excel novice. Thank you in advance for any assistance!

What is the best formula for populating a cell based on multiple number ranges in the value of another cell? For example:

"XX1"
10002296074 to 10002319526

"YY2"
22038866-0-1 to 22313712-0-1

If the value of cell A1 is within the range of "XX1" then the formula would fill cell A2 with the text "XX1" If the value of cell A1 is within the range of "YY" then the formula would fill cell A2 with the text "YY2"

I know this seems simple, but the ranges are not similar and I would ideally like to have the formula check 7 ranges. I was able to get some functionality in using an IF statement, but the number ranges with "-" seemed to present an issue. This is the clunky formula I have been attempting to utilize:

I am assuming there is a more elegant solution to having the formula match the number within a range.  I contemplated
creating an individual worksheet for each number range (ie a sheet for each range of numbers corresponding to XX# and a sheet
for each YY#) and using VLOOKUP, but am unsure if that would even be possible - having VLOOKUP search multiple worksheets to
return the various values.

Thank you for reading!

I would like to sum a column of values listed in a table based on multiple criteria. I have created cells for the user to input the varying criteria. I'm trying to use a sumproduct formula to evaluate the criteria and calculate the filtered summation. The problem that I have is that not all the criteria will be used. If one of the criteria inputs is left blank, the return value is always "0". How can I setup the formula to ignore the blank cells? Please let me know if there is a more efficient method to complete this calculation. Thanks for your help.

Here's the formula: =SUMPRODUCT((B4:B19=I3)*(C4:C19=I4)*(D4:D19=I5)*(E4:E19))

Hi there, I'm not sure how useful the title is but hopefully I can explain it a little more clearly -

I'm formulating a stock picking model for college; there are 100 assets and I give each asset a weighting based on multiple criteria across 50 periods (for historical testing).

I want the model to allocate 10% of the fund to each of the top 5 (50% total) and 5% to the next 10 (50% total) so it invests in the top 15 assets. This alloaction is for each period.

So far so good - I can get it to do all that.

However, each asset is classified into one of ten sectors or types and I don't want the model to invest more than 25% in each sector. I'm clueless on how to do this. Can anyone help?

Does that make sense?

My data is sorted like this, with the weightings in the table:

Asset1 Asset2 Asset3 ... Asset100
Sector 1 1 4
Period 1
Period 2
Period 3
.
.
.
Period 50

Thanks all!

I have this code that I pulled from another thread here (IIRC) and the code works fine for what I want, but I need one more level - I need to be able to have it do this same operation based on multiple criteria.

For example:
If col:15= "E" paste to sheet EGC,
then If col:15= "R" paste to sheet Reviewed,
then If col:15= "D" paste to sheet Dispute
etc

Thanx! =)


	VB:
	
 Extract_Data() 
     'this macro assumes that your first row of data is a header row.
     
    Application.ScreenUpdating = False 
    Dim FilterCriteria 
    Dim CurrentFileName As String 
    Dim NewFileName As String 
     
    Set a = ActiveSheet 
    Selection.AutoFilter Field:=15, Criteria1:="E" 
    Selection.SpecialCells(xlCellTypeVisible).Select 
    Selection.Copy 
    Sheets("EGC").Select 
    Set b = ActiveSheet 
    Range("A1").Select 
    ActiveSheet.Paste 
    Range("A1").Select 'unselect everything
    Application.CutCopyMode = False 
    a.Select 
    Selection.AutoFilter field:=15, Criteria1:=FilterCriteria 
    Selection.SpecialCells(xlCellTypeVisible).Select 
    Selection.AutoFilter field:=1 
    Selection.AutoFilter 
    Range("A1").Select 
    Application.ScreenUpdating = True 
    End 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Please help me create formula/condition to colour fill different cells based on multiple Criteria. Very complicated for me and your assistance is greatly appreciated. Please see attachment, with further explanation.

Hi There

This is sort of a Continuation of my earlier post, Titled
Formula: Second (nth) Smallest Based on Multiple Criteria

Thanks to you all, my Problem Then was solved, but now I am having a Problem on the same subject, I will try to Exp[lain my problem through an example.

Let's say I have a database of Different vendors for Piping Industry, Let's say I have Few Vendors For Turbine Gas Meters, and they are as Follow;

TYPE****** COMPANY* SIZE* CLASS * EX-WORK* Date*****
METER, G160 - VEMM TEC - 4"- 150 - 1488.5 - 31-Jul-05
METER, G160 - ELSTER - 3" - 600 - 1375 - 7-Aug-05
METER, G160 - ELSTER - 3" - 600 - 1479 - 9-Aug-05
METER, G160 - ELSTER - 3" - 600 - 1570 - 12-Aug-05
METER, G160 - VEMM TEC - 8" - 150 - 3669.5 - 31-Jul-05
METER, G160 - DANIEL - 3" - 300 - 1450 - 5-Oct-05

Here is my question, Using The Small and array function I was able to Locate the Least Expensive Let's say
GAS TURBINE METER, G160, Class 600, size 3", But from the above Table, as you can see For a vendor by name of ELSTER I have 3 different prices in 3 Different Time (Date), what I want to do is to Choose the latest (12-Aug-05) and Price of my Turbine from the ELSTER, And disregard the Others And Then, compare that Price with Latest from the Other Vendors and The Finally Choose the Least Expensive Cost form the Latest entries of my Vendors.

Regards,
jhonExcel

I am trying to find a way to retrieve / lookup multiple results based on multiple criteria. I would normally do this with AutoFilters or a PivotTable, but I want the spreadsheet to be more user-friendly than that.

I have managed to create an array formula that returns all values based on one criteria, but cannot seem to adjust that formula to work with multiple criteria.

Please see the attached document as an example of what I am trying to do. The green box is what I successfully did with one criteria. The yellow box is where I'm stuck in building a formula that will bring back all the results that meet two criteria.

Any ideas? Thanks much.

guys can yu please tell me how to delete values in some cells based on a criteria..using filters etc. will delete the whole row but i only want to delete cells..do i have to use VBA or excel has some feature that can help me..thanks for the yur help with my earlier question AK...


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