Vlookup With Nested IF's Or CountIF's

Oct 8, 2009

Hi guys, hopefully you can shed some light onto this small problem I am having.

The current formula I'm trying to use is:

=IF(VLOOKUP(A4,EPG1!D4:AN26,6,FALSE),COUNTIF(PASS,""))+IF(VLOOKUP(A4,EPG1!D4:AN26,6,FALSE),COUNTIF(F AIL,""))

This isn't producing the desired results.

What I am trying to do in words is this:

IF... in this array it equals A4 then look at coloum 6 and then Countif col 6 says Pass and then add another if it says Fail....

View 7 Replies


ADVERTISEMENT

Nested Countif?

Mar 16, 2007

I searched on this and didn't find what I was looking for. I want to count entries that have critieria I specify in two different ranges. Is countif the way to do this?

View 9 Replies View Related

Countif Nested Conditions

Aug 28, 2007

I just need it to be able to test two seperate conditions on two seperate columns but i cant work out how its done

for example, i have two columns on a sheet, one called type and one area, both are text, i need to count how many items of one type are in a particular area. so if i have two columns "area" and "type" i want a total count of how many rows contain area="north" and type="b"

View 13 Replies View Related

More Than 7 Nested IF's And Countif Formulas

Apr 13, 2007

I'm working on a formula that will determine the lowest number in a range.

The complication is that the range varies based on how many rows match a certain criteria.

The formula I have created works correctly, however, I need to have the range go up to 10, and because of the limit of 7 nested IF's, I have to stop at 6.

=IF(A2=A1,"",IF(COUNTIF($A:$A,A2)=1,G2,IF(COUNTIF($A:$A,A2)=2,MIN(G2:G3),IF(COUNTIF($A:$A,A2)=3,MIN(G2:G4),IF(COUNTIF($A:$A,A2)=4,MI N(G2:G5),IF(COUNTIF($A:$A,A2)=5,MIN(G2:G6),IF(COUNTIF($A:$A,A2)=6,MIN(G2:G7),"")))))))

After a result is returned from this formula, I have to do the almost exact same formula, based on the min value from these results.

View 9 Replies View Related

Nested SUM & COUNTIF Formula With #DIV/0! Error

Mar 16, 2008

I have a student grade worksheet with this formula and it returns # DIV/0 which I need to solve the problem. I have tried many different ways and none seem to work. Con someone assist with this problem. Here is the formula:

= SUM(R13,R55,R97,R139,R181)/(COUNTIF(R13,">0")+(COUNTIF(R55,">0")+(COUNTIF(R97,">0")+(COUNTIF(R139,">0")+(COUNTIF(R181,">0"))))))

View 9 Replies View Related

