# Nest / Combine 2 Sumproduct Functions From 2 Different Tables

Aug 20, 2014
Looking to nest or combine the following 2 functions because I'm looking up the same cell content, except within 2 different tables.

=IF(B12="","",SUMPRODUCT(--($B$12:$B$17=X18),--($S$12:$S$17))) and =IF(B22="","",SUMPRODUCT(--($B$22:$B$32=X18),--($S$22:$S$32)))

Right now, these functions works well from a single table but need to combine them. Other function examples are welecomed too.

View 6 Replies
ADVERTISEMENT
Dec 16, 2006

I've got an indexmatch that works great

=IF(ISERROR(INDEX(accountstaff,MATCH(B$20,INDEX(accountstaff,,1),0),MATCH($ A55,INDEX(accountstaff,1,),0))),0,INDEX(accountstaff,MATCH(B$20,INDEX(accou ntstaff,,1),0),MATCH($A55,INDEX(accountstaff,1,),0)))

But I need to incorporate IF statement based on varying levels of revenue and think VBA will be less cumbersome, but don't know how to combine else if and index match.

I am trying to accomplish this:

IF B$20 (which is a dollar amount is = X, then index, match....

IF B$20 is > Y but < Z, then index, match...

View 9 Replies
View Related
Dec 13, 2006

All I am doing is counting text values in a table.

The table has 3 main columns(which are relevant to this thread anyway).

Shift - Area - Status

The example I have attached shows examples of DCOUNTA, SUMPRODUCT and a Pivot Table.

I have read many threads stating that the best one to use is Pivot Table followed by DCOUNTA followed by SUMPRODUCT.

The most effective for me seems to be SUMPRODUCT (although this does slow excel down dramatically when you use a lot of these formulas). As do Array Formulas

The Pivot Table does not update on its own, therefore constantly needs to be refreshed. (I could use code to do this)

The DCOUNTA seems to be the least effective at doing what I want (unless I am doing something wrong)

In the attached example can the DCOUNTA be used more efficiently as I don't like the fact that I am duplicating rows to apply the criteria for a different shift. e.g

Area 1 - Late Shift - Banned

Area 1 - Early Shift - Banned

I want my table to be as follows (as the SUMPRODUCT shows)

AREA - Early Shift - Late Shift - Night Shift - Area Total

Area 1

Area 2

Area 3

Area 4

Area 5

Shift Total

View 4 Replies
View Related
Oct 23, 2008

I've already nested INDEX and MATCH sucesfully

I wish to embed/nest (I'm not sure what the difference/ correct syntax is)

These Funtions:

1. "=INDEX($H$4:$H$9,MATCH(G14,$G$4:$G$9,0))"

2, =HLOOKUP(Arg!D10,Arg!$D$10:$DA$50,5,FALSE)

The HLOOKUP returns a text value that the Index and Match convert into a number. I wish this to be done in one cell - is this possible?

I'm using INDEX with MATCH because HLOOKUP can return a 'not applicable' that equals '6' this need to be discounted in my results but that is a digression FYI.

View 2 Replies
View Related
Jan 29, 2008

I have a spreadsheet that is by:

Purchase Date (column A)

Sales Date(column B)

Quantity(column C)

Name (column D)

Gain/loss (column E)

The spreadsheet is sorted by name in ascending order and also by quantity in ascending order.

Assuming row 1 is the heading. If D2 (name)= the same as another D cell and it's quantity i.e. C2 is negative, while the other D cell with the same name as D2 has a positive quantity i.e. corresponding C cell, and if the date in B2 is greater than date in column A of the other cell where it's D cell matches D2 and has a positive quantity, then in I would like "possible" to display in F2.

I have included an attachment to better illustrate what I have described above, because I am not sure if I am clear enough.

View 9 Replies
View Related
Jun 1, 2008

I would like to use a custom function or excel formula, if already exists, that finds the text "12 months" and then returns the value of the column that holds that value.

View 9 Replies
View Related
May 27, 2009

I tried to do a search for this but the search function was not working.

I have an excel sheet that I can scan Mac address into. The reason I created the sheet is because when I scan them in they are uppercase and do not have separators. I would like to combine 2 excel functions into 1

This one

=LOWER(C2)

and this one

=LEFT(E2,2)&":"&MID(E2,3,2)&":"&MID(E2,5,2)&":"&MID(E2,7,2)&":"&MID(E2,9,2)&":"&MID(E2,11,2)

View 2 Replies
View Related
Jul 29, 2014

I've been struggling with the following problem for hours now and getting nowhere fast.

I've got '=(100/X6)*L6)' in cell M6 which gives my percentage fine but I need to round to the nearest integer with '=ROUND(M6,0)' Question is how do I combine both functions to provide the nearest whole number in M6 ??.

View 6 Replies
View Related
Dec 16, 2008

In the attached xls I have a button on each tab, ideally I would like the button on the first tab to do everything, but I cannot seem to get it to work hence the current layout.

First button on first tab creates new xls and copies content of 2nd tab onto it, then copies various bits of info from certain cells in tab 2 into the 3rd tab.

2nd button on second tab simply clears the contents of the form, which is no longer needed as a seperate copy has just been created.

3rd button on 3rd tab just tidys up the cell borders

It would be much easier if I could get one button click to do everything (in that order preferably).

View 14 Replies
View Related
Apr 1, 2013

I have:

- Table 1 has ~1,600 records (name of game, meta score, user score)

- Table 2 has ~3,000 records (name of game, genre, publisher)

I want:

- Table X has ~1,600 records (name of game, meta score, user score, genre, publisher)

Basically, I want to take the genre and publish columns from Table 2, and add it to Table 1. However, I want to ignore the extra ~1,400 games that Table 2 has.

I'm nearly a complete noob when it comes to excel. Noob to the point where I don't even know what this thing is called, so I don't even know how to search for it properly! I know something can be done since there is a common column between the two (name of game).

Edit: Also, I think it'd be easier if I were able to attach my excel spreadsheets but I don't even know how to do that

View 1 Replies
View Related
Dec 1, 2009

I'm analyzing and charting performance data from two different boats. I am attempting find a way to combine the two charts into a single chart that is sorted by MPH as per my manually sorted example.

View 9 Replies
View Related
Jul 15, 2006

Can someone help with this formula,

Cell $A$24 = A cell formatted as Month and Year = July06

Cell $B$1 = a date 1/7/06 linked to $A$24

Trying to use the indirect function to ref a sheet called July06 and other ranges here a example of one range =July06!$D$2:$D$247

This is what I've got

=SUMPRODUCT(--(INDIRECT(TEXT($A$24,"mmmmyy")&"!$D$2:$D$247<="&$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!$Y$2:$Y$247>= "&$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!$C$2:$C$247="&$A2)))))

