IF function multiple criteria

hi guys,

statement basically needs to say if cell a1 = "certain phrase" AND B1 is populated then return value in B1.

can't seem to get my IFs and ANDs right :/

can anyone help please?


Hai All
This Is My First Mail On This Fourm,
Iam Looking For Sum If With Multiple Criteria
Sumif(a2:a22=d2 And B2:b22=e2 Sum Should Be Sorted Result Of Array C2 C22)
How Do I Put The Formula
Raj

Excel: SumIf with Multiple Criteria. Excel Formulas & Functions

Excel: Sum If with Multiple Criteria. How to Sum cells that meet multiple criteria. Excel Formulas & Functions

HELP!
I need an array that will COUNT the number of occurrences of multiple
criteria across two columns.

Example:
So, in the simplified example below, it would only COUNT occurrences
that are either ['Red' OR 'Amber'] AND '[Ripe', 'Ready' or 'Cooked'].
So far, I've only been able to come up counting it of it's Red or
Amber, but don't know how to incorporate the OR function for the other
criteria set.

The code so far: *=SUM(IF((H10:H215="Amber")+(H10:H215="Red"),1,0)) *
HOW CAN I GET IT TO ALSO CONSIDER THE SECOND SET OF FILTERING
CRITERIA??

_FRUIT_ _COLOUR_
Ripe Red
Inedible Red
Ripe Amber
Rotten Amber
Picked Amber
Ready Red
Cooked Green
Cooked Red
Rotten Yellow

--
AustraliaPosted via http://www.officehelp.in - <a href="http://www.officehelp.in">Microsoft Office Software</a>

HELP!
I need an array that will COUNT the number of occurrences of multiple
criteria across two columns.

Example:
So, in the simplified example below, it would only COUNT occurrences
that are either ['Red' OR 'Amber'] AND '[Ripe', 'Ready' or 'Cooked'].
So far, I've only been able to come up counting it of it's Red or
Amber, but don't know how to incorporate the OR function for the other
criteria set.

The code so far: *=SUM(IF((H10:H215="Amber")+(H10:H215="Red"),1,0))*
HOW CAN I GET IT TO ALSO CONSIDER THE SECOND SET OF FILTERING
CRITERIA??

_FRUIT_ _COLOUR_
Ripe Red
Inedible Red
Ripe Amber
Rotten Amber
Picked Amber
Ready Red
Cooked Green
Cooked Red
Rotten Yellow

--
AustraliaPosted via http://www.officehelp.in - &lt;a href=&quot;http://www.officehelp.in&quot;&gt;Microsoft Office Software&lt;/a&gt;

I have a file which contains list of customers whose outstanding balances are aged in a column and the pending values. Now the task that I am given is to separate those ageing in various buckets of <= 30 days, > 30 <=60 days etc.

I believe nested/ multiple IF function can be used , but am not able to recall that. Help is highly appreciated. Kindly refer the test file.

Is it possible to write the IF FUNCTION with more than 1 arguement e.g.

If A1=zzz and A2=yyy then enter True/False in B2

Or:

If A1=zzz and A2=yyy and A3=xxx then enter True/False in B3

Many thanks

Andy

Hi,

Once again I turn to the genius's of the world!

I understand VLOOKUP.
I understand the IF Function.
I have attached the Sheet I am working on.

In short, when the user changes the Month and/or Date then the data displayed under "Today" must be for that particluar Month & Date which is found in the "data base" futher down in the spreadsheet.

In long...

1. The user can change the Year, Month and Date. (C1:C3) This will be the condition.Attachment 60461
2. When the Month and Date is changed, cell B5 (and B6,B7,B8 ect) must then display the relevant information found in cells B52:AO60 (this is for April only).
3. If the user changes the Month and/or Date then the information displayed under Today (B5:B13) must be retrieved from the same month and Date (found further down in the spreadsheet.)
4. The user can enter data under each day under each month (found further down in the spreadsheet.)

I have entered test data in cells C52:I55.

