# Formula For Students - Result Based On Multiple Criteria

Aug 5, 2014

How to formulate results of students in excel sheet.

From the attached picture (capture1.jpg) of the excel sheet - The rules of exams are:

1. if candidate scores 50% in all 4 papers, PASS and proceed to next year
2. if candidate scores 50% in 2 or 3 papers + borderline fail in 1 or 2 papers, VIVA VOCE exam for the borderline failed paper (Definition of borderline fail is candidate scoring 45 to 50 marks)
3. if candidate scores 50% in upto 2 papers + borderline fail in more than 2 papers, RE-EXAM
4. if candidate scores 45% in 2 or more papers, FAIL and repeat the year

The rules are in the attached picture flowchart.jpg

I am unsuccessful in writing a formula for such multiple criteria...

## Return Result Based On Multiple Criteria

Sep 19, 2009

I have been trying this for hours but to no avail.

I have a table with 4 columns headers
Name, Amount, Loc and Code

The name may look like ABC 1, ABC 2....
The Loc may be in US, GB...
and the Code may be AA, BB

I need to return a result "Y" if the sum of the amount is > 100
and "N" if the sum of the amount < 100 based on the conditions
of the following :if

1) Name is the same entity, such as ABC 1 and ABC 2 and
2) Loc is the same, US..and
3) Code is the same

I have attached a sample to illustrates the result

## Multiple IFs To Display One Result Based On Multiple Criteria

Mar 18, 2014

I've got a matrix combining actions on the left hand side (Col A) and owners on Cols B to F. I have drop down list for each action/owner combo: N/A, OK, KO, TBD.

Owner #1
Owner #2
Owner #3
Owner #4
Owner #5
Results

Action #1
OK
TBD
N/A
N/A
N/A
OK

[code]....

I would like to create a formula in Col G that does the following:If any of the values in columns B to F equals OK, then OKIf any of the values in columns B to F equals KO, then KO I've been tinkering with multiple IFs and quite rightly got bogged down as too many criteria to take into account...

## Color Formula Result Based On Criteria

Jan 4, 2007

I would like to use formulas to display different results eg UNDERBUDGET - OK - OVERLIMIT etc. Ideally they would be shown using different colours & text size. I can't seem to change the colour/text size inside the formula for the different words - only for the whole cell.

## Lookup Multiple Criteria & Sum Result On Multiple Criteria

Oct 21, 2006

I am trying to solve a problem. I am currently using this formula
= SUMPRODUCT(--(Sheet2!B2:Sheet2!B300="MARKETING"),--(Sheet2!D2:Sheet2!D300="200612"),Sheet2!E2:Sheet2!E300)

This formula works for me as it is but I would like to add more months to 200612. I want this to also be 200701 and 200702. In another cell there will be up to 10 months. Is there a way to do a Vlookup or something that will look up these months in another table, rather than keep typing them out in the formula?? Otherwise my formula will be very long.

So the info looks like this in excel
MARKETING 200612 -10
MARKETING 200701 -25
MARKETING 200708 -50
ECONOMICS 200709 -30

The info goes on and on. The two variables are the MARKETING column and the month column. My problem is that I would like a seperate table that can be the months. So 200612 and 200701 is one table, and 200708 and 200709 is another table. The table changes often so I dont want to mess with the formulas, rather a table.

## Formula To Count Based On Multiple Criteria

Dec 7, 2013

What I require : Column F to be blank as long as the following is true:

1) Column A (Order) cannot be blank.
2) Column E (Status) has to be equal to either "Completed" or "Successful".
3) Column D (Sales) has to have the same date as Column C (Contact), but due to bad data quality each order number can have several rows and as long as the Sale date from one of the other "Duplicate" rows equals the contact date it needs to be counted. This is as long as there is not already a row for the Order that already has Sales / Contact day matching.

In the attached spreadsheet, I have highlighted the rows that should be blank in column F in green. My attempts at formulas to count this are in the columns highlighted in red.

WORKAROUND.xls‎

## Array Formula Based On Multiple Criteria

Jul 20, 2006

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.

## Formula To Sum Values From Column Based On Multiple Criteria

Nov 29, 2013

I have attached an example workbook with a tiny subset of data and a number of criteria from the Dashboard Engine page removed.

What I need is to sum the total value for each division on the second sheet based on the date criteria (so for the first cell Jan 1/2010 - Jan 31/2010) and on a match between division name on the Dashboard Engine sheet table and the Masterdata sheet. I have tried a number of variations but keep getting a 0 for each return.