View 9 Replies
View Related
Nov 13, 2008

i'm trying to perform an operation that gets the standard deviation of all open status. See my formula.

=SUMPRODUCT(($D$4:$D$1591="open")*1,($H$4:$H$1591)*1,(STDEVA($H$4:$H$1591)))

View 3 Replies
View Related
Mar 12, 2007

I have three pages of daily data I am working with; orginal source data, manipulated data, and my output. My output data is a daily row of six columns containing 1, 0, or -1, based on my manipulated data (1,1,0-1,1,-1). I would like by row, sum the coresponding source data (5,4,0,-3,3,-4) by the positve 1s and a second column with the negative 1s. then average the column by the number of consituents (positive5+4+3 )/3=4 & negative;(-4+-3)/2=-3.5)

View 8 Replies
View Related
Oct 2, 2008

XL 2003

I want to combine these three functions

=VLOOKUP(e4,AGTNAME!$A$1:$E$3086,5,0)

=LEFT(E4,FIND("/",E4)-1)

and iserror

View 9 Replies
View Related
Feb 22, 2010

I think this requires SUMPRODUCT, but can't work it out.

The attached spreadsheet should explain clearly.

View 14 Replies
View Related
Jul 18, 2012

I have an excel workbook with 11 different worksheets. Each worksheet represents a project my company is working on. And in each worksheet there is exactly the same table, just with different data).

