#### Criteria/CountIf and Pivot Table

```Hello:

My data looks like this

ID                   Date                      CWA
Amount
1                    08/12/2006             0
\$0.00
2                    08/13/2006             1
\$10.00
3                    08/14/2006             1
\$20.00
4                    08/01/2006              0
\$0.00

Based on the above data, I want to create a pivot, by month (I know I
only have Aug here)
to show the following:

Sum of CWA
Count of CWA
Percent of cases that were submitted with CWA.

The CWA is an indicator field that looks at another field (not listed
here) to determine if there is cash in a column.  If yes, 1, if not 0.

So my final pviot should look like this:

Jan                      Total Cases Submitted: 4
Total Cases Submitted With CWA:
Percent Submitted With CWA : 50%
Sum Of CWA : \$30.00

Thanks,
Brian

```
 0
bdaoust (13)
8/14/2006 10:57:32 PM
excel 39879 articles. 2 followers.

3 Replies
869 Views

Similar Articles

[PageSpeed] 57

```Add a column to the source data, with the heading CWACount
Enter a 1 in each row, for that field
Add that field to the pivot table
Create a calculated field which divides CWA by CWACount

bdaoust@yahoo.com wrote:
> Hello:
>
> My data looks like this
>
> ID                   Date                      CWA
> Amount
> 1                    08/12/2006             0
> \$0.00
> 2                    08/13/2006             1
> \$10.00
> 3                    08/14/2006             1
> \$20.00
> 4                    08/01/2006              0
> \$0.00
>
> Based on the above data, I want to create a pivot, by month (I know I
> only have Aug here)
> to show the following:
>
> Sum of CWA
> Count of CWA
> Percent of cases that were submitted with CWA.
>
> The CWA is an indicator field that looks at another field (not listed
> here) to determine if there is cash in a column.  If yes, 1, if not 0.
>
> So my final pviot should look like this:
>
> Jan                      Total Cases Submitted: 4
>                            Total Cases Submitted With CWA:
>                            Percent Submitted With CWA : 50%
>                            Sum Of CWA : \$30.00
>
> Thanks,
> Brian
>

--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

```
 0
dsd1 (5911)
8/14/2006 11:17:36 PM
```Perfect!

Thanks,
Brian

Debra Dalgleish wrote:
> Add a column to the source data, with the heading CWACount
> Enter a 1 in each row, for that field
> Add that field to the pivot table
> Create a calculated field which divides CWA by CWACount
>
> bdaoust@yahoo.com wrote:
> > Hello:
> >
> > My data looks like this
> >
> > ID                   Date                      CWA
> > Amount
> > 1                    08/12/2006             0
> > \$0.00
> > 2                    08/13/2006             1
> > \$10.00
> > 3                    08/14/2006             1
> > \$20.00
> > 4                    08/01/2006              0
> > \$0.00
> >
> > Based on the above data, I want to create a pivot, by month (I know I
> > only have Aug here)
> > to show the following:
> >
> > Sum of CWA
> > Count of CWA
> > Percent of cases that were submitted with CWA.
> >
> > The CWA is an indicator field that looks at another field (not listed
> > here) to determine if there is cash in a column.  If yes, 1, if not 0.
> >
> > So my final pviot should look like this:
> >
> > Jan                      Total Cases Submitted: 4
> >                            Total Cases Submitted With CWA:
> >                            Percent Submitted With CWA : 50%
> >                            Sum Of CWA : \$30.00
> >
> > Thanks,
> > Brian
> >
>
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html

```
 0
bdaoust (13)
8/15/2006 12:46:58 AM
```You're welcome! And thanks, I hope you like the book.

bdaoust@yahoo.com wrote:
> Perfect!
>
>
> Thanks,
> Brian
>
> Debra Dalgleish wrote:
>
>>Enter a 1 in each row, for that field
>>Add that field to the pivot table
>>Create a calculated field which divides CWA by CWACount
>>
>>bdaoust@yahoo.com wrote:
>>
>>>Hello:
>>>
>>>My data looks like this
>>>
>>>ID                   Date                      CWA
>>>Amount
>>>1                    08/12/2006             0
>>>\$0.00
>>>2                    08/13/2006             1
>>>\$10.00
>>>3                    08/14/2006             1
>>>\$20.00
>>>4                    08/01/2006              0
>>>\$0.00
>>>
>>>Based on the above data, I want to create a pivot, by month (I know I
>>>only have Aug here)
>>>to show the following:
>>>
>>>Sum of CWA
>>>Count of CWA
>>>Percent of cases that were submitted with CWA.
>>>
>>>The CWA is an indicator field that looks at another field (not listed
>>>here) to determine if there is cash in a column.  If yes, 1, if not 0.
>>>
>>>So my final pviot should look like this:
>>>
>>>Jan                      Total Cases Submitted: 4
>>>                           Total Cases Submitted With CWA:
>>>                           Percent Submitted With CWA : 50%
>>>                           Sum Of CWA : \$30.00
>>>
>>>Thanks,
>>>Brian
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Contextures
>>http://www.contextures.com/tiptech.html
>
>

--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

```
 0
