Count rows based on multiple criteria
Is there a way I can COUNT based on multiple criteria? I have a feeling it can be done using an array formula but I draw a blank at them!
Columns B, C and D (rows 4 through 13) contain a person's sex, age and exam grade. I need to produce a count based on the individual conditions, i.e. all males over 18 with grades above 3.0 etc.
Does anyone know how to count the number of rows based on multiple criteria on other columns? For example I want to count the number row that meet the criteria of "Yes" in Col 2 and "Yes" in Col 3. The result would be 2 in the example below. I tried different combinations of Vlookup, Countif and Sumif and could not come up with anything that worked.
What I am trying to do is count how many outstanding rows that have not been given a Award or Voucher number and excludes any rows that have Rec only
I have attached an example data showing criteria.
In my spreadsheet I would like to delete all rows where columns D AND E are smaller than 0. Im kind of a newbie to excel VBA. I am able to delete a row based on a single criteria with this code
With Range("F1", [f65536].End(xlUp))
.AutoFilter Field:=1, Criteria1:="=#DIV/0!"
.Offset(1, 0).Resize(.Rows.Count - 1, 1).EntireRow.Delete
but I'm not able to delete rows based on a criteria for multiple columns. Im sure this isn't complicated but I wasn't able to find anything on the other threads. I have attached a sample of my file
Here's my problem. Let me explain with an example.
there are 2 columns on which I want to run a count in the foll manner :
Col 1 Col 2
Now When I Pivot this data what I see is :
B1 -- 1
B2 -- 2
B3 -- 2
It counts the no of rows for each unique combination. But What I really want is to count each combination as 1 , regardless of how many times it repeats. Basically I want the row count for A1 as 3 and on my report I want to show that A1 went through 3 B values. So it aggregates as :
A1 = 3
At present it is aggregating as A1 = 5 based on the pivot above. Please tell me how I can achieve this?
Related Applications & Scripts
Cross-browser (works in IE, Mozilla-based browsers such as Firefox, Opera 9+, and Safari 3.0)
Multiple rows selection (you can delete many rows simultaneously)
Paging (navbar to move among pages, change page size)
Tabbed order of cells in rows (you can move between cells pressing Tab)
Skinnable (change skins using API)
Resizeable columns (drag&drop column borders of the grid header)
Sortable (click on a header column to sort the grid)
Dynamic loading (automatical loading data on grid scrolling)
Frozen columns (columns with independent horizontal scrollbar)
Remote files generator (JS script to automatically build Smart Grid remote files corresponding to your table)
Advanced calendar for date cells.
Vorras Mailer enables webmasters to offer web-based email to their visitors. Similar to Hotmail or Yahoo!Mail, Vorras Mailer supports multiple users, and new user registration. It can also be configured to read remote email, allowing users to consolidate their various email accounts into one location. The remote email system permits users to access their mail securely from any online connection worldwide.
AutoShrink is a special PHP Class that enables you to automatically shrink multiple links in a text with a specific url shrinking website. AutoShrink contains so far 15 different websites to shrink with. AutoShrink also lets you to add new websites to the script easy as possible!.
Adfly, Bitly, Googl, Isgd, Jmp, Snim, Snipr, SnipURL, Snurl, TinyURL, Xrlus, Twirl, Kattit, MeeLa, IamFlyin
This class wraps common MySQL database table SQL queries. It can:
- Perform SELECT queries and retrieve a single value, a single row into an array and all rows into a multidimensional array.
- Insert or update rows from a list of fields and values
- Delete table rows that satisfy a condition
- Count records in a given table
- Check if a table record exists with a given field value
- Perform a database search
- Return a certain number of rows of result from database, et
This package can be used to create Web based user interfaces for configuring sets of properties. It can compose and generate HTML forms with fields that appear split between multiple pages. The form pages may be browsed using a tab or wizard like user interface. The form may be composed adding several types of fields to each form page.