# IF And OR Statement (populate A Cell Based On Which Number Meets The Criteria I Define)

Dec 26, 2008

I am trying to populate a cell based on which number meets the criteria I define. This is based on sales revenue, so if the revenue is less than \$6.5M, I want to use a certain value. If the value is equal to \$6.5M but less than \$8M I want to use another value and finally if the revenue is greater than \$8M i want to use another value. Here's my formula, but it returns \$0.

=IF(B2<6500000,Bonuses!J45)*OR((B2>6500000)*AND(B2<8000000),Bonuses!F45)*OR(B2>=8000000,Bonuses!B45)

## Define A Range For SumProduct Function Based On An If Statement?

May 31, 2006

I want to calculate a weighted average but I need it to only calculate on the months to date. I have a data validation drop down on a title page that is toggled to the current month each time a report is needed to define what months have actuals. I bring in data for all the months but only the current months have actuals. I need to calculate the weighted average on ONLY the ACTUALS. How can I set the ranges for sum product based on the data validation list on the title page?

## Formula That Counts Number Of Unique Text Values If Meets Criteria

Nov 6, 2013

I have been using this function to count the number of unique text values in a data set:

=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))

It works great if I want to count number of unique text values overall. However, I want to count the occurrences of unique text values if they meet specific criteria. (Like a countifs function would if it could count unique text).

## Populate Cell Based On Two Criteria?

Aug 2, 2013

I have a list of teams, and beside them I need a calculation to be filled.

The criteria is based on a cell reference (a month which can be changed from a drop-down in BM2) and also the name of the team.

dummy 16.xls

## Populate Cell Based On Multiple Criteria

Dec 7, 2006

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.

## If Statement Based On Various Cell Criteria

Aug 11, 2009

G5 - can contain True or false
G6 - contains text but the if is on the basis of this cell being "Hand Delivered" or not

I have tried the following to illustrate what I after:

## Coying A Cell Value If It Meets A Criteria

May 12, 2009

i am trying to get my excel formula to say if 2 date ranges in my excel workbook are the same (in seperate worksheets) to put a value from a certain cell into another cell in another worksheet. This is all in the same excel application...

The first step i have taken are:

1) if the 2 date ranges are the same to say 'true' if not 'false'

I am trying to get a forumula to say "if it is true to put a value from a different cell into another cell (both in different worksheets) but the same excel application...? but if its false to move on to the next date

## Count Single Cell If Meets Criteria?

Apr 12, 2012

I am trying to come up with a simple formula to count a single cell if it contains either a 2 or 3.

The cell can contain numbers ranging from 0-8.

Even better would be if I could some how evaluate single cells based on the contents and then count the number of cells where the criteria for contents is true. The problem is the criteria differs from cell to cell (i.e. D2 could = 2 or 3, but E2 needs to be counted only if it contains a 4 or 5).

I know COUNTIFS only evaluate a range but it would be perfect if I could somehow get it to work for single cells. COUNTIFS(D2,2,D2,3,E2,3,E2,4,F2,2)

## Delete Formula If Cell Value Meets Criteria?

Feb 18, 2014

I'm using the following on the Worksheet_Activate event to update rows with the date, (Cell AD1 contains the current date):

Code:

Is there a way I can amend it so it only updates those cells which currently have no data?

At the moment it is changing every cell with the date which overwrites existing dates - I want it to update the new stuff only.

## Formula To Return Cell That First Meets Certain Criteria?

Mar 5, 2014

Say I have a column of cells filled with numbers that are ascending. What I would like to do is to pick out the first cell that meets my condition. For instance, these cells contain irrational numbers that increase from 0 to 100. I would like a formula to pick out the first cell that exceeds 10.2, and to return the row number of that cell.

In the case of descending numbers, if I would like to pick out the first cell that goes below 10.2, would the formula be the same?

## Delete Nth Row In All Worksheets If Cell Meets Criteria

May 21, 2008

I want to scan all sheets in a workbook and to delete a first row where a value in cell(1,1) is "table".

Sub DeleteFirstRowInWorksheet()
Dim SheetName As Worksheet
Dim i As Integer

For Each SheetName In Sheets
If Range("A1") = "table" Then
Rows("1:1").Select
Selection.Delete Shift:=xlUp
End If
Next SheetName
End Sub

It delete only in an active sheet. What's wrong?

## Sum Cells If Another Cell Meets Criteria And Stop When Count Is Met

Jan 14, 2009

We were so close!. But it appears that the assumed correct answer only works if there are no repeating N. The repeating N gets the same count as the last Y and it throws off the sum ....

## Color Cells Where Cell Below Meets Date Criteria

Nov 21, 2007

I´m trying to do a macro that changes the color of all fonts in a row if the cell in the column "R" has the date lower than a specific one, something like this:

Dim Data2 As Date
Data2 = Sheets("Sheet1").Range("today")
Range("R12").Select
Do While ActiveCell <> ""
If ActiveCell < Data2 Then
ActiveCell.EntireRow.Select
Selection.Font.ColorIndex = 3
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop

