Free Microsoft Excel 2013 Quick Reference

I am looking at column 9 ("I") and 16 ("P"). I went to add +1 to the value in column P in case I have either "OGBL", "OGBM" or "OGBL" in the corresponding row in column P. Otherwise I want to keep everything intact. Please note that some values in column I are "#N/A". I wrote the following loop for this, however, it gives me error. Any suggestions regarding this would be appreciated:

VB:Sub hey() Dim LastRow As Integer With Workbooks(ReportBook).Sheets(ReportSheet) LastRow = .Range("g65536").End(xlUp).Row On Error Resume Next For n = 2 To LastRow ticker = Workbooks(ReportBook).Sheets(ReportSheet).Cells(n, 9) Select Case ticker Case ticker = "OGBS" .Cells(n, 16).Value = .Cells(n, 16).Value + 1 Case ticker = "OGBM" .Cells(n, 16).Value = .Cells(n, 16).Value + 1 Case ticker = "OGBL" .Cells(n, 16).Value = .Cells(n, 16).Value + 1 End Select ' Next n End With End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

- Nested IF statement to return a value based on multiple criteria
- Returning specific values based on multiple criteria in a tablewith duplicate entries
- Summation of values based on multiple criteria & ignoring blanks
- How can I have a formula result based on multiple criteria/columns
- Return a value based on multiple criteria
- Count unique items based on multiple criteria and making it easily reproducible
- How can I have a formula result based on multiple criteria/columns
- Parsing Data based on multiple criterias
- HELP| populate cell with particular value based on multiple condit
- HELP| populate cell with particular value based on multiple condit
- Determine Cell Value Based On Multiple Criteria In Two Columns
- Formula to return cell contents based on multiple conditions
- Cell value based on multiple conditions
- Formula to return cell contents based on multiple conditions
- Cell Value Based On Another Value Being Within Numeric Ranges
- Overwrite Input Based On Multiple Criteria
- Counting Rows Based On Multiple Criteria Found In Various Columns
- Clear cell contents based on multiple criteria
- Is there a way to delete a cell value based on a condition?
- Look up value based on Multiple criteria
- Finding top 2 values per group based on multiple criteria
- SUMIF based on multiple date criteria
- Return array based on multiple criteria
- Populate Cell Based on Multiple Criteria

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.

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

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

A/status B/date C/expense type D/amount

1 allowed 05/07/06 car Â£20.00

2 notallowed 05/07/06 car Â£450.00

3 notallowed 05/07/06 car Â£15.00

4 notallowed 05/07/06 car Â£26.00

5 allowed 05/07/06 post Â£20.00

6 allowed 05/07/06 post Â£20.00

7

8 total car allowed

Â£--.--

9 total post allowed

Â£--.--

10

11 total car notallowed

Â£--.--

12 total post notallowed Â£--.--

what formula can I use to say total all instances of "car" & "allowed"...

or of "car" & "not allowed"

I have tried =SUMIF, but it will only recognise the first column in the

range..

eg in D8 I wrote:

=SUMIF(A1:C6, "allowed""car", D16)

but it will not recognise multiple criteria ie "allowed" & "car".

how can i total the values based on multiple criteria in different columns?

thanks in advance

nicky

if A1 < 75 return "9" in B1

if A1 is between 75 & 95 return "3" in B1

if A1 is > 95 return "1" in B1

Thanks in advance.

Mike

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

A/status B/date C/expense type D/amount

1 allowed 05/07/06 car Â£20.00

2 notallowed 05/07/06 car Â£450.00

3 notallowed 05/07/06 car Â£15.00

4 notallowed 05/07/06 car Â£26.00

5 allowed 05/07/06 post Â£20.00

6 allowed 05/07/06 post Â£20.00

7

8 total car allowed

Â£--.--

9 total post allowed

Â£--.--

10

11 total car notallowed

Â£--.--

12 total post notallowed Â£--.--

what formula can I use to say total all instances of "car" & "allowed"...

or of "car" & "not allowed"

I have tried =SUMIF, but it will only recognise the first column in the

range..

eg in D8 I wrote:

=SUMIF(A1:C6, "allowed""car", D1:D6)

but it will not recognise multiple criteria ie "allowed" & "car".

how can i total the values based on multiple criteria in different columns?

thanks in advance

nicky

Here is how the part of the raw data locks like:

Column A Column B Column C Column D Column E Server Name Backup Date Start Time Elapsed Time Backup Size Server 1 05.04.2012 18:00:00 00:30:00 100 MB Server 1 05.04.2012 18:10:00 00:50:00 50 MB Server 2 05.04.2012 18:30:00 00:15:00 75 MB Server 2 05.04.2012 18:15:00 00:30:00 110 MB Server 1 06.04.2012 00:15:00 01:00:00 1000 MB Server 2 06.04.2012 18:30:00 00:15:00 50 MB Server 2 06.04.2012 18:15:00 00:20:00 115 MB Server 1 06.04.2012 18:00:00 00:25:00 100 MB

