COUNTA IF Criteria Based On 2nd Column
Nov 12, 2012
I have a sheet with 2 columns. First Column lists sizes of Devices. Second colum lists a servername. Example:
A B
30 server_a
30 server_a
65 server_a
65 server_a
45 server_b
45 server_b
15 server_b
What I want to do is a device count, not device sum on a criteria based on Column B.
For instance whenever column B equals servername A, then count how many devices it has.
This would equal 4 instead of 190 which is the sum of the devices. I can do a sumif statement, very easily, but I don't want a sum, I just want a count.
View 4 Replies
ADVERTISEMENT
Feb 21, 2010
I trying to convert the following formula (I, II, III) and add criteria of Name and Date to the count values of “c”
CURRENT FORMULA
I: “=COUNTIF(C2:C3100,"
View 9 Replies
View Related
Apr 2, 2009
I've got two sheet's in a workbook. The first contains data and is setup with dates in column A, day of the week in column B, and values going across by day in column's C through H. As an example it looks as follows:
View 5 Replies
View Related
Sep 16, 2013
I am trying to write a VBA code that does the following: The table below provides a sample of the Source (i.e how we start off with) and the Result (i.e how the format needs to look like). More specifically, for each product in 1st column that has a number in blue and or red column i need a row inserted immediately below (hence the counta function to indicate how many empty rows to insert). The end result should have a separate line for each blue/red colour depending if there is a number shown in the source data. Obviously the result should be displayed in another sheet (i.e sheet2).
PRODUCT
BLUE
RED
COUNTA
PROD A
9902
1
[Code] .......
View 5 Replies
View Related
Mar 4, 2008
i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)
I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}
I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message
Function
Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)
Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)
-Didnt get thru this bit to start building on the Function! keep giving me #Value!
View 5 Replies
View Related
Feb 7, 2014
I have the following sample data set and I'm trying to return the appropriate column header based on criteria (i.e. DDD) and a number value which will be somewhere within the range of the table. In example below, the value returned should be Header 2 because the value is greater than those in Header 1 column (range H9 to L26).
Here's data table:
CriteriaHeader 1Header 2Header 3Header 4
ZZZ5.0015.0050.00130.00
ZZ5.0015.0050.00130.00
Z5.0015.0050.00130.00
YYY5.0015.0050.00130.00
YY5.0015.0050.00130.00
Y5.0015.0050.00130.00
DDD5.0015.0050.00130.00
DD5.0015.0025.0075.00
D5.0015.0020.0065.00
RRR5.0015.0015.0045.00
RR2.5010.0010.0030.00
R1.503.0010.0025.00
UUU0.751.505.0020.00
UU0.751.505.0020.00
U0.751.505.0020.00
P0.751.505.0020.00
T0.100.105.0018.00
CriteriaNumberValue
DDD10.00Header 1>>>=INDEX($I$9:$L$9,MATCH(I29,INDEX($I$10:$L$26,MATCH(H29,$H$10:$H$26),)))
View 3 Replies
View Related
Mar 10, 2014
I have two columns. Column A had numbers and column B has names. I need a count from column A for each name in column B.
View 1 Replies
View Related
Jan 30, 2013
If i have the following layout of data
Column 1 Column 2
January 1000
January 1234
February 1300
March 1600
January 15
March 123
April 234
January 3000
I would like a formula that adds all the January numbers together returning a result of 5249
To move this one step further i would eventually need to add these numbers based on quarters, for example if the value is January February or March in column 1 then add the numbers in column 2.
I'm sure i have done this before using a countifs maybe but my mind has drawn a blank
View 1 Replies
View Related
Jan 13, 2008
I am trying to get an average from one column based upon criteria from an adjacent column. The number of days to close a case for race columns Black and White are listed in B5:C16 and E5:F16 and H5:I16. I need a formula to calculate the average days taken to close cases for Males and then the same for Females. Sample below: ...
View 12 Replies
View Related
Aug 17, 2006
I have an personnel file with employee info, and I want to create a macro that will look in the "Master" worksheet at the Department column ("I") for anyone in Benefits, and then copy their name from the Name column ("D") into the "Benefits" worksheet. The names should begin pasting in cell "D3" but will recognize if a cell already has a name in it and then paste in the cell below that.
Here is what I have:
Option Explicit
Public Sub RatingbyDept()
Dim Dept As Range
With Sheets("Master")
For Each Dept In .Range("I2:I1000")
With Dept
I keep getting error 92 - "For loop not initialized".
View 8 Replies
View Related
Mar 30, 2014
I have 2 columns; 1 with "dates" of incidents, the other with "number of incidents". The dates are not in any order but I want to be able to count "number of incidents" for certain time periods. e.g. If dates are between 01/02/14 and 28/02/14, what are the number in incidents. This number would be represented on a new worksheet.
I've looked at COUNTIF/S, SUMIF but just cant get there.
View 8 Replies
View Related
May 10, 2014
The below sample is an accurate example of a much larger set of data. Column H will be my desired result.
I need a macro that will look at the matching values (numerical value of month) of Column F, then enter the sum of the corresponding values (rows) contained in Column D for that month. The sum of each month should be placed in Column H at the last value of the row for each particular month.
A
B
C
D
E
F
G
H
1
Item#
Description
Quantity
Month
Year
2
1001
[Code] ........
View 9 Replies
View Related
Oct 13, 2011
VBA code to summarise a column based on 2 other columns.
All flagged codes must be summed and all non flagged codes summed.
code.....amount.....flag.......summerised code..sum amount...flag
aaa100...yaaa200...ybbb100...ybbb200...yaaa100...yccc100...ybbb100...yaaa300ccc100...ybbb300
aaa100ccc200aaa100bbb100bbb100ccc100ccc100aaa100bbb100
View 2 Replies
View Related
Oct 31, 2013
I am looking to count every 4th column that has a average score less than 90. My columns are set with 3 scores and 4th column has the average of those scores. Like such:
untitled.jpg
I can count them using a SUMPRODUCT(--(MOD(COLUMN function but I cannot get a criteria to work.
Non-VBA preferred
View 4 Replies
View Related
Dec 6, 2013
Attached is a workbook that better explains what I am trying to do. I am counting pastdue accounts from several different insurance companys. I have several pastdue brackets, and I need the total amount of pastdue accounts for each bracket, for each insurance company. I think the countifs function wrapped in an index match may get the job done, but I have not been able to figure it out. right now I have a countifs wrapped in an if statement, and it is producing incorrect results.
test book 1.xlsx‎
View 3 Replies
View Related
Oct 18, 2008
I would like to title columns based on values from other cells...
View 9 Replies
View Related
Mar 5, 2008
I am trying to auto-fill a column based on certain criteria - in other words, fill in blank cells based on a cell above, just a little more complex version. I have added an attachment to give everyone a visual of what I am describing and then maybe this explanation will make a little more sense.
First, if you open the attachment, the left hand side is a small example of what I have. The middle is the condition. And the right hand side is what I'd like to see happene.
In the example, I am matching up a time and when I hit that time, I am adding on a certain number of miles per hours based on the condition and the cell above. Meanwhile, I am filling in the blank cells with the condition until the condition changes. So a cell is incremented based on the condition and gets its value from the cell above.
View 14 Replies
View Related
Nov 7, 2012
I'm not sure if this is the correct way to display my sample data, but here it is (how to change it. I looked in the guidelines, but I can't download anything onto this computer, so I can't get the HTML maker)
A
B
C
D
E
F
G
H
I
1
Descriptor
Group
Identifier
Inventory
Total Vaue
Invetory Type
Group
Total Incomplete Inventory Value
[Code] .......
Here is what I would like in:
The sum of the total value of half finished and unfinished inventory for each group.
So, for group one, it would be 145. For group two if would be 38, and group three would be 316. (displayed in column I)
I think an array formula would do this, but I'm not really sure how this would work.
View 9 Replies
View Related
Aug 19, 2013
There is probably a simple way of doing this in excel but the only way that i can think of doing it is through VBA.
There are about 25 columns and 4000 rows in my spreadsheet. I want my macro to look up 5 criteria in Sheet 1 and the same 5 criteria in the previous years spreadsheet. If all 5 values in Sheet 1 match all 5 values in Sheet 2, i want it to import a cell's value from that row in Sheet 2 to the active row in Sheet 1.
Here is the code I have so far:
Code:
Sub Import2012Numbers()
Dim BlockNbr As String
Dim Position As String
Dim Scenario As String
Dim LOA As String
Dim ScenVal
Dim x As Integer
[Code] ...
I think i've got everything down in terms of the variable definitions etc, but i think the problem is coming from the order or method of the for each and looping process.
I need the macro to take A2, S2, U2, W2, and V2 in Sheet 1 and look through all the rows in Sheet 2 to see if any of the rows match all 5 criteria. Only then do i want it to move on to A3, S3, U3, W3 AND V3 in Sheet 1
When i step through the code i keep getting the error message "Next without for" for i, or "For command already in use".
View 1 Replies
View Related
Sep 1, 2013
I've worked on this one way to long and thought I'd ask here now.
Column A Column B
A 1
A 0
A 0
B Blank Cell
B 0
B Blank Cell
B 0
B 0
I have a third cell that I would like to count how many zeros in Column B there are that correspond to the letter B in Column A. Blank cells cannot be counted. This answer should be 3.
View 7 Replies
View Related
Apr 18, 2014
A1:A10 = Criteria Range 1
B1:B10 = Criteria Range 2
C1:E1 = Criteria Range 3
How to sumif all 3 criteria are met?
View 7 Replies
View Related
Apr 30, 2014
I am really struggling to summarise weekly budget data into period budget data by subcategory.
I have column headings A3 being Subcategory B3 being GL Code & C3 being GL Name - where column B & C are distinct values but column A are not. And then in D2 I have the period no e.g. P01, with the Week no in D3. I want to sum all cells that are in a particular subcategory and period - the data set is broken down by weeks.
View 4 Replies
View Related
Nov 30, 2007
I am trying to get the average of two columns
column A and Column B
Column A has text (survey type) i.e. Move In, Mid Year, Year End
Column B has the scores given on that survey type i.e. 70
I am trying to get the average of of all the Move In cells from column A
for each survey type so that I get average of all the scores in column B.
View 3 Replies
View Related
Nov 7, 2013
What i really need it to do is if each row in column H = "Cleared" then to only clear columns A, C to H. Theres a formula in B that needs to be left... any ideas?
Also i need this to work on all atbs as the commandbutton will on a master tab
VB:
Private Sub CommandButton1_Click()
With ActiveSheet
.AutoFilterMode = False
[Code]....
View 4 Replies
View Related
Apr 22, 2014
I am looking for a formula as per the thread title. I have attached an example workbook.
I have been playing around with SUMIFS and SUMPRODUCT but can't crack it.
sum based on criteria transpose columns to rows.xlsx
View 5 Replies
View Related
Jan 31, 2014
I have been creating a schedule on excel, the schedule includes a top row which has the following headings Date, Agent_ID, title, agent_name, 07:00, 07:15, 07:30, etc up until 21:45
The columns that are named with times are times that indicate a break time.
The column named title is the actual shift time, eg 08:00 - 17:00.
I need a formula that would look at my source data, and populate a sheet in the following layout
agent_id, agent_name, title, start_time, end_time
The title be one of the following:
Shift 08:00 - 17:00
Tea Break 10:00 - 10:15
Lunch Break 12:00 - 12:30
Tea Break 14:15 - 14:30
If I need to have the shift portion and the break portion appear on separate tabs that would also be ok, but ultimately I need to keep my original source as is, but the change it to be able to upload it into a MySQL database.
View 2 Replies
View Related
Mar 10, 2014
count cells row wise that meets criteria both in column & Row as well.
View 2 Replies
View Related
Jul 23, 2009
I'm using a sumproduct forumla to count rows based on specific data in multiple columns. So if column A equals 1 and column G does not equal 6 and column M equals 4 then count that row. I know how to do this. The problem I'm having is that I want to count column A if equals 1 or 2. I tried adding an OR comand in with my sumproduct but it doesn't seem to work that way.
View 4 Replies
View Related
Apr 14, 2014
I need a macro to turn to values all formulas in specific columns only. For example column C, F, I, L,O,...until column UYG, always with the interval of 2 columns.
View 1 Replies
View Related
Oct 19, 2011
Simple code that can delete entire row if certain criteria is met in a single cell
Example
I have a bank statement where under a first column (DATA TYPE), the cell could contain either "DATA" or "TOTAL"
How can I delete the rows contain the word "TOTAL" assuming the column is already sorted.
View 3 Replies
View Related