# IF, VLookup, Sumproduct Combination

Dec 22, 2009
formula below... currently is giving me a:#N/A

=IF(EG55="FCST",VLOOKUP(--EE55,Control!$R$8:$R$20,6,0)*(SUMPRODUCT(--($HH$11:$HH$62=EE55),--($HJ$11:$HJ$62=EJ55),--($HL$11:$HL$62))),NA())

If I remove the vlookup part the formula return the correct results, but I want the resulting number be multiplied by a percentage (that what's the vlookup is trying to accomplish).

View 9 Replies
ADVERTISEMENT
Oct 9, 2009

I need combining sumif & sumproduct. I have attached a file which explains what I need.

View 2 Replies
View Related
Jul 25, 2009

Ive attached my example but explaination of what i am trying to do is below:

In sheet 1 i have products listed with a product ref

In sheet 2 i have a list of features by product ref.

I want to be able to put each feature next to the relevant product in sheet 1, some products may have 3 features, others may have 5 or more.

View 4 Replies
View Related
Apr 24, 2014

How to use offset in combination with match and vlookup. Well I think I have to use Offset to find the value ( cell with time in it).

I have in my workbook 3 sheets: Sheet1, Sheet2 and Agents.

In 'Sheet2' every week I upload a report with persons and every person has a certain amount of time behind their name.

In 'Sheet1' I want to get (load) the data: the person and time from 'Sheet2'.

In 'Agents' I only match the names. That's because the names in the report I upload in 'Sheet2' have a different lay-out then the ones I use.

The matching and to get the names correct in 'Sheet1' Is no problem. Though I get stuck with the cells where the time is placed in the report I upload in 'Sheet2'.

The persons are in Column C ( C7, C26, C45, C64 etc) but the value I also need to get is not in line behind the names. It's In the 7th row under the name and in Column L.

Example:

Wiebe (C7) time ( L14)

Gary (C26 time ( L33)

Kay (C45) time ( L52)

What I use to match the names and get data is this formula.

=INDEX(Sheet2!$A:$L;MATCH(VLOOKUP(Sheet1!$A2;Agents!$A:$E;5;0);Sheet2!$C:$C;0);MATCH(B$1;Sheet2!$1:$1;0))

Is it possible to use Offset ( or something different) in this formula to also get the cells with the time ( matching with the right person)

View 14 Replies
View Related
Aug 14, 2014

What I want to do is the following, I have two sheets, one where the data needs to be filled and the second where the date needs to be looked up. In Sheet1 I need to find a date for each of the NR2 and NR1 combination. But in the second sheet there are multiple NR1 occurences and also single occurences. So if there is only one, I need that date, if there are several I need the average of all the occurences for NR1, not taking into account the N/A ones.

(some examples from the file)

NR2 NR DATE

100707987121951

100702347121960

100707750121960

100707721121960

100702422121960

[code]....

So for example, NR1 121965 has two dates, 03/09/2002 and 27/01/2004, so here it should calculate the average of these two and put that average in the first sheet.

I was thinking of something like IF(MATCH(?) gives one result,put that with vlookup, else AVERAGE of all MATCH that are not N/A)

View 3 Replies
View Related
Sep 22, 2009

I have attached an example s/sheet. Basically this is an excerpt of the data that sits in a pivot table. What I want to do is from another sheet query this data. I don't want to use another pivot table as they are quite hungry in terms of memory and the data source we have is quite large. In essence what I want to achieve is in cell G2 the user enters a code. A function (vlookup?) will then scan column A to find that code.

The function then needs to look across and sum the total of Requests and Responses for all the dates. Whilst the dates may change, the number of dates will remain the same. Once it has summed them it needs to return the totals to cells G4 and G5. Additionally it needs to fill in the relevant total (offset?) for the corresponding week as detailed in columns H-AH. It seems quite a simple lookup issue but I am not very versed in nested lookups. I have looked around and it seems INDEX woudl do the job but I am at a loss on how to construct this type of function.

View 3 Replies
View Related
Jan 21, 2014

I have a file with two work sheet, in 1 sheet have monthly allowance to staff in 2nd sheet I need the data in a schedule format. Please see the attached file, my formula not working here properly.

View 6 Replies
View Related
Jan 30, 2009

I have a daily tracking sheet. I want (off to the right) to be able to enter start/end dates and have it sum the total grossage for JUST those dates alone. Which function do I use?

http://s401.photobucket.com/albums/pp94/nmweir/?action=view¤t=untitled.jpg" target="_blank">http://i401.photobucket.com/albums/pp94/nmweir/untitled.jpg" border="0" alt="Photobucket">

direct link? :

http://i401.photobucket.com/albums/p...r/untitled.jpg

View 9 Replies
View Related
Feb 17, 2009

Column B has the "date"

Column D has the "time"

Column E has the "field" then in

Column F is "VIN" and

Column G is "HOMI"

Then I have another area on my page that I need to redistribute data.

I need a vlookup, sumproduct, or something that will give me the data I want.

here is what i am looking for:

R138 through AE300 is the data area.

Column R has the "date"

Column T has the "time"

Column V and W are "field 1"

Column X and Y are " field 2"

Column Z and AA are "field 3"

Column AB and AC are "field 4"

Column AD and AE are "field 5"

I want to be able to put a formula in the field areas V138:AE300 that will find the "VIN" and put it in my new area and my "HOMI" and put it in the new area.

Is this impossible?

Or is this just wishful thinking?

If I need to give anymore info let me know.

I cannot add any software at work so I can't show you the data I am talking about, unless i can copy and paste the sheet to show you.

View 9 Replies
View Related
Apr 29, 2009

this formula works but returns #N/A when the result is false, how can i get rid of N/A but replace with 0.00 even though i have 0.00 as false

=IF(VLOOKUP(A10,Down!$F:$H,3,0)="No",SUMPRODUCT(($A10=Downloads!$F$7:$F$3823)*(CT!E$9=Downloads!$A$7:$A$3823),Down!$D$7:$D$3823),"0.00")

View 9 Replies
View Related
Jul 12, 2012

I have a formula that I can't get to paste successfully in the forum - it keeps getting cut off?!? ... but I think I can probably simplify my explanation to get the answer I want anyway.

I need to only show the value from AUS!$H$2:$H$17 if the C2 & D2 combination are the same as the AUS!$B$2:AUS!$B$17 & $AUS!$C$2:AUS!$C$17 combination.

View 9 Replies
View Related
Mar 20, 2009

I have 2 cells with numbers. In a 3rd cell I want to create a formula which looks at the 2 data cells and shows a value. The rules are the following: If C1 or C2 are bigger than Xthen C3=value1 else C3=value2. I have some basic excel knowledge but im not very familiar with functions. I'm using Excel 2007.

View 4 Replies
View Related
Feb 28, 2014

I need to consolidate several amount and make sure which of them falls into a sum of an amount.

I wonder if excel can find all the calculation in range A, and highlight those that equal to the cell D10 (example as per my attachment).

Calculation.xlsx

View 5 Replies
View Related
Jan 16, 2010

how to create a list (I know how to calulate the count) of combinations without repetition when choosing 2,3,4 and 5 words from a set of 5 in Excel 2007.

e.g. Alpha,Bravo,Charlie,Delta,End

(AlphaBravo=BravoAlpha)

Choosing 2 = 10

Choosing 3 = 10

Choosing 4 = 5

Choosing 5 = 1

View 11 Replies
View Related
Nov 13, 2013

I have data in a 5x5 area and I would like a VB Script or Function that can give every possible combination of one number from each column added together.

i.e.

500 400 300 200 100 1500

400 300 200 100 500 1900

300 200 100 500 400 1800

200 100 500 400 300 etc...

100 500 400 300 200

View 5 Replies
View Related
Jun 22, 2007

I have a list of numbers from 1-7 would look like this each number in a seperate cell.

3 5

1 2 3 5 6 7

2 4 5 6 7

6 7

4 6

6

I want to use one number from each row (which there is only 6 rows) and then find every number from 1-7 that will complete the sequence 1-7. So with the numbers above (using one number from each row) the only other numbers that could be used would be 3 or 5.

The combos that would work:

row 1 use 3 ----------- row 1 use 5

row 2 use 1 ----------- row 2 use 1

row 3 use 2 ----------- row 3 use 2

row 4 use 7 ----------- row 4 use 7

row 5 use 4 ----------- row 5 use 4

row 6 use 6 ---------- row 6 use 6

5 would complete ---------- 3 would complete

Remember the numbers and how many numbers in each row can change but will always be 1-7 and I always need to find every number that can complete the sequence 1-7 by using one number from each row.

View 9 Replies
View Related
Nov 15, 2008

on combination of numbers

on the extreme left column, i have 23 numbers from A1:A23. All 23 numbers are in the form of 4 digit. For example A1 there is 1234, i need to display the possible 3 digit combination of this in the same row (like say 123,124,234,134 in B1,C1,D1 AND E1).

Another example in A2 there is 3545, i need to display 354,455,355 in the same row in B2,C2,D2

I need to perform this operation for the 23 numbers on the extreme left row. Can give me some hint on the code.

View 9 Replies
View Related
Feb 10, 2006

I am trying to show the all possible combinations of a set of numbers in Excel, in my case I think permutations are more appropriate to use. For example: there are three numbers 1, 2, 3 I want to show results like:

1, 2, 3

1, 3, 2

2, 1, 3

2, 3, 1

3, 2, 1

3, 1, 2

The functions in Excel available only give the total number, but I want to see these combinations!

View 6 Replies
View Related
Jan 23, 2014

I was just wondering if it was possible to only allow cells in a worksheet to only allow values that are a combination of 2 arrays whether it be through data validation or other means. For example, if I have an array that has a b and c and a second one with 1 2 and 3, is it possible to only allow values a1 a2 a3 b1 b2 b3 c1 c2 c3?

View 2 Replies
View Related
Mar 10, 2014

I have set of people where by based on Level & designation want to find the percentile on their CTC.

Attached is the sample file. Percentile.xlsx

View 6 Replies
View Related
Apr 6, 2009

Can Offset be combined in an Index Match Match formula as per the attached sample?

View 4 Replies
View Related
May 11, 2009

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.

View 6 Replies
View Related
Aug 18, 2009

I would like to check a combination of two cells, if these two cells are both empty (not zero, just blank) then it will return a blank in another cell. I tried using AND but am unsure how it works. I would like to use a "Case" Function.

Function FirstCheck(Count1, Count2)

Select Case FirstCheck

Case Count1 = "", Count2 = ""

FirstCheck = ""

Case Else

FirstCheck = Abs((Count1 - Count2) / (Count1 + Count2))

End Select

End Function

View 3 Replies
View Related
Jan 17, 2010

I have created a program where there is a spreadsheet containing all of the items in a loan tools store, which are issued out to people. I have created a "Search/Find" function within the "Issued Items" sheet. Within this search function, you are able to search all or any combination of the following criteria: Serial; Description; Name; Location. For example: If you just type data in the "Serial" field, it will just search that column and select the cells which contain that value.

The problem I am having is when searching multiple criteria, each and every cell in the columns which are searched is selected. Whereas, I would like only the cells which match all of the criteria to be selected.

For example: If I was to type "1" into the "Serial" field, "2" into the "Description" field, "Liam" into the "Name" field, and "Workshop" into the location field:

Current: Serial column is searched and all cells with "1" in are selected. Description column is searched and all cells with "2" in are selected. Name column is searched and all cells with "Liam" in are selected. Location column is searched and all cells with "Workshop" in are selected.

What I Would Like: Program to search each of the specified columns and only select data which meets the searched criteria. For example: Rows 20 & 28 to be selected as they both contain, Serial "1", Description "2", Name "Liam", Location "Workshop".

Note: The sheet will have password protection.

View 10 Replies
View Related
Oct 17, 2007

i've attached a worksheet yet removed my attempt at formulas as it would have made most of you cry... what i'd like to do is select an item from a dropdown list (B1) (that i've built and it works, phew) and display the summary data (B3:B10) from the column or sum of columns in array (A12:F21) as explained in the relationship matrix.

I've tried ifsum and dsum (copied in each cell B3 to B10 of course) yet it doesn't extract items from the array with the drop-down selection. nor does it add columns together.

View 14 Replies
View Related
Apr 8, 2009

I am trying to sum 12 columns based on looking up a reference that is in one column. Basically I have 2 files where on both files Column A has a G/L account number. On the data file I have credits for each month going from column C to Column O. On the other I have one column where I want to bring in the sum of all the months based on looking up the G/L number in column A.

View 4 Replies
View Related
Dec 24, 2012

My Columns are as follows:

A1-Criminal Name, B1-Crime, C1-Age, D1-Ratings, E1-Punishment

In the punishment column one will get 22 years life imprisonment if he fulfills the following conditions.

1. He must have done Rape OR Murder

AND

2. His Age should be >30

AND

3. His Ratings should be>8

It should throw 22 years in the Punishment column only if the above conditions are met otherwise it should be Nil.

More Info on this:

1. Crime column includes Murder, Rape, Robbery, Assault, Kidnap etc

2. Age column ranging from 22-75 years.

3. Ratings column ranging from 1-10 points

4. There are 3400 records we have in the list

How to write an IF AND OR combination formula for this ?

View 9 Replies
View Related
Jul 23, 2013

I have to write a formula which states the following:

if cells AA1,AB1 &AC1 = 0 then "Slow-Moving",

if of these cells AA1,AB1 &AC1 contains a number then "OK",

if cells, AA1,AB1,AC1,Z1,X1,Y1 all = 0 then "Non-Moving"

I believe an If and AND combination could work but its not working for me.

View 8 Replies
View Related
Mar 14, 2014

1

0

0

4

6

=largeodd(a1:e1)

=largeeven(a1:e1)

write a udf function to deal with the above ?I know large(a1:e1,1) for picking up the largest one in the row,but no idea to find the largest one when these five numbers are combined to build a 5-digit number.

[ want to find largest 5-digit (also 4 ,3 digit ) numbers combined by thess five numbers]

View 9 Replies
View Related
Jan 26, 2009

I'm trying to figure out how to setup a worksheet to find the most common 2 digit numbers going vertically from the bottom(cold) to the top(hot) it would consist of 90 digits 0 thru 9

it would look like this

4 0 3 9 0 4 3 3 2

9 2 5 6 5 6 9 6 6

8 9 9 3 1 0 2 9 8

1 6 7 5 9 9 8 2 5

2 7 2 2 2 8 5 1 3

0 1 4 7 4 7 6 0 9

3 5 6 4 3 3 0 4 4

7 8 8 1 8 5 7 8 7

5 4 1 8 6 1 1 5 1

6 3 0 0 7 2 4 7 0

each vertical line would be considered weeks 9 thru 1. week 9 would be the first vertical line of digits on the left. it could also contain the most common 2 digits horizontally. Both 2 digit values would be color coded ex. blue equals most common 2 digit horizontally and green equals vertically. I would also like to color code the most common 2 digit value diagonally as long as it is the most common of either the vertical or horizontal 2 digit. Each number is seperate on the worksheet they would not be pairs. im using excell 2003.

View 9 Replies
View Related