But it just don´t work. The macro does that in the first row, than it stops. Do you have another code for this, or another way to do?

## Count Unique Values If Adjacent Cell Meets Criteria

Apr 30, 2014

In the picture below, I need a formula in column E to count the unique occurrences in column A (excluding blanks) if its corresponding value in column B (B1 value) matches that in column D (B2 value). Currently column E is showing the values I would want the formula to return.

Capture.PNG

At the moment I have a formula as below:

=SUMPRODUCT(--(B:B=\$D2),--(A:A<>""))

this will do a countif in column A if column B matches the value in column D, but would not weed out duplicates for me.

Modifying my formula. I have attached the sample workbook below.

Book1.xlsx

## If Cell String Meets Criteria Then Clear Contents From Array On Same Row

Jul 17, 2013

I'm attempting to clear the contents from a range of cells on rows where a cell string may equal R, X, XX, Y, Z, ZX, #N/A.
The macro runs fine until it gets to a cell that contains #N/A. How to get this to work?

Sub Recalculate()
Dim r As Integer
r = ActiveSheet.UsedRange.Rows.Count

Application.ScreenUpdating = False

For Row = 13 To r

[Code] ........

## Refernceing Cells: Formula That Looks At A Cell On A Front Sheet, And Then Returns The Contents Of That Cell As The Result If It Meets The Criteria

Apr 5, 2009

I have a formula that looks at a cell on a front sheet, and then returns the contents of that cell as the result if it meets the criteria. So for example this formula would be in Cell A1 on Sheet2 IF(SHEET1!A1,"New",Sheet1!A1,"-")

This formula is always in the same cell (different sheet) as the cell that it is looking at, down 1500 rows. Instead of having the formula named for each cell is there anyway to ask excel to 'look at this cell but on this other sheet'.
e.g IF(Sheet1!"This Cell" etc). That way no matter what cell you put the formula in it is always referencing the correct cell for the formula?

## Possible To Define Range For Combobox Input Based On Number Of Non-blank Cells?

Nov 25, 2013

Rather than having to give it a range, I'd like to have a generic range giving me room to add or subtract values in the column without changing this statement.

Code:

Worksheets("Sheet1").Shapes("Combobox2").ControlFormat.ListFillRange = _
"O2:O14"

## Populate Value Based On Criteria

Jun 2, 2008

I have a spreadsheet as below:

Sheet1 *BCDEFG3QAUSERQAUSERQAQA4SRF RegistrationWorkflow-UserWorkflow-QAWorkflow-User FeedbackORMC 1ORMC 25CompletedCompletedCompletedCompletedCompletedPending Excel tables to the web >> Excel Jeanie HTML 4

I need to create a formula that would read from cell B5 to G5 and the first "Pending" it finds, it should populate the corresponding value from B3 to G3.

In this case, since from B5 to G5, the first "Pending" is in cell G5, the formula should return as "QA".

## VBA To Populate Data Based On Criteria?

Jul 16, 2014

I need to populate data based on criteria.

The criteria has been attached as follows.

Sample Test.xlsx

## Lookup Based On Criteria And Populate?

Jan 15, 2012

I have the following in sheet 1:

Sheet1 *BCDEFGHIJKL1NameLast DoneMarket Cap (M)PEPrice/NAVHigh, 52 WksLow, 52 WksRevenue GrowthCurrent RatioDebt to EquityROA2AMMB \$ * * * 5.76 \$ * * * *17,361.71 12.8861.633 \$ * * * * * *7.05 \$ * * * * * 5.30 10%1.20.42%3CIMB \$ * * * 7.27 \$ * * * *54,036.27 14.8372.156 \$ * * * * * *9.01 \$ * * * * * 6.56 3%20.61%4RHBCAP \$ * * * 7.31 \$ * * * *16,022.91 11.0761.435 \$ * * * * *10.40 \$ * * * * * 6.53 6%3.20.83%5HLFG \$ * * 11.70 \$ * * * *12,120.91 7.2581.576 \$ * * * * *13.74 \$ * * * * * 8.44 4%1.711%

I have the following in sheet 2:

Sheet2 *BCDEFGHIJKL2Parameters**********3Last Done**********4Market Cap (M)**********5PE**********6Price/NAV**********7High, 52 Wks**********8Low, 52 Wks**********9Revenue Growth**********10Current Ratio**********11Debt to Equity**********12ROA**********13***********14StockLast DoneMarket Cap (M)PEPrice/NAVHigh, 52 WksLow, 52 WksRevenue GrowthCurrent RatioDebt to EquityROA15***********

1)I need to create a formula to populate all stocks that satisfies the parameters that I will input in cell C3 to C12 in sheet 2 based on the data in sheet 1.

2)The stocks should then be populated in cell B15 downwards in sheet 2. If there are 20 stocks that satisfies the parameters in cell C3 to C12 in sheet 2, then all 20 stocks should be populated in cell B15 downwards in sheet 2.

3) The respective details of the stocks should also be populated in cell B15 to L15 for all stocks that satisfy the parameters entered in cell C3 to c12 in sheet 2.

## Populate Worksheet From Another Based On Criteria

