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.
View 11 Replies
ADVERTISEMENT
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?
View 5 Replies
View Related
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"
With ActiveSheet.QueryTables.Add(Connection:="TEXT;B:BOMOEBTRfro1.txt", Destination:=Range("A1"))
[Code] ........
View 8 Replies
View Related
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.
View 3 Replies
View Related
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.
View 4 Replies
View Related
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
View 6 Replies
View Related
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
View 3 Replies
View Related
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.
View 3 Replies
View Related
Oct 14, 2003
if there is a way to display a table as column percentages but have the totals as raw numbers.
View 9 Replies
View Related
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
View 5 Replies
View Related
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
View 5 Replies
View Related
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.
View 6 Replies
View Related
Dec 25, 2012
Build table with 2 matching condition.
View 6 Replies
View Related
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)))
View 2 Replies
View Related
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.
View 7 Replies
View Related
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....
View 4 Replies
View Related
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?
View 1 Replies
View Related
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.
View 1 Replies
View Related
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] ........
View 2 Replies
View Related
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.
View 1 Replies
View Related
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.
View 1 Replies
View Related
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
View 9 Replies
View Related
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.
View 9 Replies
View Related
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:
View 9 Replies
View Related
Oct 16, 2003
Is there a code I can use to update pivot tables e.g every 10 mins?
View 9 Replies
View Related
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()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Pivot Table Data'!R1C1:R1892C7").CreatePivotTable TableDestination:="", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Product", _
ColumnFields:="Location"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales").Orientation = _
xlDataField
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub..................................
View 4 Replies
View Related
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.
View 7 Replies
View Related
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.
View 3 Replies
View Related
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.
View 4 Replies
View Related
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)
ReDim breadCrumbs(UBound(Lines)) As Variant
For i = 1 to UBound(Lines) - 1
breadCrumbs(i) = Split(Lines(i), ",")
Next i
End Sub
View 1 Replies
View Related
