I am trying to combine the LOOKUP and COUNTIF functions--because I cannot

nest more than 7 functions.

Here are the two functions I have:

=IF(1-COUNTIF(G2:M2,"P")>0,1-COUNTIF(G2:M2,"P"),0)

and

=LOOKUP(G2,{"P","S","B","A","E","T","B","BB","BBB" ,"TB","TBB",""},{3,3,3,3,3,2,2,1,0,1,0,3})

How can I combine these two functions to return a zero value if the first

function is zero, but return a 3 if the first function is greater than

zero????

I can e-mail my file if that helps find a solution?!?!?!?

Thanks!

Kate

nest more than 7 functions.

Here are the two functions I have:

=IF(1-COUNTIF(G2:M2,"P")>0,1-COUNTIF(G2:M2,"P"),0)

and

=LOOKUP(G2,{"P","S","B","A","E","T","B","BB","BBB" ,"TB","TBB",""},{3,3,3,3,3,2,2,1,0,1,0,3})

How can I combine these two functions to return a zero value if the first

function is zero, but return a 3 if the first function is greater than

zero????

I can e-mail my file if that helps find a solution?!?!?!?

Thanks!

Kate

- Combining LOOKUP and COUNTIF functions
- V-lookup, and or function
- Combine LOOKUP and CONCATENATE
- XL2000: SUMIF and COUNTIF Functions Return Incorrect Results When Used ...
- How can I combine text and a function in a single cell
- XL2000: SUMIF and COUNTIF Functions Return Incorrect Results When Used ...
- Nesting Lookup and If Functions
- How can I combine text and a function in a single cell
- Combining LOOKUP and IF functions
- Lookup and Countif combination
- LOOKUP and/or COUNTIF and/or SUM?
- Combining IF and LOOKUP?
- Combine lookup and Concatenate functions
- Combine vlookup and copy function
- AVERAGEIFS, SUMIFS and COUNTIFS Functions New functions in Excel 2007
- Combining Frequency and Countif Formulas
- Using proper date format for SUMIF and COUNTIF functions?
- Lookup and Offset
- Sumif function - countif function - microsoft excel
- Multiple Lookup and CountIF
- Countif function problem in a macro
- How do i combine IF and OR function
- Lookups and Non returned values
- Dynamic Range using Offset and CountIF

nest more than 7 functions.

Here are the two functions I have:

=IF(1-COUNTIF(G2:M2,"P")>0,1-COUNTIF(G2:M2,"P"),0)

and

=LOOKUP(G2,{"P","S","B","A","E","T","B","BB","BBB","TB","TBB",""},{3,3,3,3,3,2,2,1,0,1,0,3})

How can I combine these two functions to return a zero value if the first

function is zero, but return a 3 if the first function is greater than

zero????

I can e-mail my file if that helps find a solution?!?!?!?

Thanks!

Kate

I have the following

Spreadsheet 1

Column A--------------Column B

Product Code----------Price

ABC-------------------.50

CDE-------------------1.15

FGH-------------------.75

ECT-------------------.85

Spreadsheet 2

Column A--------------Column B

Product Code----------Price

ABC-------------------.50

ABC------------------1.00

ABC--------------------.75

CDE------------------1.15

CDE------------------1.30

CDE------------------2.00

FGH-------------------.75

FGH-------------------.75

ECT-------------------.85

Is there such a function, which would return the results in Spreadsheet 2, only if the price is less than 15% the price in spreadsheet 1 - using the product code with a combination of a V-LOOKUP and OR functions?

I want to use: =LOOKUP(Sheet1!A2,Sheet1!B:B,_______)

For the ______ I want to use: =CONCATENATE(Sheet1!C2,Sheet1!D2,Sheet1!E2)

However, I am not sure how to combine the two correctly

The SUMIF and COUNTIF functions return incorrect results when you use them to match strings longer than 255 characters to the string #VALUE!. This behavior occurs because SUMIF and ...

have the cell display "Error count" and after that, have the function run.

This is how I would like it to look:

Error count: 0

I'm unable to find the right combination to make it happen. Is this possible

to do?

The SUMIF and COUNTIF functions return incorrect results when you use them to match strings longer than 255 characters to the string #VALUE!.

Summery Sheet

31

32

33

34

etc..

I then want to populate the field next to it with a media type.

Summery Sheet

31 Paper

32 Vinyl

etc..

The order is fixed on the summery sheet, but in random order on the entry sheet. There is also some instances where multiple media types are used.

Entry Sheet

36 Paper

50 Vinyl

31 Mesh

40 Paper

31 Paper

etc..

Is there a way to populate the Summery sheet with

31 Mesh/Paper

or if multiple types are used have in the formula a specific result?

Any help would be greatly appriciated. Sorry for the long drawn out explanation.

have the cell display "Error count" and after that, have the function run.

