how to sum 3 cells when 2 out of 3 cells match.
Here is the data.
Cells A1:A10 = Florida
Cells A11:A20 = Florida State
Cells A21:A30 = ~Florida
Cells b1:b5 = W
Cells b6:b25 = L
Cells b26:b30 = W
Sum all cells that have "Florida", "~Florida" and "W" in common.
I'm struggling to work out a formula to do this even though it sounds simple.
I want to count cells in a particular row or column that contain any data, ideally without having to specify a range, so I just want to know that in column C contains x or row 5 contains y amount of data. So it would look at the entire rown or column and work out how many cells contain something and shows how many.
obviously if one wants to count all cells that contain data they can use COUNTA, but what if i have a range of cells that contain IF formulas and only want to count the cells that display data?
presumably you'd have to use some variation of NOT(""), but i can't seem to make it work.
I want it to count and fill in a range in column A until it sees a blank or notices the change in value in column B. In the example below i hope it shows what i need to do. i left the last group without numbers to show that is where it needs to start counting over again. i am basically wanting to count down 1st place 2nd place etc.
View 8 Replies View RelatedSay the Range is (D3:M3) there is data in D3,F3,J3,K3,L3, and M3. I want to count consecutive cells with data from M3 backwards to D3. In this scenario the sum would be 4.
View 7 Replies View RelatedI've been asked to produce a spreadsheet that monitors the time it takes to answer queries from partner companies. Ive attached some of the spread sheet that im struggling to work out. I would like a formula that will total the number of days it takes us to answer the queries per month for each company.
Example - For January
Compnay A 8 Days
Company B 7 Days
Company C 13 Days
Counting a variable range. Does anyone know why this is not working...
Dim r
r = ActiveCell. Offset(0, 12).Value = Application.WorksheetFunction. CountIf(Selection, "Standard")
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = r
I have a filtered worksheet -WB1 (filtered by Column "B"). I want to count the number of cells or rows in column "B"(Only the filtered ones) of WB1. Copy out that exact number of cells from another workbook(WB2) from the bottom moving up(Column "A") and paste it into WB1 column "I".
WB1 - Count Filtered Cell/Row with reference to Column "B"
WB2 - copy Cell count bottom to top of Column "A"
WB1 - Paste into Column "I"
I'm having trouble getting a formula to sum the last 10 results of data entries, so far I have:
=SUM(OFFSET(INDEX(D2:D300,COUNT(D2:D300)),-9,,10,1))
This works fine on the assumption that all the rows contain a value 0 or 1, this may not be the case as rows may be left blank for a varying degree of time and results may be posted intermittently?
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.
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: ...
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) .....
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.
I have 1 column with a varying amount of rows (around 2000+ rows) with data that is about 10 chars long. Since I cannot print it all on a few pages, I would like a way to re-orgnize the data into cells so I can print on as few pages as possible such that the qty of columns isn't wide then an 8.5 x 11 page.
One idea is the following.
Here's what i have.
Col1
1
2
3
4
5
6
7
8
9
10
...
Here's what i want.
Col1 Col2 Col3 Col4 Col5 Col6 Col7 ...
1 2 3 4 5 6 7
8 9 10 ...
I am trying to paste data into cells that are offset from specific data in my excel sheet.
The code I've got so far is:
[Code] ....
What I am aiming for is to search my Columns G and H that contain the values 1 & 1. Then from the cells that contain those variables, move 2 row up and 7 columns to the left and then paste on that active cell.
I am completely new to VBA so im sure there plenty wrong with this VBA script but so far ive managed to paste the data but it just pastes across the whole row instead of just the cells ive copied.. now i just get errors on the script.
Is there a function in Excel that will count cells that are different from one another? I.e. it would not count the same value twice. For example, in column A:
a
a
b
a
b
b
c
a
c
The result would be 3. However, if I replaced the first "a" with a "d" I would get 4 as a result. Basically I want to know how many different arguments there are in a list. I could not find a function that does this in the Excel list of functions.
I am now attempting to do the same type tally except I want a tally for "in conference" Wins/Losses.
I tried the same "SUMPRODUCT" formula as before except this time I held the control key down so as to use only the cells desired. I'm getting "VALUE" for my answer though.
The row I have added in for in conference tally is row 3.
All blue colored cells are for the ACC conference and the green are for the SEC.
Is there a way to get the win/loss figures for selected cells / rows only?
I am trying to create a formula to count the # of names up to a certain word. So Cells A1-F1. Are a list of names, the Last name in the list starts with VACANT. How do I create a formula to count the # of names in the list upto the work VACANT? So if VACANT moves to C1 it would decrease the # of names.
View 9 Replies View RelatedI have written some VBA code to to test if there are 8 names within a range (EF5:FH5, these are 8 merged cells, (4x4 cells)), if there is 8 names within the cells, ie not any empty cells, then the coding will add the new staff name to "trigger_box_1b", and if not then "trigger_box_1a". These trigger boxes are then used to populate further cells.
Private Sub Trigger_Box_1_Change()
Windows("Admin Skills Matrix.xls").Activate 'puts focus on correct spreadsheet
Sheets("Skills Matrix").Select 'puts focus on correct page
Select Case WorksheetFunction. CountIf(Range("EF5:FH5"), 0).double
Case 0
Trigger_Box_1b.Text = New_Staff_Name
Case Else
Trigger_Box_1a.Text = New_Staff_Name
End Select
End Sub
the problems I have be having is that VBA is not recognising the Countif function ("Compile Error: Invalid qualifier", then highlights the Countif). I have tried using a CountA function with the same responce. I have tested the rest of the coding and know that it's working fine (don't think anyone would like to see 75+ pages of VBA coding)
I know this question may have been posted before, but I can not seem to get this to work with my formula. I have a formula to count the values of a column so that I can convert it to a graph. I am giving a value of 1 to cells that contain numbers between 0 and 9.5 and giving a value of 0 to cells that contain numbers greater than 9.5. It works fine except that I have some blank cells that I do not want to get rid of and it is counting those as zeros and giving them a value of one. I want to ignore the blank cells or give them a value of zero.
=IF(AND(F2>=0,F2
I need a formula that will look into a range of data and tell me whan the last time a value exceeded 0 (working backwards).
So below the first row would return a value of 6, the next 5, the next 0, the next 1 and so on....
I can do it with an if formula but the amount of days it will be looking at will be too many, plus the range will keep growing as time passes.
FriSatSunMonTueWedThuFriSat
222000000
111100000
111100011
110111110
000111111
000000011
111111111
111111111
5117400000
564000000
8110660000
0000018171318
My data is as follows:
Month 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
Number of Starts 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10
Rolling Number of Cases 10 20 30 40 50 60 70 80 90 100 110 120 120 120 120 120 120
In my dataset above the cases finish after 12 months (hence why there is a maximum number of cases).
What I am seeking is a method to count along a certain number of cells, then sum the contents of those. For example, I would like to count the contents of up to 12 cells, then sum those 12 cells.
Where it gets difficult is as follows:
- In month 6, I am just counting the contents of 6 cells (months 1-6)
- In month 12, I am counting the contents of 12 cells (months 1 -12)
- In month 18, I am counting the contents of 12 cells (month 7 - 18)
way to count cells in a spreadsheet (not values in the cells but just the cells themselves). So I have a whole bunch of cells, some are red, some are blue, some are green based on the info i have to keep track of.
so can i write some COUNTIF for red cells green cells, etc
I have attached a spreadsheet with some tele numbers. What i need to do is to find out which column only has 1 telephone number, eg. only a cellphone number, etc. I have aboout 50 000 records that i need to check if only 1 telephone number is available. Can i use the countblank function?
View 2 Replies View RelatedA cell on sheet1 needs to count the number of cells on sheet2 (D3:D31) that has a complete (top, bottom, left and right) border. This is my best shot at it.
[Code] .....
Sheet1 cell I input =CountBrds('sheet2'!D3:D31)
One issue, on sheet2 for example cell D4 is merged with E4 and F4 as is every even cell in the D column down to row 30. Not sure if that throws a wrench in things or not.
I have a large number of customers listed in an excel sheet that may recieve a visit from my organisation. The reason for the visit may vary and sometimes a customer may recieve more than one visit. Each Row (or record) maps to a customer. Each column has a visit type which I insert a date in to say when the customer has been visited.
I am looking for a function that will return if a customer has been visited or not. As dates can be summed like numbers I am currently saying in the "Visits Recieved" column =if(sum of dates (visit type colunm) >0, 1,0) Then I simply sum the column to get my answer of how many csutomers have been visited.
I have a column (within a database) containing hundreds of Project Numbers
(e.g., P8763, PA3229, P1090.1, etc.), and the same Project Number can appear
more than once in the column. I need to count the number of unique Project
Numbers within that column, based on the criteria of another column. Is
there a way to do that using a combination of the DCOUNTA function and a
formula?
I am trying to write a formula for the following can anyone help please?
I have column D with blank cells and some with purchase order numbers in them. I want to count the blank cells in column D but only if the blank is 3days older than the date recorded in column A. Once I have done this I want to count ther same on every work sheet and I have 24 off them and get one answer.
This will tell us all the outstanding purchase order older than 3days
I have a Column (G) of dates that is the sum of Column (B) and 6 months. I have conditionally formatted Column (G) to turn RED when overdue Yellow when close and GREEN if more than a month out. At the end of Column (G) I would like for it to add all the "RED" blocks and total them.
View 12 Replies View RelatedCells in the range of BG8:BP8 either have a "W" or an "L" in them. I want to have cell BO9 display the total number of Ws and cell BP9 to display the total number of Ls.
View 3 Replies View Related