Free Microsoft Excel 2013 Quick Reference

Combining LOOKUP and COUNTIF functions

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


Post your answer or comment

comments powered by Disqus
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

Hello All:

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?

What would the proper syntax be to combine the LOOKUP and CONCATENATE 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

XL2000: SUMIF and COUNTIF Functions Return Incorrect Results When Used ...

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

I would like to combine text and a function into a single cell. For example,
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?

XL2000: SUMIF and COUNTIF Functions Return Incorrect Results When Used ...

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

I'm not sure if this is possible, but I would like to nest lookup and If functions. If this isn't possible perhaps someone could tell me another way to do what I need. What I am trying to do is create a workbook for a report. I want the first page to be the actual sumerized report. This sheet is locked to prevent users from changing anything. Then I have on another sheet in the book a sheet that the user enters all there data in. The problem I'm having is the summery has info arranged in numerical order.

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.

I would like to combine text and a function into a single cell. For example,
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?

I'm trying to use a state transition matrix in Excel. I randomly generate a
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%

Hi. I'm trying to use a combination of Hlookup and COUNTIF. I'm selecting a date value in a cell using data validation. I'm then wanting to write a formula to lookup that value in a row of dates, and then use a countif to find all the '1' values in that column.
Could anyone help?

Hi all,

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

I have the following function contained in C3:

=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

I have a worksheet with two columns, Grps and Mems. Mems column
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

Is is possible to combine a copy and vlookup function? I've created a
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.

Is it possible to combine the Frequency and Countif formulas?

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

Hi,

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

Hello,

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.

Sumif function - countif function - microsoft excel

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

Hi Excel Experts,

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

I'm using the SUM, COUNTA and COUNTIF functions in a macro. The SUM and COUNTA works but the COUNTIF function does not return results.

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.

Hello all I'm working on a spreadsheet with the fol formula:
=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?

Hey Guys,

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 have a data set that alters each week which means at the moment I have to update the ranges manually each time.

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!


No luck finding an answer? You could always try Google.