This is how I would like it to look:

Error count: 0

I'm unable to find the right combination to make it happen. Is this possible

to do?

percentage and want to lookup the value in a corresponding table where this

value would fall if we sum across the row. For example, the first randomly

generated value is 13.5% and we are starting in state 6. Therefore, I want a

formula that looks in row 6 (starting state) and determines which column

(1-6) has the value 13.5% if you sum the values across (in this case the

answer is 5). Then 5 becomes the new starting state and so on. Is there a

formula or combination of formulas in Excel that can carry out this complex

function?

Thank you.

State transition matrix

1 2 3 4 5 6

1 85.00% 10.50% 3.10% 0.74% 0.66% 0.00%

2 8.90% 79.00% 9.20% 1.70% 1.20% 0.00%

3 1.00% 7.50% 73.00% 15.00% 2.50% 1.00%

4 0.00% 1.00% 13.00% 76.00% 9.00% 1.00%

5 0.00% 2.00% 5.00% 6.00% 71.00% 16.00%

6 0.00% 1.00% 1.00% 6.00% 9.75% 82.00%

Could anyone help?

in the attached workbook there are people's names (A B C D etc) in the first column - for each of those names there are two rows, one for the type of shift worked (using a drop-down list) and the second one for the hours assigned to that shift (a LOOKUP function that checks the table at the right).

At row 53, where all the question marks are, I would like to be able to look in the column above, see how many instances of "Cleaner" there are (Cleaner 1, Cleaner 2, etc) and then add all the hours only for those cleaners. I'm pretty sure I could use wildcards in there somewhere, such as Cleaners*** so that I don't have to do it 4 times (4 cleaners)

I've tried some variations/combinations using SUM and LOOKUP and COUNTIF but I'm afraid I'm scratching my head with this one.

Any help would be greatly appreciated and thanks in advance to those willing to take a look for me.

Cheers,

AJ

=LOOKUP(B3,Sheet2!I5:I6,Sheet2!J5:J6)

B3 is a dropdown list with product names that correspond to product numbers in Sheet2!J5:J6

it does what i want it to do, but I want to take this further and I am lost.

A3 will be a quantity and D3 will be a price. Depending on my quantity, I will have 4 different prices. I can do all the nested IF statements in the cell D3 as follows

=IF(A3>20,Sheet2!K5,IF(A3>10,Sheet2!L5,IF(A3>0,Sheet2!M5)))

Sheet2!K5 Sheet2!L5 and Sheet2!M5 are the three different prices I get depening on the quantity I type in A3.

How do I combine my LOOKUP and IF functions so that if I change my product in my drop down menu, I get the correct price

(sorry for the long post)

Samurai

contain list of names, each name is a member of multiple grps.

see Figure A

*****************Figure C ********

********Figure B******

***Figure A***

Grps: Mems: Mems1: Grps1:

RN-Ex Alba Alba RN-Ex

RN-Us pual Pual RN-Us

RN-Us Alex Alex RN-us

RN-BO Amy Amy RN-BO, RN-Ex

RN-Ex Amy John RN-Br, RN-us

RN-Br John Sara RN-BN, RN-us

RN-Us John Justin RN-us

RN-BN Sara Anselme RN-BO, RN-ex,RN-us

RN-Us Sara

RN-Us Justin

RN-BO Anselme

RN-Ex Anselme

RN-Us Anselme

Figure B, I added Mems1 column using Advance filter, copy to another

location and unique unique records only switches.

Figure C is where I would like to end up with, but don't know how to

properly nest/combine If, lookup, match and concatenate formulas.

Any idea?

Thanks -Mike

worksheet for others where information can be selected by drop down lists.

Whatever they select will then call up addtional information via a vlookup.

But, in case the information they are looking for is not already in the list,

they need to be able to type it in - so I cannot protect those cells and do

not want to lose the formula for future use.

If I could put the formula somewhere else and include a 'copy to' command,

it would solve the problem.

Can this be done?

The AVERAGEIFS function returns the average (arithmetic mean) of all cells that meet multiple criteria.

The SUMIFS function adds the cells in a range that meet multiple criteria.

The COUNTIFS function counts the number of cells within a range that meet multiple criteria.

The AVERAGEIFS function in cell A8 returns the result of 12.5

1.First Argument: The Averaging range, 12.5 is an average of 10+15 appearing in that range.

2.Second & Third Argument boxes: Criteria range 1 + Criteria1, 10, 15 & 20 meeting the criterion ">2" in List range.

3.Fourth & Five Argument boxes: Criteria range 2 + Criteria2, 10 & 15 meeting the criteria ">5" in Number range and ">2" in List range.

The SUMIFS function in cell A9 returns the result of 25

1.First Argument: The Sum range, 25 is the sum of 10+15 appearing in that range.