Now we would like to get an overview of all these different data put together in one 'summarizing' table on a new worksheet (number 12).

-Is it possible make such a table without having to copy past all the time?

-The data in the tables may change over time, so it would be good if that 'summarizing' table automatically adapts to the other ones.

View 9 Replies
View Related
Aug 28, 2009

way to join two or more tables in excel adding new rows and columns.

In this way:

Input TABLE1

ID, Tag1, Tag2

1, alfa, big

2, delta, small

Input TABLE2

ID, Tag1, Tag3

2, delta, green

3, gamma, yellow

Output RESULTING TABLE

ID, Tag1, Tag2, Tag3

1, alfa, big, -

2, delta, small, green

3, gamma, -, yellow

View 9 Replies
View Related
Feb 26, 2014

I have two columns of data that I need to use SUMPRODUCT on. However, in one of the columns, there might be text after the number. The text can be several different characters. I only want to use the numbers, never the text. Also, there are usually blank cells within Column B and at the bottoms of both columns because this formula is going into a template for future worksheets that all have differing numbers of rows.

Example:

14T

16

40

20

150

97L

67

13

12

For the above example, I want the result to be 9879 (14*16 + 40*0 + 20*150 + 97*67 + 13*12 + 0*0 + 0*0 = 9879).

I've tried using the LEFT function to only get the numbers before any text, but I can't make it work with the blank cells at the bottoms of the columns.

View 10 Replies
View Related
Mar 5, 2012

How to add Sumproduct and Min/Max functions? Using Excel 2010

I am looking for the minimum, or maximum number within a range while using the Sumproduct function.

=SUMPRODUCT(--ISNUMBER(FIND("R",CMSB3003.xls!$A$12:$A$20000)),--ISNUMBER(FIND("Reg",CMSB3003.xls!$AF$12:$AF$20000)),

--(CMSB3003.xls!$B$12:$B$20000>=$B$2),--(CMSB3003.xls!$B$12:$B$20000

View 7 Replies
View Related
May 30, 2007

I have two matrices:

table 1: A B

and

table 2: D E

I need to summarize A where B could be found in D, when E is "alpha".

In SQL this will be like:

SELECT * from table_1 WHERE A=1 AND

(SELECT * FROM table_2 WHERE D=B and E="alpha") IN NOT NULL

View 9 Replies
View Related
Jun 9, 2013

I have a spreadsheet that contains two input tables: Parallel and Perpendicular. Next, the user can select a group which a certain entry belongs to. Now what I want is a "summary" of these entries in a table that has no blank rows and combines both Parallel and Perpendicular entries by the Group. Please note that there is no fixed number of inputs for any one group i-e the solution has to be dynamic.The groups are numbered from 1 to 6 and number of groups is fixed i-e 6.

A sample file illustrating the inputs and required output is attached.

I also posted this on: [URL] ....

Sample_file_summary.xlsx

View 2 Replies
View Related
Apr 21, 2014

I have to track how well location managers are keeping track of skill competencies.

We rate skills as...

0 – know nothing ,

1 – read training manual,

2 – check off by manager.

I need the managers to provide these ratings then I have to report what locations need more training. When an new employee is entered into the system, the manager should give them a target of 2 for each skill the employee needs to gain. If the target skill is 2 rating higher than the current skill level, I have a column named Delta that records a 2.

I have learned how to use the pivot table feature. I can make separate pivot tables using filters that show…

a) how many 2s each employee has for a target,

b) how many 2s each employee has for deltas,

a) how many 2s each employee has for a current level and I can show the date the 2s where achieved

What I need is to learn how to combine all of this information on one pivot table (or report), but the filters really seem to make a mess of it all when it is all together. adding multiple sheets to one Pivot table.