Here is what I have to provide:

Backup sessions that are running within 1-2 hours period can be considered as running simultaneously so there time can be calculated as follows:

(The Largest Start Time + Corresponding Elapsed Time) - Smallest Start Time = Backup Time Interval

So for Every Date such Time Interval I have to provide. Like This:

Column A Column B Column C Backup Date Backup Size Backup Time 05.04.2012 335 MB 01:00:00 06.04.2012 1265 MB 01:45:00

Backup Size for 05.04 is a SUM of all backup sizes for 05.04 that i have calculated with "sumifs" like this:

SUMIFS(

'RAW Data'!E:E;

'RAW Data'!A:A;"Server 1";

'RAW Data'!B:B;"05.04.2012";

)

But following part I cannot figure it out:

Backup Time for 05.04 is = (18:10:00 + 00:50:00) = 19:00:00 so 19:00:00 - 18:00:00 = 01:00:00 or 60 min. with is largest time interval (example: 18:10:00 - 19:00:00 > 18:00:00 - 18:30:00 or 18:30:00 - 18:45:00)

Backup Time for 06.04 is = ((((00:15:00 + 01:00:00) = 01:15:00) - 00:15:00) = 01:00:00) + ((((18:30:00 + 00:45:00) = 18:45:00) - 18:00:00) = 00:45:00) = 01:45:00

I want to populate cell B1 with a particular value based on multiple

conditions in cell A1. I need cell B1 to do the following:

- If cell A1 = "Dog" then the value of B1 should equal the value in cell E21

- If cell A1 = "Cat" then the value of B1 should equal the value in cell F21

- If cell A1 = "Mouse" then the value of B1 should equal the value in cell G21

how the heck do i do this? is this even possible?

many thanks,

doon

I want to populate cell B1 with a particular value based on multiple

conditions in cell A1. I need cell B1 to do the following:

- If cell A1 = "Dog" then the value of B1 should equal the value in cell E21

- If cell A1 = "Cat" then the value of B1 should equal the value in cell F21

- If cell A1 = "Mouse" then the value of B1 should equal the value in cell G21

how the heck do i do this? is this even possible?

many thanks,

doon

I need some help in determining how to go about getting information populated in a cell. In one column I have various codes for Instrument Types. In the next two columns I have a S&P rating and a Moody's Rating. I'm trying to populate a percentage amount in another column based on what is reported on in the first three columns.

The Criteria I have been given in layman's terms is as such:

If column A is either "ABS", "CMP","CMV","CRF","CRP", or "CRZ",

AND the corresponding cell in Column B has "AAA*","Aaa*","Aa*","A*","BBB*","Baa*" where the asterisk denotes wildcards,

AND the corresponding cell in Column C also has "AAA*","Aaa*","Aa*","A*","BBB*","Baa*",

Then the value in Column R is good and should not be altered,

Otherwise the value in Column R should be .15 .

This is somewhat related to my previous post below, but I was given additional criteria to work with and I can't get my head around it. http://www.ozgrid.com/forum/showthread.php?t=93124

Any help would be very appreciated.

Thanks!

JL

FYI....Here is the code I've been playing around with for the past couple of hours. Looks logical to me, but what do I know. I must be doing something horribly wrong as it's not coming up with my expected results.

VB:Thanks again for taking the time to look at this.MyTry() Dim c As Range For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row) If c.Value = "ABS" Or c.Value = "CMP" Or c.Value = "CMV" Or c.Value = "CRF" Or c.Value = "CRP" Or c.Value = "CRZ" _ And Left(c.Offset(0, 1), 1) = "A" Or Left(c.Offset(0, 1), 3) = "BBB" Or Left(c.Offset(0, 1), 3) = "Baa" _ And Left(c.Offset(0, 2), 1) = "A" Or Left(c.Offset(0, 2), 3) = "BBB" Or Left(c.Offset(0, 2), 3) = "Baa" Then 'Do Nothing Else: c.Offset(0, 7) = 0.15 End If Next c End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

JL

a named range based on multiple criteria. I've written similar formulas in

the past, but this one escapes me. I've narrowed it down to a simple

example, which if I can get this to work, I can apply the knowledge to my

more complex spreadsheet.

Given the following data table with named ranges the same as the column

headings in Row 1:

-A- -B- -C-

1 Name Num Mon

2 Dale One Jan

3 Barb One Feb

4 Dale Two Mar

5 Barb Two Apr

I want to return the cell value of the Mon column that corresponds to a

particular Name and Num, resulting in a grid that *should* look like this:

