Countif Function For Counting Values Based On More Then 1 Variable
May 7, 2007
I'm trying to make a spreadsheet that will count the number of times a certain incident occurs, for a particular person, for a particular month. The attached spreadsheet is an example of what I need done.
For the attached spreadsheet, I need to find out how many times x employee has been late for x month, and how many times they've been late overall.
You can see one of the many tries I've attempted in the second sheet, but it doesn't seem to want to work. I have to be able to do this without VBA, because of signature issues.
View 9 Replies
ADVERTISEMENT
Dec 15, 2009
I'm having difficulty trying to write a formula for the following type of data:
[data] ....
This is a data set with 10k+ lines; I have about 150 different items in the "Criteria" column. What I want to do is count how many unique items occur in "Column to be counted" by each vairable in the "Criteria" column. The output should look like this: ...
View 9 Replies
View Related
May 19, 2014
Is it possible to use a variable in Countif?
See attached sheet. When a number repeats for the 3rd time, I need to reset the
Countif function.
For example;
Countif($A$5:A20,A20).
I need to change the $A$5 to the current row number so we can start the count again. I need to do this several times during the sheet.
We are counting numbers until they repeat for a 3rd time and then we start the count again at that point.
View 1 Replies
View Related
Aug 6, 2009
I noticed that when I use countif to count cells with certain text value it works but up to some point when it returns way too much then (when there are generally more values matching I think). I don't know what is the cause ..formatting? some function limit ?
View 3 Replies
View Related
Mar 21, 2014
I am trying to implement a count on a table that splits up the number of customers associated with a list of managers. Sample data attached.
Count function.xlsx
So, I need some way to display a single instance of each manager name with a count of how many customers are associated with that manager. The number of managers and the number of customers associated with each manager does vary and are displayed in separate columns per the attached.
View 14 Replies
View Related
Apr 24, 2014
I run a report that dumps from data from an ERP system. I've attached a sample of this report in excel that has two sheets. A summary and the data.
I am able to report on the total number of Purchase order lines and total purchase orders using various formulas. I am also able to use formulas to report on the number of lines associated with each product in the I column.
However I cannot, how to calculate the number of unique Po# (B Column) numbers based on the Product (I Column) .....
View 14 Replies
View Related
Feb 16, 2006
I have a column of data with 1000 rows. In this column there are duplicated values. I would like to be able to use a function count how many unique values are in the column. Let's say there were 4 duplicates of 250 values in the column. I would like to be able to write a function to calculate the 250. I am very familiar with Excel and am able to acheive the number through subtotals and/or pivot tables. I know I can find how many instances one specific value appears in the column through sumproduct and/or countif statements,
View 14 Replies
View Related
Jan 31, 2013
what function can I use to count the number of unique values in a range of cells?
View 4 Replies
View Related
Feb 2, 2009
Is it possible to use a "countif" function as part of a macro?
Here's the situation. In workbook "A", I want to look at a range of cells ("O19:O248") and count the number of times "apple" appears within that range. If "apple" appears 20 times, I would like the variable v_applecount to = 20 so that I can call that variable in workbook "B"
Note, I essentially want to do something like this in the Macro:
v_applecount = CountIf(O19:O248, "Apple")
View 5 Replies
View Related
Jan 19, 2013
I'm stuck on the final piece of my macro project. I've attached a workbook with two sheets: sheet 1 is what I currently have, and sheet 2 is what I'd like my report to look like when complete.
On Sheet 1:
- column B is called Supervisor Name
- Columns D-O are months of the year, with either a Yes or No in each cell.
I'd like to write a macro that will:
- Take all unique values in Supervisor Name column, and paste these Above the current table.
- For each Supervisor, and each month, I'd like it to calculate, as a %, the number of Yes mentions in each month divided by the total cells (Yes/(Yes+No)).
Sheet 2 contains the output, in the format I'd like to see it.
Sheet 2:
Supervisor Name
October
November
December
Supervisor 1
8.3%
[Code] .......
View 7 Replies
View Related
Aug 2, 2014
I am using Office/Excel 2007 and Windows 8
I want to determine the number of values SUMIF/SUMPRODUCT functions used/checked to arrive at the answer, i.e. 95 for row 2 and 116 for row 3. A function that can give a 3 and 2 respectively.
Example:
Results for Row #2 gives: 95 with 3 values picked up under "Rate"
Results for Row #3 gives: 116 with 2 values though three places were checked up under "Rate"
How can I count the number of these values referencing at the "Rate" like in the functions used.
I managed to use SUMIF/SUMPRODUCT functions to calculate totals based on "Rate" in row one.
I tried COUNTIF function, but allows only field/heading.
A
B
C
D
E
F
G
[Code] ..........
results for each formula used
95 formula: =SUMIF(B$1:G$1,B$1,B2:G2)
95 formula: =SUMPRODUCT((B$1:G$1=B$1)*(B2:G2))
3 formula: =COUNTIF(B1:G2,B1)
View 9 Replies
View Related
Apr 18, 2013
I am trying to count multiple occurences.
Instrument Name
Times Traded
AEON CO LTD
6
ASICS CORP
3
ELECTRIC POWER DEVELOPMENT C
10
In column C (Times Traded) I have used a simple countif formula: =COUNTIF($B$2:$B$8068, C2)
Now I have to check for how many times this instrument was bought or sold. This is in column A .... How will I count the Buy values ... cause this formula I devised doesnt work =COUNTIF($B$2:$B$8068, and(C2, A2:A8068="Buy")
View 4 Replies
View Related
Jul 25, 2013
I have a column of data (say in cells J1 to J20) such as
AB*
CD+
EF&
etc etc - not every cell has an entry.
In another column elsewhere, each of these items has a number associated with it, e.g.
AB 3
CD 3
EF 4
Note that the symbol (*,+,&) is not in the table.
What I want to do is this: For each value in column J, I want to lookup it's corresponding value in the table and add 1 to a count if the value is say 4 (so in the example the cell containing my result would be 1 because only EF has a value of 4). If I wanted a value of 3 my count would be 2 (because AB and CD both have a value of 2).
I think I need a combination of countifs, vloopkup and left (as I need to strip off the number in row J somehow) but I can't get the syntax right (left for example won't work on a range).
View 1 Replies
View Related
Aug 11, 2007
Is it possible to count unique values in one column based on that column and another column? I have a report that has a column of dates (B:B), column of month and year (C:C), and a column of RMA numbers(A:A). The RMA numbers repeat if there is more than one part on the RMA. I would like to count the number of RMA created each month. Below should be Jan = 2; Feb = 2; Mar = 1............
I have a CSE formula that allows me to count based on two criteria but it does not count unique values and you have to specify the two criteria.
View 4 Replies
View Related
Jun 23, 2014
As you can see in the attached excel doc, I have calculated a column for "Number of Claims Per week" and also "Dollar amount Per claim". This was done using VLOOKUP and distributions. That is not the issue.
The issue I am having is creating the "TOTAL" for the week. So for example, if week one generates 4 claims, I need to be able to add up claim amount #1-#4.
It seems as though this would be a basic function, but I can seem to find a way to add up a certain number of cells, based on a variable generated from a random number.
If you take a look a my excel sheet, I am trying to solve for the "Claim Total" column on the far right. This column should be the sum of (Number of claims * The individual amount per claim). Note that if there is 4 claims in that week, I would have to add up claim 1-4 to get that weeks total.
Question2.xlsx‎
View 9 Replies
View Related
May 13, 2014
The spreadsheet has over 200K rows and two dozen columns. My job is to:
1. There are two columns, one is called OCR code where we have repeating ID's and other column is called Minutes Count which is basically a number. I have to filter the OCR column for similar OCR codes(values) and then add their time(Minute Count) so that I get total minutes for a specific OCR code. After adding the min I will copy them into a new sheet with the code in one column, total minutes in another and number of repeatitions for that specific OCR code or count number in a separate column. Example:
OCR_Code; Minutes
54xg; 456
45fk; 65
23IS; 18
54xg; 971
45fk; 265
.
.
.
Now I've to filter 54xg so that I can only see it and its corresponding minutes. Then I will add up all min for 54xg. Then I will count the number of instances a 54xg has been repeated in spreadsheet(here its 2 but it can be in hundreds in my case). Last thing is to transfer all this data to a new sheet. And automatically repeat the process for other OCR codes like 45fk, 23IS etc...
View 3 Replies
View Related
Aug 21, 2014
Formula which will count unique values in column A based on condition in Column B which "y" .....
Show ranges as A:A instead of A1:A100 as I dont know the size of the table, it can be thousands rows .....
View 3 Replies
View Related
Jan 21, 2014
I have a large sheet (several thousand rows and growing) - see a small cut of the data. The columns following on from this have a list of people's names, hence why a lot of the data repeats (as a number of people attended each program).
Program
Clinic Title
Start Date
[Code].....
formula that will automatically give me that answer?
View 2 Replies
View Related
Jan 24, 2014
I have column in my table that calculates time in hours between now and ticket open date (ticket open date in B1)
so, in C1 i have following formula:
=(now()-B1)*24
Results are in hours, such as 15, 25, 32, 65 etc
In pivot table, I would like to see how many tickets with values of less than 24 or more than 48, etc
My available columns are: Region, Ticket#, AgingOpenHr(this new column).
I tried it different ways, and I cannot get it Less Than and Greater Than work under Values, works if I select grater than or less than in Label, but label does not produce consistent numbers.
View 1 Replies
View Related
Feb 11, 2014
i have a spreadsheet that acquires data from 42 other worksheet and in a row of cells it has yes or no at the bottom of that row i have =COUNTIF(A2:A34,"YES"). But it will not count the yes' because the cells that it is trying to count all have formulas in them like =tues!A34 or similar is there a different formula i can use to still count the yes' even in a cell containing a formula?
View 7 Replies
View Related
Jul 7, 2009
How can I minus 1 from this COUNTIF. Basically counting non blanks - but it keeps counting the title as well, even when i change it to start at row D2 (it just jumps back to D1 next time). =COUNTA(RAW_DATA_2!$D$1:$D$215)
View 4 Replies
View Related
Jul 26, 2009
I am trying to use a countif formula to count how many students a teacher has. Here is the formula COUNTIF(fceteacher!C:C,E2) I am using a dropdown list to count how many students the teacher has.
View 4 Replies
View Related
Sep 5, 2013
I have a countif that is doubling when counting. I am using:
=COUNTIFS('Stage 3 Data'!G2:G3000,">="&Breakdown!C19,'Stage 3 Data'!G2:G3000,"
View 3 Replies
View Related
Aug 6, 2014
In column A I have a list if places that can contain duplicates ie
Manchester
Birmingham
London
Birmingham
London
Manchester
Manchester
London
In column B through to D a list of statements to which there are multiple answers i.e.
Yes / Maybe / No
What I'd like to know is how many 'Yes' answers are in the data range for column B:D in Manchester
I've used a countifs but have to result to multiple countifs adding each column together which is fine for 3 columns but not when there are 50!
View 5 Replies
View Related
Jan 10, 2014
My below countif formula is counting the blanks how can i revise to not count the blanks?
=COUNTIFS('Rep Summary'!$A:$A,$A8,'Rep Summary'!$T:$T," ")
View 9 Replies
View Related
Oct 3, 2012
I've got a head-scratcher here that no one in the office can figure out (at my level anyway). I'm trying to count multiples of the same value within the same cell. For instance, if one cell's value is:
2, 2, 4, 5
I need my '2' count column to return two '2's. I'm using a wildcard because the cells will contain the numbers 1-7, some multiple times, and usually in different orders. The wildcard formula I'm using only picks up one if multiples of the same value are entered in one cell. Here's the formula:
=COUNTIF($C$4:$AG$60, "*"&"2"&"*")+COUNTIF($C$4:$AG$60, "2")
This formula only returns a value of one in the '2' count column. It can't find the second '2' for some reason. I haven't been able to make COUNTA work either.
View 4 Replies
View Related
Feb 1, 2007
how do to count the number of occurrences of a text string in a range of cells, where some cell have comma delimited entries?
I am trying to count the number of times a project number is identified in a column of cells. However, in any row in that column a cell may have multiple project numbers referenced, separated by commas.
Using countif Excel thinks that the cell has a different entry and it won’t include it in the count even though the criteria string is in the cell.
View 9 Replies
View Related
Feb 7, 2014
Here is a table with values from Factor1 to Factor 5. Underneath that, for a specific index, I can mark Y or N for factor values. For instance, for index 1, Factor1 and 2 is marked as Y. The goal is to calculate total factor based on variable and factor values. In this case, variable value is 6, factor 1 =1 and factor 2=1.25). So Total factor = 6*1.25*1.5 = 11.25. If all factor values are marked as N, then total factor = variable value (Example is for Index 3)
How this can be implemented. I tried using COUNT function to count the total number of Y but that works only if all factors have same values.
Table_factors.jpg
View 4 Replies
View Related
Jun 11, 2014
Here is a table
Col1 Col2 Col3 Col4
8821015m14:44.0E__uMail
9266321m34:13.0E__uMail
92677165m23:43.0E__uMail
9309415m07:24.0E__uMail
[Code] .....
Here is some context
Each one of these rows represents an entry into a ticket in our incident management system. Col4 represents the method by which the incident was reported to us (Email, Voicemail, Walk In, Ask IT). A ticket can be open and closed with a single entry if the issue is easily solved or it can have many entries if the issue is complex and requires more troubleshooting or escalation to a higher tier of support. I need to determine the number of unique tickets submitted by each reporting avenue (Email, Voicemail, Walk In, Ask IT).
In this table the number of tickets that were:
submitted via Email is 14
submitted via Voicemail is 1
submitted via Walk In is 2
submitted via Ask IT is 1
Col1 Col2 Col3 Col4
8821015m14:44.0E__uMail = 1
9266321m34:13.0E__uMail = 2
92677165m23:43.0E__uMail = 3
9309415m07:24.0E__uMail = 4
[Code] ....
What I need is a formula that will produce this result.
View 5 Replies
View Related
Feb 24, 2014
Ok so I am trying to streamline the Shirt ordering process at my work. I have made a table with Employee names as Column A, then Row 1 is shirt sizes from small to XXXL. The Cell values are going to be the colors people want. I want to total the number of each size/color combination. But someone may want more than one of the same color. For instance you would enter "BK, BK" if you wanted 2 black shirts of that size. This is where the issue with COUNTIF comes in. If a cell has the same value twice, it only counts it as one because counts the cells with an instance of a value, not the actual count of instances. Is there a way to make COUNTIF or a better function for this?
View 6 Replies
View Related