dsd1 (5911)
8/15/2006 1:03:47 AM

Similar Artilces:

Table Borders on IE Zoom
I have and ASP.NET 3.5 site that is displaying white lines for table cell borders, only when zoom is set to other than 100% in IE7. No problem with IE8 and Firefox. Does anyone have a suggestion on how to eliminate this. The site is http://www.pb101.com. Thanks for any suggestions. -- AG Email: npATadhdataDOTcom Hi AG, I have tested the page in both Internet Explorer 7 and Internet Explorer 8 and when zooming the page, I repro the issue you said. As I tried to add the CSS style "body{zoom: 200%}" into the page, I find it works well with 200% zo...

Subtract colums in pivot table
I have a pivot table that has the following characteristics (Excel 2007): -rows (down the left) are values: "# Employees", "Total Pay" -Columns (across top) are Dates I want to calculate the difference between different date columns. Example: I have: Date 5/23/2010 5/16/2010 5/24/2009 Total # Emp 10 15 5 30 Pay 1000 15000 500 16500 I'd like: Date 5/23/2010 5/16/2010 5/24/2009 Total Vs. Last Wk % Change Vs. Last Yr % Change # Emp 10 15 5 30 -5 -33% 5 33% Pay 1000 5000 250 6250 -4000 -80% 750 15% Can anyone tell me how to cre...

Multiple criteria #3
I was looking for help with the following code. I would like to count the agents hired after 2002 and were hired directly Dim hiretype As Text Dim agntct As Integer Dim dbs As Database, rst As Recordset hiretype = "Direct" agntct = DCount("[Agent Hire Date]", "tblAgentdetail", "[Hire Type]= '" & hiretype & "' and [Hire Date]> #12/31/2002# ") ' Return reference to current database.D Set dbs = CurrentDb ' Open table-type Recordset object. Set rst = dbs.OpenRecordset("TestAG") rst.AddNe...

Update table with data from rows with previous dates from specifie
I need to update a table where if a row contains a specified stat value I need to change a date value to the date value of a row with the same subID but a different stat value. My primary question would be if I need a cursor to perform this operation or if it can be done without a cursor (Sql Server 2000 Tsql please). In the sample data below if a stat value is in ('A', 'E', 'U') I need to update that row - I need to update the date2 column with a date1 value from the closest row with the same subID but the stat is in ('N', 'R') and ...

computing formula according to criteria #3
Thanks, That's a good idea, the problem is I have several participants, and need a template sheet where I can just paste each participant's data and the means and SD's will be computed automatically. At the momen going over each and every participant is taking me hours. I really need a formula... -- lior ----------------------------------------------------------------------- liory's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1049 View this thread: http://www.excelforum.com/showthread.php?threadid=26138 another way to do it would be to write a ...

Adding a field from another table on a form
I'm trying to add a field to my master form from another table. I created a relationship between the master table and the other table, but even after I've updated the control source to = [table]![field] all of the results are showing as "#Name?" . I know I'm doing something silly, please help me. To add a field it needs to be in the report's Record Source. Use a query rather than a table. However, a query based on related tables may not be updatable. You have provided few details, so it is difficult to be specific, but typically a subform is used t...

