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.

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.

- Count unique items based on multiple criteria and making it easily reproducible
- Counting and Suming based on multiple criteria
- Overwrite Input Based On Multiple Criteria
- Counting Rows Based On Multiple Criteria Found In Various Columns
- How do you create COUNTIF functions based on multiple criteria?
- Counting Blank Cells Based On Two Criteria
- How to count unique values in excel based on multiple criteria
- Count Unique text based on multiple conditions
- Return array based on multiple criteria
- Copy and paste autofilter data based on multiple criteria to another worksheet
- Populate Cell Based on Multiple Criteria
- Array Formula based on multiple criteria
- Clear cell contents based on multiple criteria
- Merging Cells based on some criteria
- Returning specific values based on multiple criteria in a tablewith duplicate entries
- Nested IF statement to return a value based on multiple criteria
- Formatting a cell based on multiple data ranges
- Summation of values based on multiple criteria & ignoring blanks
- Ranking based on multiple criteria and upto a maximum cap
- Auto-filter and paste to new sheet based on multiple criteria
- Color Fill Cells Based On Value Being Between 2 Values
- Formula: Second (nth) Smallest Based on Multiple Criteria II
- Retrieve Multiple Results Based On Multiple Criteria
- Pls help!! deleting cells based on a criteria

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

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.

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

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.

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?

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

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

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

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

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

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!

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?

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:I am trying a slightly different approach with the code, but can not get my language right.=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

VB:Any suggestions?If Cells(r, 11).Value "" And Cells(r, 9) = "a" Then Select Cell (r,9) clear contents End IfIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thank you,

-Marc

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!!

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!!!!!!

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.

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!

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

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!

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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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 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.