-A- -B- -C-

1 One Two

2 Barb Feb Apr

The incorrect array formulas I currently have in B2 and C2 a

B2 formula: {=IF((Name=$A2)*(Num=B$1),Mon,"X")}

C2 formulat: {=IF((Name=$A2)*(Num=C$1),Mon,"X")}

I've also tried:

B2 formula: {=IF(AND(Name=$A2,Num=B$1),Mon,"X")}

C2 formula: {=IF(AND(Name=$A2,Num=C$1),Mon,"X")}

When tracing the evaluation of these formulas, everything seems to work fine

until the final step. It just doesn't seem to want to select an appropriate

single cell to return from the Mon range. Instead of the appropriate month

text, it displays the X.

What's the right way to write these formulas?

Thanks in advance,

Bill

I'm not very savvy with IF statements, which is how I imagine this can be done, and I don't know if this would be easier to do as a macro or as a formula within G. Since the contents of the of the cells will be changing on a weekly basis, I'd prefer the formula to only be there if there is content on the line, so we don't have nulls showing.

I appreciate any help I can get.

a named range based on multiple criteria. I've written similar formulas in

the past, but this one escapes me. I've narrowed it down to a simple

example, which if I can get this to work, I can apply the knowledge to my

more complex spreadsheet.

Given the following data table with named ranges the same as the column

headings in Row 1:

-A- -B- -C-

1 Name Num Mon

2 Dale One Jan

3 Barb One Feb

4 Dale Two Mar

5 Barb Two Apr

I want to return the cell value of the Mon column that corresponds to a

particular Name and Num, resulting in a grid that *should* look like this:

-A- -B- -C-

1 One Two

2 Barb Feb Apr

The incorrect array formulas I currently have in B2 and C2 are:

B2 formula: {=IF((Name=$A2)*(Num=B$1),Mon,"X")}

C2 formulat: {=IF((Name=$A2)*(Num=C$1),Mon,"X")}

I've also tried:

B2 formula: {=IF(AND(Name=$A2,Num=B$1),Mon,"X")}

C2 formula: {=IF(AND(Name=$A2,Num=C$1),Mon,"X")}

When tracing the evaluation of these formulas, everything seems to work fine

until the final step. It just doesn't seem to want to select an appropriate

single cell to return from the Mon range. Instead of the appropriate month

text, it displays the X.

What's the right way to write these formulas?

Thanks in advance,

Bill

I need to change a cell value based on this conditional criteria: (not for all worksheet, only for the cells I want to select)

if cell value is bettwen 0-10 then change another cell value to 0

if cell value is between 20-40 then change another cell value to 1

if cell value is between 50-80 then change another cell value to 2

if cell value is between 90-120 then change another cell value to 3

etc.

ex. if A2 = 10 then change A3 = 0

if B2 = 30 then change B3 = 1

if C2 = 100 then change C3 = 3

or if it is possible to change the same cell:

if A2 = 10 change A2 = 0

if B2 = 30 change B2 = 1

if C2 = 100 change C2 = 3

+++++++++++++++++++++++++++++++++++++++++++++++++

thank you, Mr. Dave Hawley

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.

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

of day, the cell value in A2 will be deleted...

In cells L8:Q44 are where i want the lookup formula on sheet 7-4-10.

The value it needs to look up are in cells D8:D44 (on sheet 7-4-10) and needs to look in Sheet "Weekly Total" in Column D cells D712:D1500.

The other value it will need to look up are in cells L7:Q7 (on sheet 7-4-10) and needs to look in Sheet "Weekly Total" in Column B cells B712:B1500.

And what I need cells L8:Q44 in sheet 7-4-10 to return is the value in Column H cells H712:H1500 on sheet "Weekly Total".

If There's no value to return then i don't want anything in the cells on sheet 7-4-10.

Thanks in advance

Windows XP

Excel 2003

As an Example, here's what I'm trying to do:

Make Model Rating

Ford Bronco 64

Chevy Corvette 94

Dodge Intrepid 83

Chevy Chevette 34

Dodge Viper 72

Ford Escape 21

Ford Expidition 53

Chevy Impala 67

Ford Fairmont 11

Dodge Dart 33

..and from the list above, I want to get the top two rated Fords, Dodges, and Chevy's.

I have a search that is giving me only the top rated across all Makes using Indirect/Match, but that isn't giving me the top two PER Make.

Since I've failed to come up with a working solution, I implore the Excel Function wizards here to help me . I very much appreciate it!

I would like to know if there is anyway i can use a cell reference to the dates i specified in row 1 of cashflow worksheet, rather than manually entering in the dates in the formula. This will help when i have to change the dates in row 1. I tried to enter C1 and D1 as "datevalue" to see if that would help...

Any suggestions are appreciated.

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

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!