DMAX with Criteria
Is there a way to add criteria to the expression that creates my serial numbers? Me![TxID] = Format(DMax("[TxID]", "[tblMyTable]") + 1, "0000") I want the next number in the series to be based on a category field on the form. which will be matched to a ctegory field in MyTable. In other words: The user allocates the record to a Category and clicks a button to return the next available number in the series for that category. This will result in duplicate numbers in the Seriel Number Field, but this is not my Primary Key, (I am using an Autonumbe...

Basic Pivot Table Questions
Two basic questions: In my pivot table I click on the "Data" box to get a pop-up window of the possible fields I can filter. By default "show all" is checked. If I go through and uncheck some fields, hit "OK", only those fields are displayed. When I click the "Data" box again, only the fields that I filtered to appear, and even when I hit "Show all", I cant seem to get back that full original list. How do I do this? Also, is there an easy way to add a customized "group" to this list? For example if I have 30 fields, and regula...

like pivot table, with details?
Hi all, is there any way to do a "pivot table" that instead of summing (or averaging, multiplying, etc) the data fields, actually presents the data field values? I have a table that lists our purchased components part number, supplier, customer, and program, i.e.: P/N Supplier Customer Program 123B Jim GM Truck 123A Jim GM Truck 2413 Bob Ford Car 5231 Jim Ford Car 6789 Tom GM Truck and I want to display it in a pivot table like...

AND on 2 fields along with other criteria in query
Hello New to Access and trying to figure out a problem with an AND test I have a database with 4 fields called A B C D Field A Must =1 (no problem with this) Field D Must > 1 (Again no problem) Fields B & C can be any number including zero, as long as BOTH fields are not zero. If I search using the criteria for fields A & D only, I find 390 records. If I search with criteria that says show me if both are zero, (B=0, C=0 on the criteria line, along with the other criteria) it shows me the 4 or so suspect records that have a zero in BOTH fields. If I use the opposite, and...

GP Extender Tables
In Extender, which database table holds the data for the drop down list type fields? Thanks! ------=_NextPart_0001_29018BE3 Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi There Try for the setup table select * from EXT40152 The actual data, it will be stored as a number relating to the Line Item Sequence in the Setup table. select * from EXT00183 Hope this helps David Musgrave [MSFT] Escalation Engineer - Microsoft Dynamics GP Microsoft Dynamics Support - Asia Pacific Microsoft Dynamics (formerly Microsoft Business Solutions) http://www.microsoft.com/Dynamics mailto:Da...

Pivot Table Source file
I have been doing VBA in Excel for years, but have never done anything with Pivot Tables. I was given a spreadsheet that has 4 pivot tables which grab data from our main system (AS/400). How do I find what files are being used when the P/T's are refreshed? Thanks for any help. -Paul B. You may be able to select a PT, then show the PT wizard. Then click on the Back button to see the settings. This may not apply to you, but when I did stuff like this, I'd have a text file created from the AS/400. Then I'd import that data into excel and do all the manipulation from that impor...

Excel 2007 Pivot Table Formatting Refresh
I know this has been asked everywhere, but there does not seem to be a defentive answer. Can you help? 1. I have a Pivot Table with formatting (Fills, Borders, Number Format). I also have specific row heights. When I refresh, the row heights and SOME cell fills are lost. 2. How can you hide "(Blank)" columns? ...

Pivot Chart Total Columns
Hi, I have a standard pivot table/chart combination displaying project costing&budget data for several projects. I would like to plot in the chart the sum of budgets and/or costs over different combinations of projects. But I only succeed in charting the individual entries per project. Any advice? Thanks, Hans - You've discovered that pivot charts aren't very smart. They chart all the individual data in a pivot chart, and none of the totals or subtotals. Have you tried grouping the data, or perhaps taking out fields which subdivide other fields, for example, the quar...

How to perform a COUNTIF on multiple selections?
I'm trying to perform a countif on two different ranges of cells but am having difficulty figuring out how to get past the first range. This is what I have that does work... =COUNTIF(Sheet1!A13:AA39,"*David*") I've tried nesting the ranges with parantheses a couple of different ways but it didn't work. What would the proper expression be to add another range of cells like below? =COUNTIF(Sheet1!A13:AA39,"*David*")+(Sheet1!A57:AA53,"*David*") Thanks, David =COUNTIF(Sheet1!A13:AA39,"*David*")+COUNTIF(Sheet1!A57:AA53,"*David*&...

like a Pivot Table
Hello every body If any one can help me I'm working with data which most of it comes like a table with feilds as columns and records as rows. I want it to be as many rows with each feild an example what is existname age Joining Date Tele John 20 Jun-90 4321251 Iqbal 30 Jul-95 6583752 George 40 Sep-85 7843125 What I want John age 20 John Joining Date Jun-90 John Tele 4321251 Iqbal age 30 Iqbal Joining Date Jul-95 Iqbal Tele 6583752 George age 40 George Joining Date Sep-85 George Tele 7843125...

Create a list with multiple criteria
I have a data base with 1,000's of addresses w/zipcodes (Sheet 1 columns A to E, E being zipcodes). After setting a base address I get all of the zipcodes within a given radius (in this case 70 zipcodes), listed in Sheet 2, Column A. Next, I use COUNTIF (in column B) to find how many addresses are in each zipcode, in this example there are 46 addresses within the 70 zipcodes. What I want to do is create a list in sheet three that will list each with the data from Sheet 1 columns A to E). Any help would be appreciated. Ronbo ...

Countif help needed #2
Help Please!!! I have 2 colums, one which shows sales people the other which shows Dollar amount. Can I formulate a cell to ask wherever you see Sales Person A then copy the adjecent cell throughout a range or column? Will "Sales Person A" be entered more then once in the column? If so, would you want *individual* displays of each dollar amount occurrence, or would you like a single, *total*, dollar amount for the salesperson? -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may ben...

Pivot tables #21
Hi, When creating Pivot tables is there a way to suppress all data that has zero grand total values (if summarising using SUM). Thx, Don- Goto tools options and uncheck the zeros option. John "Don Niall" <donniall@aol.com> wrote in message news:070701c46e4b\$5cfeedf0\$a301280a@phx.gbl... > Hi, > > When creating Pivot tables is there a way to suppress all > data that has zero grand total values (if summarising > using SUM). > > Thx, > > Don- John, Thanks for the reply. I was hoping it might remove the entire pivot-table record for those gra...

Search folder criteria
I am using Outlook 2007. How can I customise my search folder to filter messages that contain "KSC" in subject OR belong to "Sports" category? Is there a reason why "KSC" cannot not be assigned "Sports" catagory? Melissa wrote: > I am using Outlook 2007. > How can I customise my search folder to filter messages that contain "KSC" > in subject OR belong to "Sports" category? ...

IIF function for 3 criteria, possible?
Hello, I am trying to do a query where I need a result based on 3 criteria, but not sure how to do it. in written term would be like this for a new fields (column in query). 1) If OprStat = 1 and StartDate < today date, return "LateN" 2) If OprStat = 3 and StartDate < today date, return "LateM" 3) If does not fall into (1) & (2) condition, return "OnTime" Sample Data: Order OprStat StartDate 1110 1 4/1/2010 1111 1 4/12/2010 1112 3 4/2/2010 Results: assuming current...

