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?

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?

- Sum If With Multiple Criteria
- Excel: SumIf with Multiple Criteria. Excel Formulas & Functions
- COUNTIF or SUM function (Multiple criteria) HELP!!
- COUNTIF or SUM function (Multiple criteria) HELP!!
- Using IF with multiple criteria
- If Function - Multiple Arguements?
- VLookup with If Function & multiple conditions
- Return value of adjacent column based on min/max if function with multiple criteria
- Satisfying Multiple Criteria in an "IF" Function
- SumIf Function using multiple criteria
- Multiple Criteria IF Nesting
- IF function with multiple criteria??
- Problem with IF functions in multiple string.
- Using multiple if functions for columns of data...help
- Use of vlookup and IF functions on certain criteria
- Multiple Criteria IF Nesting
- Is there a function that uses multiple criteria in a single column?
- IF function performing logical test on multiple cells
- RANK IF with Multiple creteria
- VBA Userform autofilter with multiple criteria
- Lookup a value using multiple criteria including greater than or less than checks
- Excel 2007 counting dates in date range only if multiple criteria are met
- Sum numeric text cells based on multiple criteria
- Excel COUNTIF Function for Multiple Criteria

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: Sum If with Multiple Criteria. How to Sum cells that meet multiple criteria. Excel Formulas & Functions

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>

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>

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.

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

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.

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.

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

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

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.

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

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

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

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.

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.

={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

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...?

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

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

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 two conditions (num1 < x < num2). Download a spreadsheet showing how to count data points between two numbers.