2.Second & Third Argument boxes: Criteria range 1 + Criteria1, 10, 15 & 20 meeting the criterion ">2" in List range.

3.Fourth & Five Argument boxes: Criteria range 2 + Criteria2, 10 & 15 meeting the criteria ">5" in Number range and ">2" in List range.

The COUNTIFS function in cell A10 returns the result of 2

First & Second Argument boxes:

Criteria range1 + Criteria1, 2 is the number of cells meeting two criteria in List range.

Third & Fourth Argument boxes:

Criteria range 2 + Criteria2, 10 and 15 meeting the criterion ">5" in Number range.

Here's what I'm trying to do: I have a worksheet that has a list of employee IDs and a list of months. I would like to count the number of unique ID's that occur within a certain month. My goal is to calculate the percent of ID occurances by month vs. the total monthly occurances. Each occurance equals and error, so I'm trying to find the average error percentage to set a baseline.

I've been able to get total unique IDs using the frequency, but not add a condition to count only unique ID's within a specified month. I've attached a sample with the data I'm using. Frequency Sample.xlsxAny help would be greatly appreciated!

Thanks,

Kelly

I need to sum and count the values of contracts I have listed in Sheet1 based on their expiration date (column D). On Sheet2, I want to do simple SUMIF and COUNTIF formulas to add the value (column C, Sheet1) and number of the contracts based on the corresponding month.

But I don't know how to reference a specific month in the proper format so the function recognizes it. See attached. Any suggestions??

RWL

I am trying to use offset & lookup, to return a cell x rows below the lookup value. I am able to get the correct result when I use the lookup and offset functions separately. When I nest the lookup function within the offset, I get an error. Greatly appreciatre any help.

Thanks,

Lisa.

the sumif function and countif function in microsoft excel is explained here and training on the sumif and countif functions are available here

I need a favour to figure out the logic behind this. For example my

table has 3fields,

Field 1 = CategoryID

Field 2 = Category

Field 3 = Item

The data is inserted into this table as raw data like below

6726 Stationary Pen

6726 Stationary Pen

6726 Stationary Pencil

5627 Utensils Spoon

5627 Utensils Knife

Another worksheet will summarize these details with fixed information

as below

but it will count the occurence of the 1st and 3rd field.

Pen Pencil Spoon Knife

6726 Stationary 2 1

5627 Utensils 1 1

I think it can be done using Vlookup and countif but am not sure how it

will be arranged.

Thanks for the help in advance

--

exxon99

------------------------------------------------------------------------

exxon99's Profile: http://www.excelforum.com/member.php...o&userid=34962

View this thread: http://www.excelforum.com/showthread...hreadid=570233

Sub B_Test()

Dim myRange

Dim Results

Dim Run As Long

myRange = Workbooks(1).Worksheets("Master").Range("S6", Range("S6").End(xlDown))

Range("M3") = Application.WorksheetFunction.Sum(myRange)

myRange = Workbooks(1).Worksheets("Master").Range("D6", Range("D6").End(xlDown))

Range("D3") = Application.WorksheetFunction.CountA(myRange)

'myRange = Workbooks(1).Worksheets("Master").Range("P6", Range("P6").End(xlDown))

'Range("E3") = Application.WorksheetFunction.CountIf(myRange, "">0"")

End Sub I have tried countless ways to rewrite the COUNTIF line with no results or compiler errors returned. Originally had problems with the SUM and COUNTIF function and found that column formating was the problem. After clearing all column formats, the SUM function promptly began working but the COUNTIF keeps eluding all my efforts. The column which the COUNTIF is pointed to contains values of 0 to 500. Only values greater than 0 are to be counted.

I would appreciate if someone could trouble shoot this part of my code.

Thanks in advance.

=IF(VLOOKUP(C4;Appartment!$C$1:$C$63;1;FALSE)=C4;"Double";"")

I would like to and an OR in there that it would lookup and other columie:

(B4;Appartment!$B$1:$B$63;1;FALSE)=B4;"Double";"").

is it possible?

I have a validated list cell and want to return the description of this code, (it's alpha numeric).

The problem I have is the list will not be sorted into any order.

Can anyone help with the problem?

The validation list is order alphanumerical on a different worksheet within the same file.

I've tried Vlookup, lookup and match functions but with no luck.

I would like to use a dynamic range and think I need to use the Offset and CountIf functions.

Both the start and end of the ranges are dynamic.

The range is based on grouping the numbers in Column E (LocNo)

What I would like to do or think the way to achieve this is by:

First looking at Column E

Count how many cells are equal to criteria. (CountIf)

offset 5 columns left from the first cell that meets the criteria (Offset -5)

offset X rows down based on how many cells meet the criteria (Offset X)

I've include a spreadsheet spread sheet which 'should' help illustrate the problem showing the ranges I would like and how the data changes each week.

any help would be much appreciated!