Free Microsoft Excel 2013 Quick Reference

Conditionally increment cell value based on multiple criteria

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 Sub 

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


Post your answer or comment

comments powered by Disqus
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.

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

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

I have an expenses sheet set up as follows:

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

I need to create a formula to return a value based on 3 criteria.

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

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 have an expenses sheet set up as follows:

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

I have huge amount of raw data in excel 2010 spreadsheet and I must filter it in order to make some reports from it. So far I made some of the filtering but hit a rock when I had to compare multiple values based on multiple criteria.

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 am not sure how to phrase this, but this is what i am after...

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 am not sure how to phrase this, but this is what i am after...

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

Hi All,

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:
	
 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 Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks again for taking the time to look at this.

JL

I'm trying to write a formula to display the contents of a cell found within
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 need to create a macro that will calculate a value based on the contents of multiple cells. Looking at the example attached, if columns A and/or B (employee ID and name) are empty, then allowable OT (G) should be 0. If either have data, then if Stage (F) is CAN, G should be 20. If Stage is FAS or FAR, G should be 10.

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.

I'm trying to write a formula to display the contents of a cell found within
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

Please I need some help... Thank you very much...

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

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.

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

Is there a way to delete a cell value based on a condition? i.e. Each change
of day, the cell value in A2 will be deleted...

Can someone help me out with this problem. I'm trying to have a cell lookup up a certain value based on three different criterias. Here's what i have,

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

I am trying to find the top two values per group based on multiple criteria. The list I'm working with is not sorted and would be better for it to not have to be sorted as on-the-fly sorts will likely often occur from the raw data and I wouldn't want that to mess up the results I'm looking for here.

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 have a somewhat suitable formula to enter values based on multiple criteria into a worksheet from other worksheets in my workbook.

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.

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

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!


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