Calc staff vacation time based on more than 1 criteria
I would like to calculate on a daily basis, accumulated vacation time based on the following criteria: Total daily hours (work and leave taken) Hours must fall on a week day Entitlement hours from another spreadsheet that are based on employee seniority and regular shift hours. 'Sheet1' in my 'Book1.xls' looks like this: A2:A372 Day of the week (starting with Sunday in A2) B2:B372 Date beginning with April 1, 2010 C2:C372 Explanation (if necessary) D2:D372 Work day 1 = it's a workday, blank = not a workday E2:E372 Paid Daily Total F2:F372 Worked hours ...

Table Containing Vendor ETF Info
Does anyone know what table contains the Vendors ETF Info (routing number, account number, etc)? I looked up the window "Vendor EFT Bank Maintenance" and see that it has the table "AddressEFT" associated with it, but I can't find the physical name for it. I am using GP 10. Thanks, Jason I found it SY06000 On Aug 27, 2:14=A0pm, Jason <webberl...@gmail.com> wrote: > Does anyone know what table contains the Vendors ETF Info (routing > number, account number, etc)? =A0I looked up the window "Vendor EFT Bank > Maintenance" and see that it has t...

Connecting Text Box in Query Criteria
I have created a Select query for retrieving some particular Date Records from a table using the BETWEEN expression in query criteria bymentioning the From Date and To Date and it’s working fine. Each time I don’t want to open the query and enter the From Date and To Date, so I want to create TWO Unbound text box in Forms for entering the From Date and To Date and nearby the text box I am having a command button to run the query. Now I want to know how I can connect the Two Unbound Text Boxes that is From Date and TO_DATE which is created in Forms in the Select Query (BETW...

Pivot Table Show Data AS
Hi there, I am trying to create a calculated field in a Pivot Table that automatically calculated the difference between two columns. I am using "Difference From" but when I choose the base field (as the item to subtract from) I also need to choose a base item? I do not want to subtract from one item but rather a base field only? Also when I choose any base item it returns as NA#? Any assiatnce would be greatly appreciated. Regards Mick To compare one field to another, you can use a calculated field -- From the PivotTable toolbar, choose PivotTable>Formulas>Calc...