Dec 18, 2006

I have a master list of people. (call it master) I have 1 other worksheet that should contain everyone aged 15 and up (call it must progress). One of the columns on the master show's their ages. I was hoping excel would automatically populate the sheet based on the age from the master list. So, for example. On the Master list, Joe Smith is 17. I would like Each cell in the Joe Smith Row to automatically copy itself to the must progress worksheet because his age is over 15. The formula would be based off the age on the master.

## VBA To Populate To Specific Cells Based On Criteria

Jul 6, 2014

I am looking for a VBA that could populate from column (A2:A5) to cells (B2:F5) as in the attached spreadsheet, based on the frequency, in a five year time-frame.

screen_print.JPG

[Code] .....

## If Statement Based On Multiple Criteria

Dec 23, 2013

I am looking for a way to say

IF A2>9 then B2 returns bronze
IF A2>24 then B2 returns Silver
IF A2> 49 then B2 returns Gold
IF A2>99 then B2 returns Platinum

## Populate Data In Table Based On Dropdown Criteria?

Jul 7, 2014

I've been trying to get a table to populate based on a couple of criteria. However, I've not come-up with the solution yet.

I have my dropdown selections in cells C2 & C3. The objective is to populate the table below the dropdown with data from the sheet named (very unimaginatively) 'Data'. Currently you see the selection AA-11 & Mar-14 in the Contract ID & Month cells. If I change this, the table below should auto-populate.

I've attempted using Vlookup, Index-Match. But it does not give me the desired result.

Also, the number of Products can change each month (although the file shows 4 for each month & each Account).

## VBA Code To Auto Populate Fax Template Based Criteria

Aug 14, 2014

I am after a VBA code that I can use to populate a fax template based on criteria's

Basically I have records which are being logged throughout the day via a User form and sometimes there may/maynot be a charge. the user decides at the time. The column is named "to be charged" and is filled with either Yes/ No.

I need to be able to select the date to print or Just the same day date and print all faxes' that have "Yes" for charge This way all the information for each fax is populated and the user can just print.

and if possible mark a Colum non the master sheet as printed with a X or something

## To Merge Variable Number Of Cells Into One Single Cell Based On Criteria

Sep 19, 2009

Here’s an example of my data:

A B
1110AAAAAAA
2220BBBBBBBB
3330CCCCCCCC
4330DDDDDDD
5330EEEEEEEEE
6440FFFFFFFFF
7440GGGGGGG
8550HHHHHHH

I need your help to figure out how to merge the above data to look like this in new blank worksheet using a VBA macro:

A B
1110AAAAAAA
2220BBBBBBBB
3330CCCCCCCC
DDDDDDD
EEEEEEEE
4440FFFFFFFFF
GGGGGGG
5550HHHHHHH

## MAX If Meets 2 Criteria

Jul 15, 2014

I am having problems getting a formula to calculate the max if it meets 2 criteria. MAX of time listed by 1/past a certain date. 2/are of a specific client.

Here is my formula...
{=MAX(IF(All_Tickets_07012014!\$D\$2:\$D\$155685>1/1/2014,IF(All_Tickets_07012014!\$O\$2:\$O\$155685=\$A2,All_Tickets_07012014!\$G\$2:\$G\$155685)))}

The data...
\$D\$2:\$D\$155685 - is the list of dates per ticket.
\$O\$2:\$O\$155685=\$A2 - Are a variety of clients, where \$A2 is a name of one of the clients.
\$G\$2:\$G\$155685 - Being a range of time stamps.

The formula runs, however it only shows the MAX of all entries, not within the specific date range...

## Getting Last Value From Column That Meets Certain Criteria

Jul 8, 2014

In theory I know what I should do, but I don't know the syntax. So here it is:

There are 1450 unique records in my XLS, every record contains 45 different rows(these are the phases) with their position(1....45). Every row has a status (Not Started, In Progress, Complete)

COLA(uniqueid)....COLi(it is a number, it is the position).....COLN(status)

id1....1......status
id1....2......status
id1....3......status
.
. .
. .
id1 ....45.....status

id2.....1.....status
.
.
.id(n)

basically I would like to get the last "in progress", If not found, the last "Complete, If not found then the first "Not Started". and put a "Y" right next to the row to a new column for all the groups(45 rows)

## Find The Last Row Which Meets Criteria??

Feb 28, 2009

s/s has 325501 rows. Column C contains names of people (whether present or not -I enclose small attachment to illustrate).
Column J contains scores (if present). I need column N to list the last row number where each column C name scored points (not just when heshe was last present). I think I need macros which I can fill down both columns (??).

## Sum Range When Another Meets Criteria

Mar 24, 2008

In the attached Exel work book I have work sheets named

Material Usage – Usage of materials
Estimate - calculate the Total material cost for a job
Material Cost – defines the material cost for each material type

In “Estimate” worksheet Job number is repeated but sub jobs falling under a particular job number is unique. Materials used for each sub job is different.

Once the job number is selected from the list box , I need to calculate the total material cost for each job. I tried sumif function but I don’t know how to get it to look up for each material type and get the sum .