Screenshots...

Count of Targets

Count of Deltas=2

Count of Current Skills

!What I need to produce!

[Code].....

View 1 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
Jun 9, 2013

I have a spreadsheet that contains two input tables: Parallel and Perpendicular. Next, the user can select a group (from a drop down) which a certain entry belongs to. Now what I want is a "summary" of these entries in a table that has no blank rows and combines both Parallel and Perpendicular entries by the Group. There is no fixed number of inputs for any one group i-e the solution has to be dynamic.

Attached is a file illustrating the inputs and the required output. I would prefer a formula based approach but not against VBA based suggestions either.

Sample_file_summary.xlsx

Also posted at: [URL] ....

View 2 Replies
View Related
Sep 20, 2009

I am trying to combine sorted data from 2 worksheets to a new work sheet to process further. I have one worksheet with order number, part number, order qty but with different delivery dates. On another worksheet, I have the order number too, part number, the qty delivered. The qty delivered is not always the same each delivery.

My aim is to find out how many are already delivered under a certain purchase order and the balance of undelivered parts.

I used pivot tables to sort out the data but I am stuck here not knowing how to extra the sorted data from the pivot tables to the 3rd worksheet. I will need to match the order number and the part number.

View 9 Replies
View Related
Apr 4, 2014

Basically I have an equation:

=SUMPRODUCT(G9:G11,H9:H11)/SUM(G9:G11)

It's just a simple percentage calculator for my purposes and works fine as is. However, I want to make it dynamic whereby from a user input the length of the array will increase or decrease, e.g. G9-G11 will become G9-G12 if there are four rows occupied with data. For all my other SUMming equations I have solved this and even came up with what I think is a perfectly valid solution for this one. The one problem is that it doesn't work when combined into a single equation.

My solution for the upper part of the fraction is this:

"A1"=SUMPRODUCT(INDIRECT("G9:G"&MIN(ROW(G9)+A8-1)),INDIRECT("H9:H"&MIN(ROW(H9)+A8-1)))

Where the user inputs the number of rows used in A8.

The solution for the lower part of the fraction is this:

"A2"=SUM(INDIRECT("G9:G"&ROW(G9)+A8-1))

[code]....

View 3 Replies
View Related
Nov 10, 2006

I am trying to combine a subtotal formula in sumproduct formula but end in error. Appreciate if someone can assist me with the right formula. I would like to combine subtotal in the following sumproduct formula:

=SUMPRODUCT(((C3:C9)=C14)*((G3:G9)=B15)*(H3:H9))

View 5 Replies
View Related
Apr 13, 2014

i have a macro which takes the results generated and puts them into a table and filters the data so that zeros does not show and arranges the table from largest to smallest. Now i have a problem where i need to select the power, fuel oil consumption, weight and area from one table and the other power, fuel oil consumption, weight and area from the other table and combine them together so that i can have all possible combinations of the data, but i don't want the zeros to show in the combinations. i need the combined data to show on a separate sheet. The data of table 1 varies with rows as shown:

The Data of Table 2 varies with rows as shown:

I would like to achieve a result like this in the following columns and rows without having to input them manually:

View 1 Replies
View Related
Dec 15, 2013

Now I'm trying to improve the functionality of the file attached above.I've attached the file again with another drop-down menu. What I'm trying to do is to have a sum of all the months between the two months indicated in the drop down menu.For example by picking two months from the drop down menus, I want to know the sum of the values between Jan-Mar (Jan, Feb and Mar), or for example Jul-Oct (Jul,Aug,Sep and Oct)...

I've tried to combine SUMPRODUCT with INDEX and MATCH but it doesn't work. I want to build upon this formula:

[Code]....

I forgot to mention that I have multiple strings (column E) which are NOT unique. Basically "VLOOKUP way" won't work, rather it should be "SUMIF way".

In the attached file I've added one additional row (r19), which is the same as r18, just to try how the summing will work.

Dynamic SUMIF(S).xlsx

View 7 Replies
View Related