Sample Workbook.xlsx‎

## Formula To Count Based On Text / Multiple Criteria?

Jun 27, 2012

I can't seem to get a formula to work. I am trying to count the number of times "EE" shows up in a range only if "CR" is listed in that row within another range. For simplicity Column A has a list with the following values scattered from A2 through A100: CR, LT, ST, DI. Column B has a list with the following values scattered from B2 through B100: EE, EF, EG.

I need to know how many EE's are listed in column B if CR is on the same line in column A.

## Array Formula To Return Multiple Values Based On Various Criteria?

Dec 19, 2013

I can manage an array formula that returns a value based on a criteria. Simple. But I want to add in an additional couple of criteria. Now I'm stuck....

My sheet looks at a manually entered postcode, finds out what region this is in, and returns a list of postcode I have defined as being in that region. (So the postcode WF1 3JY would return a region of Yorkshire, and list postcodes of WF, BD, L, etc)

I also have a list of engineers, with a column for their home postcode.I want to be able to list all the engineers from my list whose home postcode matches any of the values on the already created list from the postcode and region entered. So far I have this, which finds me all the engineers for just one postcode area.

## Formula To Lookup And Sum Cells Based On Multiple Criteria In Row And Columns?

Jun 17, 2014

I would like to create a formula in a summary sheet ("sheet 2 section" in attached)that looks up and sum cells based on multiple criteria in row and columns in "sheet 1 section". I thought I sumifs would work, but I kept getting #value errors. I'm not a power user in excel. I attached the spreadsheet - it is only an example of what I want to do as the real data is confidential and large. The result I should I get is in section 2. Lookup account 12.251 for tim in the month of February - result is 14.

Test2014.xlsx

## Formula/Function To Return Multiple Values Based On Criteria

Aug 25, 2006

I would like to be able to use the Vlookup function to return more then one value as a result of the criteria. I have a cloumn of dates that populates the X axis of a gantt chart with data whilst the Y axis will be populated by a site reference resulting in a program of work, the Y axis data is the result of a vlookup function. My difficulty arises however with multiple sites, for instance where two or three sites will be visited on the same day. The vlookup function will only return the first value it finds in a range to the formulated cell. The result being a missing site(s) from the gantt chart / work program. is it possible to return all values to a cell i.e. site1, site2, site3. using a vlookup or do i need to use another method of doing this?

## Return Result Based On Criteria

Oct 27, 2006

if Cell F42 contains 50%, then my fomula returns "wrong"

=IF(AND(0%<F42,F42<50%),"F",IF(AND(50%<F42,F42<60%),"D",IF(AND(60%<F42,F42<70%),"C",IF(AND(70%<F42,F42<80%),"B",IF(AND(80%<F42,F42<100%),"A","wrong")))))

how do I get the formula calculate correctly?

## Result Based On Two Criteria Lookup

May 28, 2008

See the attached file. I think index and match may help me having done a search several times in these forums, but having been trying to do this for several hours I am just struggling to understand each part of the formula unfortunately. I have a list of data (cellsA1 to C33) which I need to use as my source for the information that is displayed in cell I2 based on the two selections made in cells F2 and G2.

I can't use filter and data sort in this situation - I am creating a spreadsheet for someone who truly knows nothing about excel, so I need this to be as simple as pointing and clicking at cells F2 and G2 and the formula doing all other work. Is it possible to do what I am asking for (hopefully the fact that peoples' names are repeated and months are repeated won't cause a problem?

## Return Ranking Based On Students Score?

Feb 28, 2012

How to return ranking based on students' score?

STUDENT SCORE Rank MAY 11255 4 JACK 5262 5 TIM 432525 1 STEVEN 352332 2 HUGO 32232 3 FERNANDO 5153 6 SANDY 4556 7 SARAH 2265 8

## Looking Up Values Using Multiple Criteria - Funky Result

Apr 6, 2014

Spreadsheet 'Raw Table' has many, many rows, and a dozen columns of raw input data. Each row has date, number, and text fields.

Spreadsheet 'Dashboard' is a somewhat simple one page summary of one day's worth of data entry from Raw Table. Dashboard isn't a simple table, but more form-like in appearance (not sure if that matters). Essentially, the values from Raw Table are spread out in different locations in Dashboard, and not just in a row.

The Dashboard fields gets their values from Raw Table based multiple criteria, the most important being a manually inputted date entry at the top of the Dashboard. The other criteria are simple numbers (ie, 1, 2, 3, 4....) built into the formula. So a set of criteria for one particular cell in the dashboard could be: 4/6/2014, 1, 3. Those 3 criteria are unique identifiers: there is only one match, ever. If those criteria are met, then a value from a specific spot in that row from Raw Data is placed in the dashboard field.

