# Database Functions Vs. Array Formulas Vs. SUMPRODUCT Vs. Pivot Tables

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

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

## Array Mode That Put It Back In The Letter, Number Display For Formulas/functions

Sep 25, 2009

My excel (2003) is stuck in array mode - is there any way to get it out of Array Mode and put it back in the letter:number display for formulas/functions? It's doing this in VBA too which is totally killing me.

## Using VLookup / Pivot Tables To Achieve Reused Formulas

Feb 13, 2013

I am tired of doing the same amount of repetitive work, I would like to know if it is possible to acheive a "template" where I just drop a database pull and it negotiates where the data should fall.

When I pull data, I have the following values I need -

Part number
Date - which is by day, I need grouped by Monday - Sunday to seperate amounts ordered by week.
Branch plant - East and West
Order Quanity which is placed by day
QOH - Quanity of parts I have on hand

I need the days grouped by weeks, 7 days by date equal 1 week.
I need summing part number amounts due within that 7 days into one total.

I would need 4 columns
1) Demand due
2) Supply due
3) QOH
4)Difference

I need each branch to have their own sheet, for a total of 4 sheets... Supply and Demand for both plants.

What it boils down to is I need to know what the demand is per week, and the supply I have to meet that demand, broken down like the attached sheet.

Seems my sheet is too large due to the amount of data for the site, but here is the link to the LARGE File...

[URL] .....

## Loop Through Array In VBA To Refresh Pivot Tables

Jul 24, 2014

I'm hoping to automate some pivot table refreshes. I've got a dozen pivot tables on a sheet all with different numbers. I'm hoping I can write a code that loops through the pivot table names and runs the refresh. Here is the code as it is right now. I've essentially copied, pasted, and changed the name of the pivot table for the refresh.

I would like to create an array in vba (22,21,20,19,18...) that renames the PivotTable and runs the code.

sub Refresh ()
Dim pt4 As PivotTable
Dim Field4 As PivotField
Dim NewCat4 As String

Set pt4 = ActiveSheet.PivotTables("PivotTable22")

[Code] ..........

## Database Like Query / Functions

Jan 4, 2010

I have a workbook file containing several sheets, is it possible to pull data from another sheet or workbook with multiple column criteria.

For example :

i want to filled a cell in Sheet B from Luas_Tanam column in sheet A, i'm thinking a database query like this :

--select from sheet "A" where column "estate" = "BSRE" or column "Divisi" = "Div 01" or column "No_Block" = "J17" or column "Tahun_Tanam" = "2008"--

It goes like =DGET() functions, but i can't get it work, maybe any other solutions?? i've attached example of the workbook, sory for the bad "ENGLISH" question.

## Hard Code Criteria In Database Functions

Oct 2, 2007

Cell A1 is HEAT_CODE
Cell A2 is W5H

In cell
=DMIN(Database,"Cost",A1:A2) return the proper value

I need something like the following so I can use fill down.
=DMIN(Database,"Cost","HEAT_CODE"=A2)

the above returns #VALUE! error.

## Align 2 Tables, Match Two Outputs From A Database

Dec 21, 2007

My goal is to match two outputs from a database so that the same person's record can be found on one row. At present output 2 does not marry up to output 1 due to staff leaving and new appointments being made. The data in output 1 should remain static. The data in output 2 must be manipulated to match the order of output 1.

Output 1 is a 5 column record containing (in A6:E6)

Forename, Surname, Branch, Roll Number, and a % score

Output 2 is again a 5 column record containing (in G6:K6)

Forename, Surname, Branch, Roll Number, and a % score

Each employee can be identified by a unique Roll Number Code contained in the fourth column of each record.
If an ideal match has been found I'd like the letter Y to be inserted in the L column of each record to signify a positive comparison. All non matches should have the letter N in the L column & these non matches should be placed at the bottom of the dataset.

## Sumproduct & Indirect Functions

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

## SUMPRODUCT Combined With Other Functions

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

## Sign & Sumproduct Functions

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)

## Adjust Column Label Selection Multiple Pivot Tables Based On One Pivot Table

Aug 16, 2013

I have a pivot table in the first sheet which includes the field "Date" as a column label.

In the remaining sheets, except for one, there are pivot tables based on the same underlying dataset which also include the field "Date" as a column label.

I would like to adjust the selection (i.e., exclude some dates) from the column label in the first sheet and see if it is possible to make the same adjustments automatically to the pivot tables in the remaining sheets as well.

note that the field "Date" is used as a Column label, i.e., it is not a Report filter.

## Excel 2003 :: Linking Worksheets - Database Tables

Jul 27, 2014

I have a number of separate worksheets in one spreadsheet all based on the same list (eg customers); the customer's name is the first column and hence the 'key' in each worksheet; when I insert or delete a line in the main worksheet the formulae in the first (customer name) column are amended in the other worksheets BUT I ideally need more than that; when I insert a new customer in the first (main worksheet) I need a new line with that customer name inserting into the corresponding place in the other worksheets; and when I move a line (eg delete a customer and move them to the bottom of the main worksheet list) I need the corresponding lines in the other worksheets moving as well. I guess what I really need is a drill-down function; a main customer list and sub-lists all linking back to the main lists like you would get in database tables.

## Importing Data From Access Database Without Linked Tables

Jan 6, 2014

Bar those who don't believe/celebrate in Christmas, Hope you all had a good Christmas and New Year celebrations.

I have two sets of data, lets call them 2013 and 2014. I have a Spreadsheet already set up in excel, but currently I just past this information into a tab in excel (which isn't ideal at all), and given the data is growing, is slowing down my excel spreadsheet a lot.

I have some experience with Access, SQL and queries.

What I have done thus far is to link my Access DB with Excel, run a query and paste the resulting table from the query into excel all using VBA (easy peasy).

The problem i have is that the table is still linked, and i do not want or need it to be linked. I know that there is a "Unlink" Option, which is not what i need, and also a "Convert to Range" option which is also not what i need, but closer. This got me thinking, is it possible to import the data into an array (all in VBA in excel from the query in the SQL) and then i can paste that information into my Excel spreadsheet, or is there a better method?

## Sumproduct Across Tables

Feb 22, 2010

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

The attached spreadsheet should explain clearly.

## Change Pivot Source Data In Multiple Pivot Tables?

Jan 21, 2013

I have a single workbook with multiple worksheets. Each worksheet has a different pivot table displaying a different view of the data. Each pivot table uses the same source data at worksheet1.

Each week i add new data to the end of the source data, which means that I need change the source data reference separately in each pivot table to update each pivot table view to include the new data. This is laborious as there are quite a few pivot tables.

Was wondering if there is some way of changing the pivot table source data reference on all pivot tables at the same time.

## LEFT And SUMPRODUCT Functions Combined?

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.

## Excel 2010 :: Sumproduct And Min / Max Functions

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

## Refresh Pivot Tables Linked To Pivot Table

Jul 25, 2006

I currently have several pivot table that's linked to a single pivot table(let's call it X) in the same workbook. I'm doing this to limit the file size because the data in X comes from a text file that has millions of lines. However, it's such a pain every time I need to update the tables because simply clicking "refresh" does not update those tables that are linked to X with new data. I would have to instruct the wizard in every linked table to point to X every time. I'm trying to write a small program to re-point to X for each of those other pivot tables whenever i refresh data. However, after trying to record the steps to do this I'm still unable to run these

Sub Macro1()

ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:= _
"PivotTable1"

End Sub

## Pivot Table Based Off Multiple Pivot Tables

Sep 5, 2006

Is it possible to create pivot table from another multiple pivot table.

Example: I have two diff pivot table "Income" and "Expense" as well
and I need to preapare new pivot table using with those two pivot table

## How Can I Integrate Two Tables In Sumproduct

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

## SUMPRODUCT / INDIRECT Functions Not Working With ROW Reference When Combined

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

## Can Pivot Table Be Created From Several Other Pivot Tables

Mar 5, 2014

I have 12 months worth of data in twelve separate workbooks each with a pivot table, I have copy/moved the pivot table tab from each into one workbook so now I have a workbook that consists of 12 tabs each with a pivot table. What I would like to be able to do is create a summary table with the full years data; where I am running into problems is that each months table has slightly different row and column counts and labels making any formula like =sum([sheet 1 cell a1]+[sheet 2 cell a1]) problematic.

## Pivot Tables: Pivot Table Layout

Oct 14, 2003

if there is a way to display a table as column percentages but have the totals as raw numbers.

## Show All Pivot Items In Pivot Tables

Jun 19, 2008

I've got 4 pivot tables (all derived from the same base data) on 4 separate worksheets. I've been able to (with this help of this site) to use VBA to hide pivot items on all of these sheets using a list on a user form. Hide/Show Pivot Table Field Items. Hide Pivot Table Fields Pivot Items by Criteria

I now need to be able to show all the pivot items on only 3 of the 4 pivot tables, with the 4th pivot table being left untouched. For ease assume that my sheets are sheet1, sheet2, sheet3, and sheet4. The tables I wish to update are on sheet2, sheet3 and sheet4. The pivot table on each sheet is called "PivotTable4" and the pivot item is called "Business". The pivot item contains 12 business names (Business1, Business2 etc etc)

Is there an easy way of doing this? I've spent the day looking through the internet and various "Dummies" books but with little success, I fear that I'm obviously below even Dummy level

## Sumproduct- Database That Shows Repeated Templates Down The Page

Feb 26, 2007

I have a database that shows repeated templates down the page...ex.

"Site" (here) Remnant
Jan
Feb
Mar

"Site"(here) Remnant
Jan
Feb
Mar

All after another using the same template.

I am trying to sum all the occurances that fit the criteria Jan and Remnant:

I have this:

=SUMPRODUCT(--('"STATIONS"'!\$A\$4:\$A\$300='"GROUP"'!\$A3)*('"STATIONS"'!C\$3:R\$3='"GROUP"'!C\$2),'"STATIONS"'!\$A\$4:R\$300)

But I get a N/A , my reference A3 matches the dates in the database and C2 matches the repeated occurances of Remnant...

Is this possible since my templates keep repeating down my database?

Basically, each template in the database is related to different stations, and I am trying to sum all the stations details in the database for each month and Header"Remant"

## Convert IF Functions To IF OR Formulas

Jun 12, 2008

To avoid manually updating a formula 100+ times, I am looking for a way to update cells in a column with reoccuring formulas with references to other cells that vary respectively from cell to cell. Meanwhile, there are other cells without formulas that this will not apply to in the same column. I am trying to avoid macros if possible, unless it can be easily understood/modified by someone who knows next to nothing about macros or VBA. My initial thought was to use the find/replace feature, but I don't know of any way to do this so the reference will update respectively for each cell (ie A9 then A10 etc.) in both the find and the replace fields.

ie
Find: A9="Text1"
Replace: OR(A9="Text1",A9="Text2")
Where the row number updates respectively.

Simplified example:

Existing:
=IF(A9="Text1","1","2")
[misc. blank or non-blank cell]
=IF(A10="Text1","1","2")
etc..................................

## How To Build And Execute Formulas And Functions

Dec 10, 2012

how to build and execute formulas and functions. My love for messing with excel started with an interest in personal financial management utilizing amoritzation tables, budget planning, paychecks, tax bases, etc., My wife sees it as strange but I love messing around with the Excel formulas. For now it's all self taught but, as stated, that will be changing.

So far, I haven't been able to find how to execute the following scenario.

For brief background, I'm attempting to identify the results of various outcomes of an individual currency trade by changing the investment size given the results of the last "x" amount of trades. Put another way, I'm running data sets on Forextester wherein I trade a historical period of time on a specific set of currencies, i.e. "Pound/Dollar." I'm wanting to know what the resulting balances would be, or would have been, if I implemented a money management strategy that increased my investment on consecutive trades when there was an "x" number of consecutive "wins" (ending account balance more than beginning) on the previous "x" number of trades. and decreased my investment when there was an "x" number of consecutive losses (ending account balance less than beginning account balance) on "x" number of trades...

So far, I've built the formula so that if I input the beginning and ending balance of a specific trade and Excel populates a "W" or "L" (Win or Loss) in the "Results:" Cell which was pretty simple. The following is one of three scenarios.

Strategy
Conservative:

Start with risk 1% of Account balance (which is \$10,000 for example)

The Rule: With each open trade reduce the account balance used by the amount risked on opened trade
(For consecutive trades, assume current trade results in a total loss of investment. Thus, \$100.00 of the \$10,000 accounts means you're working with \$9,900 on next trade)

The Rule: After 3 consecutive winners that make (1.8 * Risk percent used by the trade * account balance used by the trade) or greater in profit, increase risk to 1.5%

First trade Investment: \$100.00 plus minimum of 80% gain. Second Trade Investment: \$99.00 plus minimum of

80% gain. Third Trade Investment: \$98.00 plus minimum of 80% gain. For trade number four, the investment amount would now be 1.5% of the total account balance.

After 1 loss reduce risk to 1%
First Trade Investment \$100.00 and you lose - size of loss doesn't matter. Next trade opens with investment of 1%.

After 2 consecutive losses reduce risk to 0.5%

First trade Investment \$100.00 and you lose - size of loss doesn't matter. Next trade opens with investment of 1%. Second trade is also a loss. Thus, third trade invests 0.5% of total account balance.

After 4 consecutive losses reduce risk to 0.33%

After 2 consecutive wins that make (1.8 * 0.33%* account balance used by the trade) in profit, reset risk to 1%
(Two wins = 1% investment, regardless of number of previous losses

There are two additional scenarios but they all follow the same pattern. The differences are only in the actual risk percentage. I can modify the formula as needed.

## Multiple Nested IF & SUM Functions/Formulas

May 23, 2008

I am currently having trouble with what I thought was a simple IF statement. As you will see from the formula I want to complete a statement for every month of the year but Excel will not let me go past July. Is there a limit to the number of arguments in an IF statement and how do I overcome this? =IF(\$A\$3="Jan'08", SUM(C7),IF(\$A\$3="Feb'08",SUM(C7:D7),IF(\$A\$3="Mar'08",SUM(C7:E7),IF(\$A\$3="Apr'08",SUM(C7:F7),IF(\$A\$3="May'08",SUM(C7:G7),IF(\$A\$3="Jun'08",SUM(C7:H7),IF(\$A\$3="Jul'08",SUM(C7:I7),"n/a")))))))

## Color Cells Containing IF Functions/Formulas

Jun 4, 2008

I need all cells which containing formula IF in red colour. I have tried it with conditional formatting, but i can`t figure it out.