You help will be of great value and highly appreciated.

In advance, Thank you!

*** Please don't laught at the formula I have in B5. I was just trying to figure out how it might work.

Hi,

I am trying to find the corresponding column value based on the result of an minumum/maximum function with multiple criteria. I have found that i am able to find the minimum/maximum value basd on certain criteria, and i am also able to find the corresponding cell of the result of this calculation BUT the issue is i am unable to return the exact row which corresponds to the result of the initial minimum/maximum calculation.

I have added a much simpler version of what i am trying to do in the table below. The real version contains many more criteria:

A B C D 1 Name Number Category 1 Category 2 2 jeff 540 red yes 3 peter 470 green yes 4 steve 360 red no 5 andrew 490 red no 6 michael 520 green yes 7 greg 360 red yes 8 robert 480 red no
From what i can ascertain I am trying to perform two functions, both using an array (as far as I can tell):

1. Return the minimum Number (Column B) based on criteria:
MIN(IF((C2:C8="red")*(D2:D8="yes),B2:B8)) CTRL+SHIFT+ENTER (this would then return the number 460 from row 7)

PROBLEM:
2. Then, i want to fin the corresponding name for the calculation above. i have tried the following only to find that whilst this find the correct number (460 in this instance) it finds the first one and will then return "steve" when in fact i am looking for it to return "greg":
INDEX(A2:A8,MATCH(MIN(IF((C2:C8="red")*(D2:D8="yes),B2:B8)),B2:B8,0))

Any help would be greatly appreciated. I've spent a dozen hours reading threads without luck - i am completely stumped!

thank you.

I need a formula that looks to see that a value is greater than one cell and
less than or equal to another. If the results are valid, I want it to say
"yes". If not valid, then result as blank. I have an IF formula that satifies
one value but not for two. It looks like this:

if(A1=B1,C1,"")

How do I make it work to satisfy multiple criteria? Thanks
--
JerryS

I am trying to sum the amounts in column A based on the figure in Column B.
I want the formula to sum Column A if the value in Column B is between 99 and
200. For some reason, I am having trouble with the multiple criteria part of
this. Is there a way to do this, another function, or anything?

I have the need to count instances based on multiple criteria (3, to be
exact). I already have successfully done a formula based on 2 criteria, but I
am not getting the syntax right, or maybe just not using the right formula at
all to get the 3rd criteria selected out. The table of info I am culling
information is set up as shown below:

Part Part# Status Ship To Ship Date Exchange Invoice $$
Widgets 075 Shipped ABC Co 5/15/05 N/A 20
Bobbins 093 On Hold Acme Inc 3/20/05 Widget 10
Gadgets 024 Shipped Smith Co. 8/10/04 N/A 25

The spreadsheet containing the successful results thus far is set up thusly:

Part PN Shipped Exch Sold In House
Widgets 075 1 0 0 (Here's where the
problem lies)

For the shipped, exchanged, and sold, I got the results using an
aggregate function, as follows:
{Count(IF((a2:a1500="widgets")*(c2:c1500="Shipped" ),g2:g1500))}

For In House, however, I need to count instances where the part =
"Widgets", Status "Shipped", AND Ship To "Acme Inc."

Any help/advice would be much appreciated.
--
Best Regards,

Beth

Hi guys,

I have a problem that to most of you will probably sound ridiculously simple but I am an excel newbie so I still couldn't find a way to do it. Here it is:

I have a column of values. I would like each entry to display a multiple of the value it contains, subject to conditions. In particular, I'll take a single entry as an example:

If it contains a value <1, I would like it to display 0;

If it contains a value between 1 and 2, I would like it to display 2*the value;

If it contains a value >2, I would like it to contain 3* the value.

I found out how to do this subject to a single condition, by using a simple IF function. How can I do this with multiple conditions?

Thanks a lot, any advice is very welcome

I have been playing with IF functions and AND fuctions but can't get this right. This is a spreadsheet to tract bills and loans with my roommate. Yes I know there are free internet services, but none allow the bill to be unpaid.

A, B, C, D, E, F G H
Catagory/Dropdown Amount Equal or Unequal Party,Dropdown, Neil% Chris% N C

Column A: has dropdown feature allowing you to choose "expense", "Loan" "Bill" or "Payment"

Column B; Is amount of transaction

Column C: Is whether the transaction is applied equally to the parties or unequally.

Column D; This is a dropdown to choose the party, "Chris", "Neil", "both'.

Column E; Percentage owed/paid by Neil

Column F: Percentage owed/paid by Neil

Column G; Neil's running balance

Column H: Chris Running balance

Formulas and functions. Here is as far as I could get. =IF(A1="expense",b1*50%,b1*E1) I put this in Columns/row G1 and H1. But I need it to be dependent on multiple criteria.

Solve for G1 or H1: If A1="expense" then based on D1 choose either G1 or H1 to display the following result. B1*50% if C1 is "Equal" or B1*E1 for Neil and B1*F1 for Chris

Solve for G1 or H1: If A1="Loan" then based on D1 choose either G1 or H1 to display the following result. B1*E1 or F1% if C1 is "Equal" or B1*E1 for Chris and B1*F1 for Neil.

Solve for E1 or F1: If A1="Payment" then based on D1 choose either G1 or H1 to display the following result. B1*E1 or F1% if C1 is "Equal" or B1*E1 for Chris and B1*F1 for Neil.
You can also figure D1 in this instead of having two percentage columns.

Solve for G1 or H1: If A1="Bill" then based on D1 choose either G1 or H1 to display the following result. B1*E1 or F1% if C1 is "Equal" or B1*E1 for Chris and B1*F1 for Neil. I think bill and expense do the same thing, disregard bill.

I have 3 columns:

A. First Name
B. Last Name
C. Email Address

then I have 2 more columns

D. First Name
E. Last Name

I want to use the criteria in D and E to compare to all of A and B to pull the result from C.

I tried =IF($A$2:$A$100=$D2,IF($B$2:$B$100=$E2,$C$2:$C$100,),0) with no success.

Should I be using a lookup function? The multiple criteria is throwing me off

thanks for any help

Alrite ppl I hope I am able to explain the problem I am facing here.. I
am working with multiple worksheets... what I want is to display
certain data in one worksheet by looking up the data from another
worksheet based on certain criteria ... let me explain - consider this
data to be present in a worksheet called 'PO Data' -

p/o no part no qty due date week no
1123 1195c 500 7/18/2006 2
1198h 1000 7/21/2006 2
1590dc 750 7/26/2006 3
1100-p 80961 200 7/4/2006 1
80961 500 7/20/2006 3
80961 30 7/21/2006 3

Now I have another worksheet say 'Plan' which SHOULD contain the
following data ..

part no qty due date week 1 week 2 week 3 week 4 week 5
1195c 500 7/18/2006 500
1198h 1000 7/21/2006 1000
1590dc 750 7/26/2006 750
80961 1200 7/4/2006 1200
80961 1500 7/20/2006 1500
80961 30 7/21/2006 30

So u see I want the data in the PLAN worksheet be displayed in the
respective columns when I enter the part no. I tried using VLOOKUP with
IF function but could not come up with a solution. I have used this
formula to calculate the week no =IF(WEEKDAY(A1 ,2)>5, "", INT((DAY(A1)
+ WEEKDAY(A1-DAY(A1) + 2) + 3)/7)) ... I also tried to use the same in
the 'Plan' worksheet but could not come up with anything... can
someone please help me with this?

adi

I have the need to count instances based on multiple criteria (3, to be
exact). I already have successfully done a formula based on 2 criteria, but I
am not getting the syntax right, or maybe just not using the right formula at
all to get the 3rd criteria selected out. The table of info I am culling
information is set up as shown below:

Part Part# Status Ship To Ship Date Exchange Invoice $$
Widgets 075 Shipped ABC Co 5/15/05 N/A 20
Bobbins 093 On Hold Acme Inc 3/20/05 Widget 10
Gadgets 024 Shipped Smith Co. 8/10/04 N/A 25

The spreadsheet containing the successful results thus far is set up thusly:

Part PN Shipped Exch Sold In House
Widgets 075 1 0 0 (Here's where the
problem lies)

For the shipped, exchanged, and sold, I got the results using an
aggregate function, as follows:
{Count(IF((a2:a1500="widgets")*(c2:c1500="Shipped"),g2:g1500))}

For In House, however, I need to count instances where the part =
"Widgets", Status <> "Shipped", AND Ship To <>"Acme Inc."

Any help/advice would be much appreciated.
--
Best Regards,

Beth

I'm aware of the SUMIF function that sums up a range based on one criteria. But I'm wondering if there's a function that does the same thing but allows multiple criteria in a SINGLE column.

The SUMIFS function only applies if you have multiple criteria spread across multiple columns *not* in the same single column.

So, any ideas on this? Thanks in advance.

Hi everyone,

So my current problem is that I want to use the IF function to perform the logical test on a column of cells instead of just one cell.

I realize that this can be done by copying the IF function to multiple cells but the way my spreadsheet is currently set up doesn't allow me to do that.

To be more specific, I am trying to use the below function:
What I am trying to do is to go through a column of dates, extract the month and compare each month with a specific month. If
the date matches up, I want to sum up data in an adjacent field based on another "equal to" criteria.

I know the SUMIF portion is working fine but I am at a lost as to how to perform an IF logical test on a column of cells (compare a column of cells one by one to another specific cell) all in one cell (without having to use multiple cells and summing things up).

Maybe this will be an interesting riddle for you guys =)

Any help or advice is much appreciated!

I forgot to mention that Excel doesn't let me use the IF function on a column of cells, haha. Well, it does but it isn't working as I expect it to as described above.

I am attempting to find the rank of a value against a list of values using multiple criteria

={RANK(SUMPRODUCT(--('Weekly Measures.xls'!dataMeasure=$B$7),--('Weekly Measures.xls'!datadepot=$B$4),--('Weekly Measures.xls'!dataweek=$B9),--('Weekly Measures.xls'!datayear=A9),--('Weekly Measures.xls'!datavalue)),IF(('Weekly Measures.xls'!dataMeasure=$B$7)*('Weekly Measures.xls'!dataweek=$B9)*('Weekly Measures.xls'!datayear=A9),('Weekly Measures.xls'!datavalue)),1)}

----
This part works no problem, I've used loads of times:
=SUMPRODUCT(--('Weekly Measures.xls'!dataMeasure=$B$7),--('Weekly Measures.xls'!datadepot=$B$4),--('Weekly Measures.xls'!dataweek=$B9),--('Weekly Measures.xls'!datayear=A9),--('Weekly Measures.xls'!datavalue))

-----

The problem comes when I try to list the result of the above against the entire population, using RANK function, results in #Value! error

If have attempted to use IF function to yet the array to compare against

IF(('Weekly Measures.xls'!dataMeasure=$B$7)*('Weekly Measures.xls'!dataweek=$B9)*('Weekly Measures.xls'!datayear=A9),('Weekly Measures.xls'!datavalue)

Any suggestions....Thanks in advance

Many Thanks
Simon

Hi,

I am trying to filter data on a worksheet by a userform. But i need more then one criteria to filter this sheet. Here is the code for one criteria, how can I upgrade this code for multiple criteria? Thanks for helps...

Userform Code:

Option Explicit

Private Sub CommandButton1_Click()
Dim rng As Range

'Set Error Handling
On Error GoTo ws_exit:
Application.EnableEvents = False
'Set Range
Set rng = ActiveSheet.UsedRange
'Cancel if no value entered in textbox
If TextBox1.Value = "" Then GoTo ws_exit:
'Call function Filterandcopy
FilterAndCopy rng, TextBox1.Value
rng.AutoFilter
'Exit sub
ws_exit:
Set rng = Nothing
Application.EnableEvents = True
Unload Me
End Sub

Private Sub CommandButton2_Click()
'Cancel Button
Unload Me
End Sub

Module Code(Macro):

Option Explicit
Function FilterAndCopy(rng As Range, Choice As String)

Dim FiltRng As Range
'Clear Contents to show just new search data
Worksheets("ödemekontrol").Cells.ClearContents
'Set the column to filter (In This Case 1 or A)
'Change as required
rng.AutoFilter Field:=2, Criteria1:=Choice
On Error Resume Next
Set FiltRng = rng.SpecialCells(xlCellTypeVisible).EntireRow
On Error GoTo 0

'Copy Data across to sheet 2
FiltRng.Copy Worksheets("ödemekontrol").Range("A1")
'Display Data
Worksheets("ödemekontrol").Select
Range("A1").Select
Set FiltRng = Nothing
End Function

Sub formshow()
'Show Search Form
Sheets("ödeme").Select
UserForm5.Show
End Sub

So how can I change this code for multiple criteria...?

I am trying to retieve a value using multiple criteria, the vlookup function is not appropriate as I have repeat values in the same column. I am finding it diffcult to explain what I need in words so i will use an example

With reference to the attached table how can I return the Link if I know the 'Road No' and the 'SLK' ie. if I have H005 as the road and 560 as the SLK how can I get this to return Link 15

For extra clarity 'SLK' is the distance along the road and each Link runs from 'SLK Start' to 'SLK End'

I will appriciate any help I can get. Thanks

~Hello. I'm adding a dashboard page to a complex tracking worksheet (macro-enabled, shared and protected Excel 2007). Where I'm stuck is trying to autopopulate a row showing a count of the number of dates in a ReviewDate column that fall between certain dates only if multiple criteria are met. I've attached a sample spreadsheet and mocked up someting below.

Sample excel file
Dash.xlsm

The results will be on a dashboard page needs to have nicer (brand standard) formatting for senior leaders, which makes Pivot Tables difficult to use. The spreadsheet is not that large so calc times are not a concern (yet). I've tried COUNTIF, COUNTIFS, SUMPRODUCT and everything else I could think of and have struck out. I think my main problem is the stringing the complex syntax together properly.

Your expertise and suggestions are appreciated! If I can solve for #1 below, I can figure out the rest I think. Thank you!

Example of source data

ReviewDate Status Approach LOB 7/23/2011 Not Started ILT A Finance 7/25/2011 In Progress ILT B Finance 7/26/2011 Develop WBT A Tech 7/23/2011 Not Started WBT B Finance 7/25/2011 Complete WBT B Finance 7/26/2011 Complete ILT B Finance 7/29/2011 Develop ILT B HR 7/31/2011 In Progress ILT A Finance 8/2/2011 Not Started WBT A Finance

What I'm trying to figure out is:ILTs Due = COUNT of Approach "ILT A" OR "ILT B" where LOB = "Finance" AND Status = "In Progress" OR "Not Started" AND ReviewDate is between July 11-18, 18-24, 25-31, etc.ILTs Complete = ILT A or ILT B = "Complete" and LOB = "Finance" (just a simple running total)

Example of dashboard table
[Finance]
Week of July 11 July 18 July 25 August 1 August 8 ILTs Due 2 2 1 ILTs Complete 1 WBTs Due 1 WBTs Complete 1

I need to sum cells considering multiple criteria, so I decided to use the SUMIFS.
The main problem is that the cells I need to sum are obtained by trimming other text cells, so even if they contain only numbers, they are not read as numbers by the SUMIFS function.

Is there any way to convert them easily in numbers befor the SUMIFS, or to sum them in other ways?

Many thanks

Excel COUNTIF Function for Multiple Criteria

Excel COUNTIF function for two conditions (num1 < x < num2). Download a spreadsheet showing how to count data points between two numbers.