I m working on a database in which i need to Count the employees whose salary is based on Different ranges.I tried The Formula COUNTIF but i m not getting the accurate results.i dont know where i went wrong.I even Tried using the sumproduct or Sum (Array Formulas).
I also need to Show the same in a pivot Table.For example all the employees ranging from 0 - 5000 ,5001 - 10000 and so on.
How do i achieve this. Please tell me how to build pivot table & Pivot Chart as well as the regular countif formula.
Thanks in advance.
So here i am with another issue that has popped up with this dam project im working on. Here is the idea:
I have created queries in an access database that compare 2 sets of data, previous and current, and compares them using a function that i wrote in VBA (fComp()). Instead of writing a HUGE iif statement in access, i wrote this function to make it nice and clean. It works very well.
Now comes the second part of my project, Excel pivot tables. Because everyone that will be using the data found in the Access database wants pivot tables, i must give them pivot tables. So i create a spreadsheet for each comparison query that i have written in Access and start setting up a pivot table using the Access database as the datasource. As simple as that sounds, i get this error "Undefined function 'fComp' in expression." I know what it's telling me, but i dont know WHY it's telling me this. Since Access would be runnign the query to populate the data for the pivot table, i cannot understand why it's pooping out on me. Is there a way to pass that function from Access VBA to Excel VBA?
help.
Emanuele
I have an Access database query where I have used the NZ function (adding fields together, where some fields are blank). If I try to use this Access database query when creating a pivot table in Excel (choosing external data source), I get the following message:
Undefined function 'Nz' in expression.
Why and how can I avoid this?
So here's the situation. I've got two tables. The first is a table of legal case types - say, it looks something like:
code description
1 theft
2 assault
3 property dispute
4 child support
...
and the second is a table of cases that looks something like:
case_num date city case_type_1 case_type_2 case_type_3
1 1/1/2007 NYC 1
2 1/2/2007 Bos 2 3
3 1/4/2007 L.A. 2 4 5
4 2/2/2007 NYC 4 7 8
5 2/5/2007 Bos 7
The idea being that each case can have more than one type (e.g. a fight about a cow might be both an assault and a property dispute). I'd like to generate a report of how many cases of each type occurred during different time periods. So for this data, it would look like:
Month Case Type Count
Jan-07 1 1
Jan-07 2 2
Jan-07 3 1
Jan-07 4 1
Jan-07 5 1
Feb-07 4 1
Feb-07 7 2
Feb-07 8 1
A pivot table would work well if I had a single column for my case types (i.e. a case could only be one type), and I could use COUNTIF() to sum up the occurrances of each case type over the entire time range. But I don't know how to use a pivot table to combine multiple columns (i.e. my case_type_1, case_type_2 and case_type_3) in to a single source for the count. And I don't know how to make a COUNTIF dependant on more than one field (e.g. the type and the date).
Thoughts on how to do this? Ideally, it'd be a solution that would support slicing it on other fields in the case table (e.g. separate it by city instead of by month) depending on what people care about.
Thanks
Hello, I'm wondering if it is possible to input a COUNTIF function in calculated field in a pivot table. I'd like a field which counts the number of nonzero values for each data field in the pivot table. If what I'd like to do isn't possible using the calculated field functionality, can anyone think of another option?
Hi All,
Need your help but I dont know if what I want is possible... I'm a newbie by the way so please pardon me should I have done anything wrong by posting this query. I searched in the other sections and couldnt find anything similar to my dilemma.
Ok, so I have a worksheet where data is either YES or NO. There are other columns as well so I decided to use pivot to get the count of each column. However, when it comes to these variable data, pivot just counts everything (both YES/NO). Is there any way that the count will just show yes?
Thanks! Hope you guys can help me..
I have a pivot table that currently sums a column of 1's and 0's to give me a count.
But when you drill into the sum you get all the records and not just the 1's.
I thought of changing them to TRUE anf FALSE and using CountIf to count the TRUE's but can't figure out how to put a calculated field with CountIf in the Pivot table.
The final result I would like is to drill into and retrieve on true records.
Is this possible, or is there another method?
[Excel 2000]
I've got a table of data that has positive as well as negative numbers
I want to count the number of positive numbers, as well as the Max, Min and Avg and do the same for negative numbers
I can easily count and find the Max, Min and Avg for ALL of the records, but I need to separate that info separately for positive and negative records.
Does anyone have any ideas if this is possible?
Thank you
I have a table that I import from a web query. I want to search it (just one column really) for text that matches a list of keywords. There are 7 or so keywords that I am looking for. If any of them are present I want a TRUE indication.
Hey guys. Here's my latest that I could use some help with. Trying to figure out a formula (or nested formulas) to create a report listed below or if I should just try to use a Pivot table (assuming the pivot table can automatically update it's source data).
Column A contains a 'pillar' (5 different pillars)
Column B contains a stage number (1 - 4)
Column C contains a date
I'd like to create a report that shows the total amount of instances a stage number is shown with a pillar within a date range.
Ex report
............
Trimester 1 (1/1 - 4/30)
Pillar 1
Stage 1 10
Stage 2 7
Stage 3 3
Stage 4 0
Pillar 2
Stage 1 12
Stage 2 9
Stage 3 5
Stage 4 4
etc
............
When I think about the formula this is what I put together in my head...
Count the total stage instances of "1" if the pillar is "Pillar 1" if the date is between "1/1 & 4/30"
Count the total stage instances of "2" if the pillar is "Pillar 1" if the date is between "1/1 & 4/30"
etc