Consolidating Lists And Nested SumIf(AND / Countif(AND Statements

Jun 10, 2014

I am trying to produce a report of supplier transactions sorted by area code. I have a spreadsheet of data consisting of Supplier Code, Supplier Name, Transaction Amount and Area Code.

I want to be able to firstly seperate the transactions by area, then also consolidate the data so it shows one row per supplier with total amount spent on that supplier and a transaction count on that supplier. I know how to sumif the transaction total and countif the transaction count.

However I have problems consolidating the suppliers in to one row per unique supplier and I also have problems nesting an AND statement in to the sumif/countif statements. I need additional criteria in the forumula to only count transactions in a specified area code.

I've attached an example spreadsheet to make it alot clearer. See results sheet in this workbook.

View 3 Replies View Related

Nested Countif Not Working: Track Metrics For A QC Department

Nov 26, 2008

I am trying to track metrics for a QC dept. Right now my sheet has 35 lines of data and a header row, for 36 lines total. This is my funtion that isn't quite right:

View 2 Replies View Related

Nested Formula (Sumif / Countif) - Sorting Table

Mar 6, 2013

It's actually a sumif/countif that works fine if i sort my table, which i don't want to do.

Here's the original formula:

Code:
(SUMIF(H4:H35,"1",G4:G35)/COUNTIF(H4:H35,"1")*1+SUMIF(H4:H35,"2",G4:G35)
/COUNTIF(H4:H35,"2")*2+SUMIF(H4:H35,"3",G4:G35)
/COUNTIF(H4:H35,"3")*3+SUMIF(H4:H35,"4",G4:G35)
/COUNTIF(H4:H35,"4")*4)/10

I'm trying to apply the formula using some other filters, like if(C4:C35=O5 & I4:I35=M5).

I tried creating a SUMPRODUCT out of it but it was just as bad:

Code:
=(SUMPRODUCT((C4:C35=$O$5)*(I4:I35=$M$5)*(H4:H35=1)*(G4:G35))
/SUMPRODUCT((C4:C35=$O$5)*(H4:H35=1)*(I4:I35=$M$5)*1)*1+...

And also this with sum(if)

Code:
=(SUM(IF(C4:C35=$O$5,IF(I4:I35=$M$5,IF(H4:H35=1,G4:G35))))
/COUNT(IF(C4:C35=$O$5,IF(I4:I35=$M$5,IF(H4:H35=1,G4:G35)))))*1+...

Is there a shorter formula that I'm not seeing ? I'm also confronted with the need of adding an "ISERROR" in the formula because not all ranges have the 1-4 numbers in column H.

View 9 Replies View Related

(FORMULA) Return Largest Time With Nested Countif And Text

Dec 4, 2013

I'm logging failed password attempts and need to find the latest time logged for a single user (when user has failed 5 times).

So the 5th attempt, I need that time

So the usual - Application.UserName in the next free cell in a range and time stamp in the next cell.

What I've tried is:

Say log range is A1:B500 and
A=Username, B=Time stamp

In column C I've nested the two together
(this has more code but not required to get this working)

In C1
Code:
=A1&" "&"live"&" "&B1

So this will bring back "Username live 23:12:12". Now, say there is 5 of these (all with different times)

How do I find the latest time for that user?

Code:
IF(COUNTIF("USERNAME LIVE"&"*")=5,[find the latest time in that list",""

Just to wind you up some more, other users name will be logged and muddled up so it's literally -

"Excel - if a user has failed 5 times then give me the time of the 5th attempt"

I've tried a mixture of COUNTIF, MAX, LARGE, TIMEVALUE(...)

View 4 Replies View Related

Vlookup Across Sheets, Nested Vlookup Possibly?

Jun 9, 2009

I’m trying to develop a workbook which holds monthly data on loan information. It tracks the interest and balance on the loan. I want the first page to have a table displaying the interest payments for every individual tab. When I was brainstorming the idea, I was considering a sort of Vlookup function to find the tab the account is on and then a further function, possibly another vlookup which connects the month to that month’s interest payment. Can anyone help me figure this out?

The attached spreadsheet is obviously simplified, there are well over 30 tabs. But I would like it to, ideally, search the account number column, search the workbook for that account number, and then when on that page use the month at the top of the first page and retrieve the interest payment and put it back in the cell. It’d also be great if the formula can be transferred between workbooks. I’m not sure if that makes sense; basically if I were to copy that worksheet into the next months book, I would like that the formula read those tabs instead of becoming obsolete due to references from the first workbook.

View 14 Replies View Related

VLOOKUP With Nested IF

Mar 20, 2014

I am trying to make this work. VLOOKUP (B2,"lookup tables'!$A$4:$A$11, if(D2'="ST" 'lookup tables!$C$4:$E$11.

I am attaching the file : MDCC Reservations.xlsx

I am trying to get the Daily room charge.

View 5 Replies View Related

Nested IF Or VLOOKUP

Nov 7, 2007

Level RatingA RatingB RatingC Low Medium High
2 x x x x x x
3 x x x x x x
4 x x x x x x
5 x x x x x x

I need to be able to do nested loops. I want to have a criteria that if a person is in LEVEL 2 and RATINGC and HIGH, then take the value under that.

View 9 Replies View Related

Nested IF/OR With VLOOKUP

Sep 24, 2009

I'm trying to build this formula and I'm only half way through it and it already isn't working.
Column D - can be 1, 2, 3, or 4
Column E - can be any number

If column D is 1 or 3, then the VLOOKUP should look up column E and point to the answer in column 4 on another worksheet (Companies in this case). If column D is 2 or 5, then the VLOOKUP should look up column E and point to the answer in column 5 on another worksheet (Companies in this case). This is what I have so far, just trying to get it to recognize if it is a 1 or 2:

=IF(OR(D2=1,VLOOKUP(E2,Companies!A:G,4)),IF(D2=2,VLOOKUP(E2,Companies!A:G,5)))

What is making this not work? I'm getting a false. But before I added the OR, it worked for the D2=1.

View 3 Replies View Related

Need A Nested IF Formula Or Vlookup

Feb 20, 2009

Objective: To find out which customers order certain items and which customers dont order certain items. Many customers may order the same item eg customer A, B, C, D all order item "4567"

I have 2 worksheets.

Worksheet 1: Showing 30 item codes, item description and customers. Items in col A (A2:A31), description in col B (B2:B31) runnning down vertically. Customer name in cell 1 of all other columns running across horizontally, eg C1, D1, E1.... (C1:GF1). There are 186 customers. (A formula needs to start at C3 and dragged to GF3)

Worksheet 2: Raw data showing customers in column A and items in column B, There are 3,753 rows. Customer in column A are duplicated as the same customer may order a number of items so for eg

ABC PTY LTD 5671
ABC PTY LTD 5683
ABC PTY LTD 5692
ABC PTY LTD 5610
ABC PTY LTD 5611

Tried the below formula
=if(C1='S2'!A2),=if('S1'!A2='S2'!B2),"ordered","")

View 8 Replies View Related

Nested Vlookup (look The Name Up From 3 Other Worksheets)

Oct 13, 2008

I have a new worksheet:

Employee No. Name

Under the Name colum (B) i want a formula that uses the employee number (A) to look up the name. The problem i am having is that i need to look the name up from 3 other worksheets.

On worksheet 1 i have fridays work and 2 i have saturdays work and 3 sundays!! I only want a name to appear if they worked any one (or more) of these days. If they didn't work i would like the name to remain blank.

On all worksheets employee number and name arein colums A and B.
I do have another worksheet that has name and number only in though. worksheet 4!

View 2 Replies View Related

Nested For Next Loop With VLookup

May 25, 2014

I have 2 sheets in the same workbook -- Entry and Setup. Setup sheet contains details about each class including how many arenas are going to be used for the event. The entries need to be divided up amongst the arenas. We are dealing with around 500 entries.

Setup Sheet
Class Number
Number of Arenas Used
Class 1

[Code].....

View 3 Replies View Related

Vlookup And Nested If Statement

Jun 26, 2007

In the example below I have created a validation list in B1 that references D2:D8. What I am then trying to do for cell B2 is create a vlookup and nested if statement that says if b1 is a number lookup the value in column D and return the value in colunm E and if b1 doesn't = a number, ie 'none' then return a null value. The same goes for B3 where if b1 is a number lookup the value in column D and return the value in columm F and if b1 doesn't = a number, ie 'none' then return a null value.dropdown list problem.

************************************************************************>Microsoft Excel - Book1.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEF1Numbernone*NumberDescriptionRef2Description**none**3Ref**1one44***2two55***3three66***4four77***5five88***6six9Sheet2*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies View Related

Nested Vlookup Not Return Correct Value?

Feb 20, 2014

I have the following formula:

=IF(VLOOKUP(A10,'CORE Data 3 Month #1'!$A:$K,6,FALSE)<(VLOOKUP(A10,'CORE Data 3 Month #2'!$A:$K,6,FALSE)-((VLOOKUP(A10,'CORE Data 3 Month #2'!$A:$K,6,FALSE)*0.03))),'CORE Data 3 Month #2'!A3,IF(VLOOKUP(A10,'CORE Data 3 Month #1'!$A:$K,6,FALSE)>(VLOOKUP('CORE Data 3 Month #2'!$A:$K,6,FALSE))+((VLOOKUP(A10,'CORE Data 3 Month #2'!$A:$K,6,FALSE)*0.03)),'CORE Data 3 Month #2'!C3,'CORE Data 3 Month #2'!B3))

The strangest thing is happen. The part of formula underlined and the part bolded are identifical. For some reason, excel is unable to find the value in the underlined portion but is able to find the value in the bolded portion. As a result, I'm getting a #N/A result. how this can be corrected?

View 3 Replies View Related

Nested Vlookup The First 4 Digits In Column

Feb 12, 2009

I have attached a small sample of some data I am working on (the total is about 6000 lines overall spread over 30 worksheets), but I am stuck trying to get a nested vlookup to work.

What I have

A list of codes contained in 'A' and values in 'B'. I have grouped together the codes in colum 'A' starting with the same 4 digits, and gave them a named range. Columns G and H show all the possible range names. 'K' is a list of all the seperate codes (I know it is the same as 'A', but this is just an example to get a formula working)

What I would like formula column L

to lookup the first 4 digits in column 'K', use that value to lookup the range name in 'G & H', then using the FULL code in K, look for that in the corresponding name range and return the value from 'B'

View 4 Replies View Related

Nested IF, ISERROR, VLOOKUP Formula Simplification

Feb 12, 2009

I have filled the following formula in many cells of a spreadsheet.

=IF(ISERROR(VLOOKUP(D75,'Sheet1'!$D$3:$D$5442,1,FALSE)),0,IF(ISERROR(VLOOKUP(C75,'Sheet1'!$C$3:$C$54 42,1,FALSE)),0,IF(ISERROR(VLOOKUP(C75&" - "&D75,'Sheet1'!$A$3:$I$5442,9,FALSE)),VLOOKUP(C75&" - "&G75,'Sheet1'!$B$3:$I$5442,9,FALSE),VLOOKUP(C75&" - "&D75,'Sheet1'!$A$3:$I$5442,9,FALSE))))

I am wondering if I have nested too many functions for this to work properly. In the case where one of the first two if statements are true (an error would be produced), I am receiving a "0" as specified. However, in the case where the statement VLOOKUP(C75&" - "&D75,'Sheet1'!$A$3:$I$5442,9,FALSE) results in an error then I should have the return of VLOOKUP(C75&" - "&G75,'Sheet1'!$B$3:$I$5442,9,FALSE), but am getting a #REF! error instead.

View 3 Replies View Related

Returning Text From Nested IF And Vlookup Statements

Jul 26, 2006

I am trying to return a text statement using nested IF statements. In order to find the value in the IF statements, I have to use lookups.

Example: ....

View 14 Replies View Related

Nested Vlookup With If Statement: Match Up Two Sheets

May 19, 2009

I am trying to match up two sheets and I the match criteria is based on the data from columns B, C, D, E, and G. Sheet 1 contains ~20,000 line items of data. Sheet 2 contains ~250 line items. I am comparing the info in sheet2 vs. the data in sheet1. Both sheets are formatted the same.

For each row in sheet2, I need to compare against all rows in sheet1. For example; for the first row in sheet2, determine if there is a row in sheet1 that contains the exact same data for columns B, D, E, and G (all must be true) and the data in column C is False. Examples:

View 2 Replies View Related

Data Validation With Nested IF Statements Or VLOOKUP Does Not Work

Jun 12, 2009

Excel spreadsheet will contain a form that will consist of drop down boxes (data validation). Each drop down box will define the data that can be selected in the next drop down box.

The way I initially went about it, is creating nested IF statements. However, we all know there is a limit of 7 nested IF statements. I have 10! So below formula does not work:
=if(B5=1,F5:I5, if(B5=2, F6:I6, if(B5=3, F7:H7, if(B5=4, F8:J8, if(B5=5, F9:H9, if(B5=6,F10:G10, if(B5=7,F11:H11, if(B5=8,F12:H12, if(B5=9, F13:I13, if(B5=10, F14:K14))))))))))

I tried other workarounds such as CONCATENATE, or & signs. No luck. VLOOKUP does not work also, because there are multiple columns in col_index_num. Anything else I try gives me this message:
“The List Source must be a delimited list, or a reference to a single row or column”

What should really happen is this:
User selects value in first list box. Second list box shows values associated wih the value from the first drop down only.

View 9 Replies View Related

Add Nested ISNA & VLOOKUP With Wildcard Via Macro Code

Aug 23, 2008

I have the following vlookup working exactly as I want it to in excel: =IF(ISNA(VLOOKUP(LEFT(C2,10)& "*", NAME,2,FALSE)),"",(VLOOKUP(LEFT(C2,10)& "*",NAME,2,FALSE))) -NAME is a defined section on another worksheet

The problem I have is when I come to use this within a vba macro I'm writing the wildcard section automatically gets spaces added so it goes from "*" to " * " and excel doesn't like it! The code I'm using to write it into the cell is simply:

ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(LEFT(c2,10)& " * ",NAME,2,FALSE)),"",(VLOOKUP(LEFT(c2,10)& " * ",NAME,2,FALSE)))"

how to stop the spaces being added?

View 2 Replies View Related

VLOOKUP And COUNTIF Together?

Jan 27, 2010

My boss has asked me to work this one out and is putting me under great pressure to resolve it. I have tried Vlookup and COUNTIF etc but just cannot get to make it work. I have attached an example file but to explain:

I have a database of inspectors faults, in column B, I have the week number, column*C, I have the part number & column C the fault category (this includes OK). The actual database is up to 5000 rows now.

What I need to be able to do is in one cell (column H2) count all the OK's for week 1 and all the Not OK's (column I2) in week 1 and the same for week 2 (column H3) and week 3 (column H4) and so on for the whole year. I have tried a vlookup using cell G2 (containing "1") as the search and the week number column as the range and then counting if OK, but it never worked!

View 2 Replies View Related

Use COUNTIF In Conjunction With VLOOKUP In VBA?

Apr 15, 2013

I am trying execute a VLOOKUP and counting a specific cell, if it's data is greater than 0 for nine different files, essentially summing the number of data points. Below is the beginning of my formula.

Code:
Range(Range("W8"), Range("W" & Analyst)).Formula = "=COUNTIF(VLOOKUP($A8,'" & Folder & "[" & Barker & "]" & A & "'!$A8:W500,23,0),"">0"")+COUNTIF(...

View 6 Replies View Related

Vlookup Or Countif Using Text Strings

Sep 10, 2009

In the attached sheet I have a list of names with hours overtime hours worked. I want a small table on the sheet which looks up when "name x more" occurs. In cell C14 for example I want it to count the number of times "Mark More" occurs in column I, but the name may change in C13, so I want to automate this - so the countif in cell C14 will reference whatever text is in C13.

View 2 Replies View Related

Excel 2010 :: Countif And VLookup Of Multiple Values

Jan 15, 2014

I am working with an Excel 2010 workbook that has two worksheets in it. What I am trying to accomplish is I want the second worksheet to scan the first worksheet for a student's name, and count all of the instances that the student has a score less than a certain threshold (we'll say "5" for this example). I have tried using various combinations of vlookup and countif functions, but have not had much success. I did get it to a point where it worked, but only for the first instance of that student's name; it wouldn't continue searching the first worksheet for any other instances.

I have attached a sample workbook as a reference : Sheet1.xlsx‎

View 8 Replies View Related

Countif Or Vlookup: Get The Total Count Of New And Active-to-date

Oct 15, 2009

I have two columns - Employee and Status of Deal. Now, I need to get the total count of New and Active-to-date but on a per employee basis. Am I making any sense? To clearly put it, I need a formula that will answer the questions below:

How many New and Active-to-date deals does Michael et al have? I tried to use Countif but that gave me the total number of deals, not on a per status basis. Will VLookUp work or a combination of both? If so, how? I'm using Excel 2003.

View 3 Replies View Related

Vlookup Countif Nest: Count Of Sizes By Position?

Oct 25, 2009

On attached Spreadsheet, we are using Vlookup to create Team pages for coaches. Works great for giving coaches a team sheet when they leave draft. I have been aasked if we can cultivate the info a little differently for ubiform screenning. Company wants a count of sizes by position, that is everyone in position 1 will get number 40. How many #40 AS, AL, AM do we need.

There are 157 teams. SO I need to loop throgh each team, pull each size for each position and put it on Size sheet. I was thinking a vllookup/countif or sumif combo would work but have not been able to come up with it.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved