# COUNT On Three Criteria, But Using No Arrays Or Pivot Tables

May 17, 2009

I have a table which, when populated, gets huge. The example I am showing here is greatly simplified.

I need to be able to provide a summary report that shows a count of entries (rows) that meet either TWO or THREE criteria, and one of those criteria is date-based.

For the date-based option, I need two possibilities: 6 months ago, and 12 months ago.

In the attached example, then, I would need to see:

- How many entries were for Purchasing and were Complaints in the past 6 months? (answer should = 2)

- How many entries were for Shipping and were Complaints in the past 12 months (answer should = 2)

- How many entries were for Shipping and were Compliments in the past 12 months (answer should = 1)

And so forth.

Here's the rub. All of this can be done with array formulas, and an EDATE cell that calculates today's date -6 months, and one -12 months. The problem there is that there would be so many array formulas (I need to do a lot of permutations), plus utilizing the volatile TODAY(), that the sheet slows down DRAMATICALLY in calculation time.

Secondly, this is for a user that is not Excel savvy. So the solution not only should not slow the calculations down, or require them to set Excel to manual calculation, but it should also NOT use Pivot Tables. (Simple users are confused by pivot tables, sorry to say... and they are a heck of a thing to format nicely for the boss to look at.)

And, of course, we can't use VBA.... that will also confuse the client.

So I know the best answer lies in database functions. I just cannot figure it out.

## Finding Missing Data Using Arrays And 2 Tables

Aug 25, 2014

Any easy way to retrieve data from another table using an array formula.

I have two files that I am using that contain 2 sets of data with columns for name, address, city, and state. The red highlighted data needs be used to find the blue data first column, which is a possible name for the company found from the red data. The issue is that the blue data is larger and has rows of data that will not be found in the other table.

I have the spreadsheet attached. I attempted to use an if/and statement :
=IF(AND(D2=C8I:I,E2=J:J),H2,0)
but found out that it does not work with arrays and only found the first address by default.

How would I set up a formula to retrieve the possible names using criteria from the blue data such as if the address and city match, then input that company name?

## Query Tables DataTypes And ColumnWidths Arrays As Variables

Jul 12, 2011

How do I set the datatypes and columnwidths as variables? I get invalid procedure call or argument. Will import different text files and set dt and cw based on names.

Dim dt As Variant
Dim cw As Variant
dt = "2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2"
cw = "7, 7, 5, 2, 7, 16, 3, 15, 11, 9, 9, 9, 7, 3, 3"

[Code] ........

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

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

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

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

## SUMIFS Using Arrays As Criteria?

Feb 28, 2014

The below formula is working fine:

=SUM(SUMIFS(YTD_GBP,Region,"Central Budgets",Cost_Desc,"Hardware",Non_Billable_Project,{"Non Billable","Central Budget"}))/1000

but if I try to add in a second criteria for Region, as below:

=SUM(SUMIFS(YTD_GBP,Region,{"Central Budgets","Corporate Services"},Cost_Desc,"Hardware",Non_Billable_Project,{"Non Billable","Central Budget"}))/1000

I get zeros.

But if I do two separate formulas and add them:

=(SUM(SUMIFS(YTD_GBP,Region,"Central Budgets",Cost_Desc,"Hardware",Non_Billable_Project,{"Non Billable","Central Budget"}))+SUM(SUMIFS(YTD_GBP,Region,"Corporate Services",Cost_Desc,"Hardware",Non_Billable_Project,{"Non Billable","Central Budget"})))/1000

## Count When Values Occur In Different Arrays

Feb 26, 2009

My difficulty is this. I have 2 columns, A and B. A contains only 0's and 1's. B contains any number from 1 to 100. I want to count all the instances of any given number in Col B that are matched with a 1 in the corresponding cell in Col A.

## Count Two Arrays With Match Condition

Dec 25, 2012

Build table with 2 matching condition.

## Match Multiple Criteria From Different Arrays

Apr 19, 2006

I'm trying to create a template that will be able to return a sales persons call target based on the category they sell and the current level they are. It works when I only ask it to look for one or the other, but I'm getting stuck trying to make it use both. I've attached a small sample. My original equation is as follows

=INDEX(\$C\$2:\$Q\$51,MATCH(\$B2,\$A\$2:\$A\$51,0),MATCH(G\$1,\$C\$1:\$Q\$1,0))

and this works perfectly fine. The problem I've now got is I need to add in a second criteria for setting the row_num. I've tried the following but I get a ref error. =INDEX(\$C\$2:\$Q\$51,AND(MATCH(\$B3,\$A\$2:\$A\$51,0),MATCH(D3,\$B\$2:\$B\$51,0),MATCH(G\$1,Telesales!\$C\$1:\$Q\$1,0)))

## Conditions / Criteria Based On Arrays With Different Formulas?

Dec 16, 2013

I'm having trouble with doing a similar thing with conditions/criteria based on arrays for different formulas (e.g. IF, COUNTIFS, SUMIFS).

Here's what i'm trying to condition and than use different formulas:

column A - Region
1. Europe
2. Europe
3. Asia
4. Asia
5. America
6. Asia
7. America
8. America
9. Europe
10. America

[Code] ....

QUESTIONS

1. (IF, AND ?)
For all cases listed in column A that have "university" in the column B, If there is at least one case having "Europe" write 1, if there isn't write 0.

The conditions are "Europe" in column A and "university" in column B.
If there are cases like that, I want formula to produce 1.
If there are no cases like that, I want formula to produce 0.

* In this example the formula would produce 1. for "Europe" and "university".
* If the formula was looking for "Oceania" in the column A with "university" in column B it would produce 0.

2. Using COUNTIF with multiple criteria.
Count all of the cases that have "Europe" in column A and "university" in the column B
The conditions are "Europe" in column A and "university" in column B.
If there are cases like that, I want formula to do count.
* In this example the formula would produce 2 for "Europe" and "university".
* If the formula was counting "Oceania" in the column A with "university" in column B it would produce 0.

3. Using COUNTIFS with multiple criteria.
Count all of the cases that have "Europe" in column A, "university" in the column B and "GER" in column C

The conditions are "Europe" in column A, "university" in column B and "GER" in column C.
* In this example the formula would produce 2 for the 3 conditions.
* If the formula was counting "Oceania" with "university" and "GER" it would produce 0.

I've spent a lot of time searching the Internet for this (e.g.how to use IF with an array, about different functions and formulas...) and found nothing that works.

For COUNTIF(S) they just didn't work, event with ctrl+shift+enter.

## Pivot Tables With Graphs

Mar 13, 2014

I have a 8 pivottables with a graph for each. is there a way that i can have a dropdown menu to select which graph to display instead of having all these graphs everywhere....

## Printing Pivot Tables

Mar 21, 2014

I have an issue with printing a worksheet with a pivot table. When refreshing the table, depending on the filter choices, the table length with expand and contract. When the pivot table contracts, it leaves a light blue shaded area. If you try to print the worksheet it includes the blue section. Is there a print macro that can be written that will only print the sections with values or perhaps a print setting that would exclude the shaded area?

## Pivot Tables Do Not Display?

Oct 27, 2011

When in a worksheet and clicking in the pivot table results, the pivot tables no longer display.

I did have a reinstall of Office last week but can't see how that would have impacted this. Otherwise, I only use the pivot table command to 'refresh all'.

I know very little about them and didn't create this workbook.

I do add entries to the source data and have tried to change source data but I get Reference is not valid.

## Row Labels In Pivot Tables?

Feb 14, 2012

When I run pivot tables, the row label descriptions are only displayed at the highest level for each category. As displayed below the highest level on the row label is MOB and is only detailed in the first row. I need for each of the different row label levels to display the data so there are no blank cells.

MOBMan. Boxset 2Large1328Manual StickeringN/A500Shrwp 13+Large16680Small596Shrwrp 7-12Large35704Small2411SW 1-6Large87912Small14635SW w/ PremLarge26966OUTDisplay Lvl 1Small100Klutz Standard Display 6-packN/A2499Man. Boxset 1Large40000Man.

[Code] ........

## Pivot Tables Within Excel?

May 15, 2014

All I want to do is write some vba script that inserts a pivot table based on all of the data in my current workbook.

Therefore I want something incredibly simple if it exists like:

ActiveWorkbook.PivotCaches.Create(SourceData:= ActiveWorksheet.Select.AllData)

ie the vba code that just takes the data you've got in your current worksheet and creates a pivot table.

## Formatting Pivot Tables With VBA

May 27, 2014

I have four sheets with a lot of pivot tables on each and my problem is that whenever I hit Refresh the formatting of the pivot tables is always changing. It seems that this is something of an issue in Excel 2010 and that they only way to truly get around it is to re-format the pivot tables upon refresh with VBA code.

Any resource or thread I can use to learn the commands to format the pivot tables?

I am getting better with VBA code but seems to always get stuck on trying new things because I do not know the commands.

## Two Pivot Tables With Combo Box

Mar 23, 2007

I'm trying to sync a common field in two different pivot tables.

I have 2 pivot tables on a single worksheet.
Each table has its own data source - 2 different databases (had to set it up that way to present all the data requested).