Here is one formula example for one such field in Dashboard:

=INDEX(('RAW DATA'!\$D\$1:\$D\$20000),SUMPRODUCT(('RAW DATA'!\$A\$1:\$A\$20000=\$D\$1)*('RAW DATA'!\$B\$1:\$B\$20000=1)*('RAW DATA'!\$C\$1:\$C\$20000=3)*ROW('RAW DATA'!\$D\$1:\$D\$20000)))

The first sumproduct criteria is a match for the date in Raw Data's column A with the manually inputted date in Dashboard located at D1.

The second criteria is match for the value in column B with a value of 1. The third criteria is match for the value in column C with a value of 3. (Again, this is an example. The 1,3 set can be any specific combo from 1,1 to 8,8. Thus, a day's data can have no more than 64 records of a dozen date, number and text fields.)

The Index function is obviously array,row,column. So this example index function results in the contents of column D in the row that the sumproduct function generates.

This dashboard spreadsheet works perfectly everywhere except four fields (out of 100+ instances of the same formula). In the faulty spots, the value that the index function is returning in Dashboard is basically the contents of column D in Raw Table in the row that corresponds to the place the formula is located in Dashboard. It's almost like there's an error in the formula result, and the index is defaulting to the row number in Dashboard. But I've tested this 8 ways til Sunday. I can even straight-up copy these four faulty fields to another part of the dashboard, and the results are magically correct. And I've checked to see that the actual sumproduct function result is returning the correct row for these fields. I don't have an answer as to why it's defaulting to the formula row number in just these four locations, and not the row value that the sumproduct function is correctly producing.

I've thought maybe it was a data type problem in Raw Data, but that route produced a dead end. It has to be something with the Dashboard. I'm going to start over with a new Dashboard spreadsheet, and see what happens. One thing I did was, early in development of the dashboard, use ctrl-shift-enter in the first faulty field, then went back and changed the formula wholesale to something different, and copied that cell to (at least I think) the three other faulty spots. But if I went back and completely changed the formulas and did away with the brackets, the cells should be fresh, no?

## Multiple Criteria Lookup Text Result?

Dec 10, 2013

So basically I am looking to build a formula that would take this information and return a text value.

if it could work anything like this sumproduct formula to bring back text that would be awesome:
=SUMPRODUCT(--(A1:A10="7654321"),--(B1:B10="ABCDE"),--(C1:C10="3"),D1:D10)

But instead of a formula that returns a sum of values, the return is a single value. The three criteria will always be unique. There will never be a case where there is a duplicate of all three.

## Lookup Multiple Criteria In Different Tabs To Return Result

Jun 5, 2008

I am trying to lookup two distinct values in two columns (turquoise and green) in 'Cust data' tab and correlate them to the same values in two columns on 'Driver activity' tab, then return a result from column in yellow on 'Driver Activity' tab to populate the driver name in yellow column on 'Cust data' tab.

File is attached.

## Matching Multiple Criteria On Single Rows And Returning Result?

Feb 5, 2013

I have two workbooks at the moment, one which holds a whole host of customer and accounts details and another which holds only the customer details. What I'm trying to do is cross reference the two to determine if a customers details appear on both and if they do highlight this, ultimately I'd like it to pull the customers account number back but even if it highlights a match that would be sufficient.

The only unique criteria I have to do this are customer surname and customer postcode.

In the Customer details spreadsheet I'm attempting to enter a formula which will look at the specific surname & postcode held on the row in which it is entered. It will then look at all of the rows on the other spreadsheet and determine whether any of them meet both criteria.

If they do I'd like it to display "match" if they don't I'd like it to display "no match"

## Sum Result Of Formula On A Range Of Cells That Meet Criteria

May 20, 2013

Is there a way I can sum the result of a formula on a range of cells that meet a criteria? For example, I need to sum the difference of only the cells that are >46. (a1-46)+(b1-46)+(c1-46)...+(g1-46).

In this case the result I'm looking for is on row 2:

Is this possible to calculate in 1 cell only (h1)?

A
B
C
D
E
F
G
H

1
44.2
48.6
47.5
0.0
42.3
44.6
49.5
??

2

2.6
1.5

3.5
7.6

## Fill Multiple Cells Based On The Result In One Cell?

May 31, 2014

When I enter barcode X in cell A1, I want B1 to say supplier and C1 to say product name and D1 to say package quantaty etc.

And if I enter barcode Y in cell A2, I want B2 to say different supplier, and so on, hopefully you get the picture.

## Return 1,2 Or 3 Based On Result Of Formula

Mar 3, 2007

Any other day but today (I've been starring at numbers too long) I could do this.

I need a simple formula that will return:

a. 3 if resut is >= \$2.5M
b. 2 if over >= \$0.5M
c. 1 if

## Check Multiple Cells, Fire Macro Based On Result

Jul 14, 2007

I have three cells where a user will input data, in some cases (2T Weld Condition) they will only enter in B12 and C12, but in the case of a 3T weld they will also enter data in the D12 cell. I then use a formula to check for the thinnest material and that is entered into another cell with a formula, B14. I then need to check the value in B14 to verify if it is above zero, but below 0.65 (mm). If it is then I would like to have a message appear on the screen notifying the user that they are outside the acceptable range.

I cannot figure out how to use the information in cell B14 because it is a formula and my code only works with a direct value. The code I am using works if I point to one of the three input cells, B12, C12 or D12. How do I use the information in B14 to work with the code below.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value < 0.65 Then
Run "MyMacro"
End If
End If
End Sub

MyMacro loads a userform with buttons, etc.

## Return 1 Result From Many Based On Multiple Condition Check Of Cell Value

May 21, 2008

I am trying to write a formula to figure out Body Mass Indexes for certain age groups and whether or not they fall into a High or Low risk category. So, I am trying to write a formula that does the following. I have 3 columns, Gender, Age and BMI. I need the formula to do the following.

IF Gender = M AND Age >18, <39 AND BMI >7%, <19%, Then return an "L" into 4th column
IF Gender = M AND Age >18, <39 AND BMI >19%, Then return an "H" into 4th column
IF Gender = M AND Age >40, <59 AND BMI >14%, <23%, Then return an "L" into 4th column..............................

## Formula To Calculate Result Based On Range

Dec 13, 2012

I have a scorecard that looks something like this:

90% or greater=3
80%-89%=2
70%-79%=1
0%-69%=0

I need the "cell" to react accordingly and I'm lost.

## Create Autoshape Based On Formula Result

Aug 1, 2006

how to have a cell equate to an autoshape based on the results of an "if" statement?

## Conditional Format Based On Formula Result Being A Whole Number

Dec 6, 2012

I'm looking to conditional format a cell/cells based on whether a formula result returns a whole number or not.

I don't want to include the formula in the sheet itself, just have that as the formula in the condition.

The formula will be along the lines of:

=IF(SUM(BB10/BA10)"a whole number",TRUE,FALSE)

My problem is, is that I don't know who to refer to "a whole number" in Excel formula language.

## How To Move Data From One Worksheet To Another Based On Formula Result

Jun 20, 2013

I have a formula sheet that uses an IF statement to determine if one columns data is bigger than another. Out of the 300 or so rows there are approx 20 that come back as yes (this is in column A) and the rest are blank

What I would like to do, is for the 20 or so rows, I would like to pull out (copy) columns B, J and L and put them into worksheet 2. Preferably without any gaps in the rows or columns.

I hope this is enough information, I am using MS excel version 2010 although I think the people who will ultimately be using it are on an earlier version.

## Formula That Returns A Result Based On All Dates In Column A

Feb 15, 2007

I need a formula to count cells based on the date, so that I can have a blank cell when the answer is 0. I am adding values cumulative and future cells need to be blank because I have a graph that has a trend line and I don't want the trend line to fall off at the end. I also don't want to have to go back to this every month and update it.

Column A
Date
1/2/2007 0:00
1/3/2007 0:00
1/10/2007 0:00
2/10/2007 0:00
2/10/2007 0:00
2/15/2007 0:00
3/22/2007 0:00
3/22/2007 0:00
3/22/2007 0:00
4/31/2007 0:00
4/31/2007 0:00
4/31/2007 0:00

Column B
Invoice #
CM-0003881
CM-0003882
CM-0003883
CM-0003888
CM-0003928
CM-0003932
CM-0003933
CM-0003985
CM-0004007
CM-0004008
CM-0004009
CM-0004065

I have this formula in cell F3 that will add the dates through the end of January:
=SUMPRODUCT((A1:A1000>=DATEVALUE("1/1/2007"))*(A1:A1000

## Run Macro Based On Result Of IF Function/Formula In Cell

Sep 14, 2006

how to run a macro from an IF function, if the function is true macro 1 runs if the function is false macro 2 runs.