The 2 data sources have one column of data in common. This column is called Projects and all project names are the same in both databases.

BUT...

Pivot Table 1: Projects is in the ROW area (multi-select dropdown)

Pivot Table 2: Projects is in the PAGE area (single select drop-down)

I would like to link the Projects data items so that when I select a project name in the Combo box, the same project name would seamlessly be selected in both tables.

Each table would populate with its own data based on the project selected.

Basically, I'd like to use the method illustrated in this Flash file:
[url]

This method would have worked beautifully if it weren't for this reason:

Table 1: Project data is in the PAGE field
(single selection)

Table 2: Project data is in the ROW field (multi-selection)

If the Projects data was in the PAGE field in both tables, my code would look like this:

ActiveSheet.PivotTables("Table 1").PivotFields("Project").CurrentPage = _ActiveSheet.Range("X1").Value

ActiveSheet.PivotTables("Table 2").PivotFields("Project").CurrentPage = _ActiveSheet.Range("X1").Value

But no, because the ROW field is a multi-select one, I get this kind of

Dec 14, 2007

I have several pivot tables that need to be re-linked to an Access database (really change the link to a different month's data). Currently, I do this manually, but I was hoping to write a macro to do this. Data is saved by month so I can't just create a "current" file as to not change the links each time. I can only get code to work for the refresh portion.

## VBA - Pivot Tables - Various Workbooks

Jun 5, 2009

I have used the macro recorder to create a macro that creates a pivot table from data located on another workbook.

with this said i have two questions.

How can i make it so that the workbook containing the data for the pivot tabel does not need to be open?

the second question is why am i gettign stuck with a macro that sticks right here:

## Pivot Tables: VBA Refreshing

Oct 16, 2003

Is there a code I can use to update pivot tables e.g every 10 mins?

## Working With Pivot Tables Using VBA

May 2, 2006

I am working with VBA to create a pivot table, and have done just fine so far. However, I need to create a two buttons that will run the following macros: 1. A macro that will remove the selected header (either row or column) from the pivot table.

2. A macro that will put the removed header back into the pivot table.

I need to be able to click on the header (whether its the row or column header) and then press the button to remove it from the table. The second button should then add that header back into the table. My code for creating the table is fine, I just need to work out the buttons. Below is what I have so far. I was think that if I had variable for the header name it would be able to tell which header to remove (so I used Set iField = ActiveCell.Value), I also tried ActiveCell.Text. All I need to do is put the text in the selected field into the PivotFields range to make it hidden. However, I keep getting an error (Compile Error: Object Required) on the line Set iField = ActiveCell.Value. Below is my current code.

Sub PivotTable()
"'Pivot Table Data'!R1C1:R1892C7").CreatePivotTable TableDestination:="", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ColumnFields:="Location"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales").Orientation = _
xlDataField
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub..................................

## Calculations Between Two Pivot Tables

Aug 3, 2006

I wonder if it is possible to make calculations between two pivot tables which have the same number of columns and rows and put the outcomes in a third pivot. For example Pivot 1 contains revenue data, Pivot 2 contains cost data and Pivot 3 provides the calculated outcome of revenues minus costs.

## Compare Pivot Tables

Aug 9, 2007

Is it possible for me to compare two pivot tables and get a report. in the attached example, is it possible to compare sheet1 and sheet 2, if the values haven't changed, then it should be green, or it should be red.

## Sumifs Vs Pivot Tables

Feb 14, 2008

I read in your "best Practice..." sticked above that it's better to use Pivot tables then multiplied criteria SUMPRODUCTs (or array formulas). i agreed with it because i found it took long time to recalculate the sheet. do you suggest it refering the new SUMIFS formula?. i found that a SUMIFS that refers to Tables can be replacement for GETPIVOTDATA. In my sheet there are several dozens of GETPIVOTDATA.

## Slicing And Dicing CSV Files - Involves Arrays And Jagged Arrays

May 8, 2013

I am retrieving a CSV file from the net. In this file there are 'x' amount of row data and 7 columns. I only care about the values in the 7th column for each row. I also don't care about the entire first row. A graphical version would be represented something like this, with the values I want colored in orange:

|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|

.
. extending until the end of the data set
.

I've managed to dice this thing into a jagged array by first splitting it using vbLf as a delimiter, and therefore adding those to an array called Lines(). Then I split Lines() up using commas as the delimiter and threw those into a jagged array, let's call it Breadcrumbs()(). I want to throw all the values from Breadcrumbs(i)(6) into an array of its own. Here's my code so far:

Code:
Public Sub CSVparser(file As String)
Dim Lines As Variant
Dim j As Integer
Lines = Split(file, vbLf)