COUNTIF (b4:b65000= "Name" Then Countif G4:g6500="BI")
Apr 21, 2009
I have a simple database spread sheet and I need to count a column under certain conditions. In one column I have employee names that appear repeatedly, in another I have codes. I want to be able to count how many times the code appears next to the name.
For instance:
If b4:b65000 = Sam Douglas then I want to count how many times different codes appear in the adjacent cell.
Sam Douglas:BI
Sam Douglas:BI
Sam Douglas:SI
Sam Douglas:BI
BI = 3
SI = 1
View 6 Replies
ADVERTISEMENT
Jul 6, 2006
I would like to write a function that enhances conditional formatting capabilities in this way: suppose that cell(4,5) contains a number that can be 0 to 5
in the adiacent cell(4,6) i want to put a function that:
1) write "NO DATA", "HIGH", "GOOD", "MODERATE", "POOR", "BAD" depending on that value
2)Choose color font depending on value
3)Choose color background depending on value
I wrote this piece of
Public Function StatusResponse(AdiacentCell As Range) As String
Dim thisStatus As String
Dim ThisFontColor As Integer
Dim ThisbkColor As Integer
Select Case AdiacentCell.Cells(1, 1)
Case Is = 0
ThisbkColor = 2
ThisFontColor = 1
StatusResponse = "NO DATA"............
View 2 Replies
View Related
Apr 22, 2009
I am trying to have a cell in sheet "Summary" count the number of cells in column DX of sheet "Analyses" that are greater than 0, provided that the value in column A of "Analyses" corresponds with the value in B8 of sheet "Summary."
(In "Analyses," there are 106 subjects, each taking up 64 rows. So, columns 1-64 correspond to Subject 1, columns 65-128 correspond to subject 2, etc. In column DX, each subject has 64 values that are either 0 or greater than 0. In "Summary," each subject has one row that summarizes the 64 trials. I want a single cell in the "Summary," sheet to reflect the number of times each subject produces a value greater than 0 in column DX of "Analyses.") I tried using this formula, but it did not work correctly:
=COUNTIF(IF(Analyses!$A$1:$A$10000=Summary!B8,Analyses!$DX$1:$DX$10000,""),">0")
(Summary!B8 = 1, so I am trying to calculate the number of values in DX that are greater than 0 only for subject 1.) When I press enter, this yields a value of 384. This is impossible, given that subject 1 only has 64 possibilities of yielding a value greater than 0. Subject 1 has 2 values in column DX that are greater than 0. I tried making this an array formula by pressing Shift+Ctrl+Enter, and that just gives me a #VALUE! error.
View 5 Replies
View Related
Feb 19, 2010
i am looking for is a way to count 2 arguments so for instance in T3 how can i count all the "Impression cylinders" in range E10:E400 and the Number 8 in range B10:B400 so it counts in T3 then i can do the same in U3 for number 9 and so on. the only way i no is by using =(sum(IF(AND( But that would take years to write for every cell and every fault is there a shorter way
View 5 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
Feb 8, 2008
I am trying to determine that if a range of cells are marked with an "X", then cells containing "Pass" (A4), Pass with Opportunity for Improvement (A6)or "Fail" (A8) will be marked with an "X". I have 3 columns that are selectable for Pass, Fail, and Non-Applicable. There are a total of thirteen questions that this evaluation form asks, and the evaluator must check one box. In order to be considered a Pass, there must be more 12 or more "X"'s marked in the Pass column. A Pass with Opportunity for Improvement would be equal to 11 marked in the Pass column, and a Failure would be 10 or less showing up in the Fail column. If there is a check in the non-applicable section, this has to be figured into thye pass fail as well. A Pass would be 85% or higher, and failure anything less than this. I will attach the file for review. I would very much appreciate help on this problem first, and then there is one other element to the sheet that plays in at the end.
View 13 Replies
View Related
Dec 19, 2008
I have a spread sheet like this:
Bob 1
Jim 4
Bill 1
Kim 1
I need to be able to count the number of rows that have both "Bob" and 1 in the same row. I know you can do this with one column using: =countif(a1:a4, "Bob")
but I cannot figure out how to do it using two columns!
View 2 Replies
View Related
Dec 29, 2008
I have in Cell M19 : =COUNTIF(F22:F71,"Active")
In Range (F22:F71) I have all the cells = "Active" but are conditionally formated to only show up when text is valid in a corrosponding cell.
So M19 is always = 50
How can M19 = only the cells in the range (F22:F71) that actually show up "Active" from the conditional formatting?
View 9 Replies
View Related
Jan 25, 2006
I am familiar with the formula to count the number of occurrences of a certain item i.e.:
=COUNTIF(Support!$B:$B,"bl-565*")
Which would filter out the number of products called "bl-565" for example.
Say, for example if I then want to further filter the number of bl-565's by problem type, and normally problem type is found by using:
=COUNTIF(Support!$G:$G,A22)-1
Can I combine the two formulas using some kind of logic statement? i.e.:
=COUNTIF ((Support!$B:$B,"bl-565*") AND (Support!$G:$G,A22)-1)
I imagine this is possible, it's just a matter of getting syntax right but so far I have been unsuccessful.
View 12 Replies
View Related
Dec 29, 2009
I'm trying to count the number of incidents in column BB that are >0 but only IF the value in column E is "Abbeywood". i.e. how many times there's a figure greater than 0 for Abbeywood. I can't seem to get count if to do this!
View 4 Replies
View Related
Mar 22, 2009
I can't seem to find out how to use wildcards like "?" and "*" in the VBA- Replace("string","s*r","k") which should give "king"
View 3 Replies
View Related
Feb 10, 2009
In column A, I have the following values:
build
chil rat
datcen
mang
nextg
ost rat
omp
sco rat
In column B, I need the formula so it shows values that if column A = "build", "nextg" or "datcen", then the value in column B is "nextg" and if column A = "chil rat", "ost rat", "sco rat", then the value in column B is "mig". For everything else, it should be blank.
View 5 Replies
View Related
Jul 19, 2007
I have two worksheets, one with "supplier names", "order numbers" and "delay in days" as columns. The other one with "supplier names" and "average delays" as columns.
It is the column "average delays" that I have issues with, I need Excel to search the "supplier names" column in the "delays" sheet and identify every specific supplier name, connect the delay in days for that order and calculate the average delay in the right cell of the column "average delays" in the sheet "delay statistics".
I have tried the help files and to search this forum but I have found nothing. I have also with my knowledge tried a few different ways using the IF function but nothing so far.
This is something I started doing but it is of course far away from any truth.
=IF(;Delays!B1:B200="Greber")..............
This is the "delays" sheet where I want to find my info. Nothing in the delay column stands for "no delay"...Kinda obvious but you know... So even the "nothing" needs to be included in the calculation..
View 9 Replies
View Related
Apr 16, 2009
tell me which operator works in VBA the way the OR operator works in functions? I want to write an IF statement that will evaluate whether a variable is equal to any of the three punctuation marks ".", "?", or "!". But I'm not sure how to code it. I attempted the following:
View 2 Replies
View Related
Jul 10, 2009
In my spreadsheet below I want to be able to enter a sales number for January, the value of cell F2.
I want cells F3 thru F12 to automatically calculate according to the "Growth Per Month" value in cell H1.
Example: If January sales are 20,000, then February should calculate to 21,000 (january * 105%).
Excel Jeanie HTMLSheet1
E F G H 1 2010 Per Month Sales XXX 5% 2 XXX XXX 3 February $ 10,000 4 March $ 5,000 XXX 5 April $ 2,500 6 May $ 1,250 7 June $ 625 8 July $ 313 9 XXX $ 156 10 September $ 78 XXX 11 October XXX 12 November $ 20 XXX 13 December $ 10 14 $ 39,990
Spreadsheet Formulas Cell Formula F3 =F2*10*H1 F4 =F3*10*H1 F5 =F4*10*H1 F6 =F5*10*H1 F7 =F6*10*H1 F8 =F7*10*H1 F9 =F8*10*H1 F10 =F9*10*H1 F11 =F10*10*H1 F12 =F11*10*H1 F13 =F12*10*H1 F14 =SUM(F2:F13)
View 9 Replies
View Related
Dec 20, 2008
As shown in the below image. I have some data from A13 to I 13. Currently if I put the cursor on A13 and press DELETE button then all the formatting from A13 to I13 goes off. But the data still remain there.
Is it possible then when I press DELETE the content of A13 then all the data from B13 to I13 should also get deleted ?
Excel Jeanie HTMLSheet2 *ABCDEFGHI13Key FieldEmp 11 2 4 8 5 3 7 Excel tables to the web >> Excel Jeanie HTML 4
View 9 Replies
View Related
Apr 29, 2009
i just tried the below formulae but its not working. if Currency in col A and Currency col B is same it should be "Matched" if not same the "CCY Mismatch" and if "#N/A Sec" then "Security Not Found"
View 3 Replies
View Related
Feb 24, 2009
I need to prevent users from entering several rows of data seperated by a "/" or a "," or a blank space ""
We use unique identifiers (around 500,000 of them) so I cant really use a drop down box to populate and then make the user select.....
If a user populates a cell with "10005486 / 10045446" or "10005486,10045446" I want to highlight a cell red and then count the instances of red cells on another tab so I can track "errors"
I was intending to use conditional formatting when a "/" or "," or " " is used within a cell. If a user makes this error, I should probably include a msgbox saying why the entry they have made is invalid also....
View 9 Replies
View Related
Oct 22, 2009
I'm having issues with sumproduct. I can't seem to get the right info that I need. Attached is the file I'm working on. Problem: I need to get the number of people "Occupied", "Partly Occupied" and "Available" on a Monday, Tuesday etc of the current week. "Occupied" means an employee has more than 2 tasks (based on New and Active-To-Date status). "Partly Occupied" means an employee is working on 2 tasks.
"Available" means an employee has NO task at all.
View 2 Replies
View Related
Nov 20, 2009
I have attached an example workbook to this message, with the sensitive data removed. I am trying to vlookup the "cube" for the product number, into the PO worksheet, IF the vendor numbers match.
Example:
On row 2 of the "PO" worksheet, part number AC1000110, should have a cube of 2.5 for vendor # 11170. I'm trying to match the "Vendor" on the "PO" worksheet with the "Vendor" on the "Cube File" worksheet, and then return the corresponding "Cube", (in column F of the "Cube File"), in cell U2 of the "PO" sheet. So, what formula needs to be entered in cell U2?
View 2 Replies
View Related
Feb 14, 2009
I am having problems referrring to a range using .Cells
If I try to use the remarked code (red text), it crashes with VBA "400" error for which I cannot find any explanation. It also crashes with same "400" message if I use a cells reference to a numeric column instead of "y").
The code works using the black it loops a column in Sheet("Holidays") and loops a row in Sheet("Schedule"), then color fills 2 ranges in columns in Sheet("Schedule") when the values in 2 cells are equal.
I have attached a scaled down version of my Workbook with this code.
View 2 Replies
View Related
Dec 2, 2009
I need "PC" to show up if I type a sentence containing "Called Parent", "Called Dad", or "Called Mon". Here is what I'm trying.
View 2 Replies
View Related
Mar 13, 2007
I have a procedure that processes data in a spreadsheet. It analyzes data in 365 sheets ( named "1" through "365") and creates a table/report with the results. When I run this on a scaled down version of my workbook (5 instead of 365 worksheets), the result is almost instantaneous. When this is on my normal workbook, the initial processing is about 1 second per day on Day 1, and the last day it speeds up to almost be instantaneous. It is not a linear relationship between the processing time (still working to get more exact timing information). Also, even if I limit my processing to 5 or 10 sheets, Days 1-10 are always slow and days 350-365 are always fast. The code being run is in the structure as shown below:
numSheets = 365
For counter = 1 To numSheets
x = CalculateSomething(counter)
Next
Private Function calculateSomething(counter As Integer) As Integer
Dim strCounter As String
strCounter = counter
With worksheets(strCounter)
For i = 0 To someNumber
For j = 0 To someDifferentNumber
'Data analyzed on worksheet
Next
Next
End With
End Function
The functions obviously aren't copied and pasted/functional, but the relevant efficiency stuff should be there.
View 5 Replies
View Related
Jan 24, 2009
I have a file that uses dates, when a cell is in date the cell is "GREEN" but when the cell is out of date after 3 years it turns "RED". I want to add another formular so that when it gets to 2 years & 11 months the cell will go "YELLOW" for 1 month only before it turns "RED".
View 3 Replies
View Related
May 12, 2009
I have the folowing criteria :
1) If “Completed" , then highlight as green
2) If “Pending” , then highlight as red
3) If “In Progress” , then highlight as blue
4) If “X Required” , then highlight as green
I need to include in the third conditional formatting box that if the cell populates "Completed" or "X Required", then highlight as green. Is there a formula I can use in the conditional formatting box ? I am using Excel 2000.
View 2 Replies
View Related
Jun 14, 2008
I want to replace all defined range names in the sheet that start with "Street" to "Road"
For example I have 50 defined names in the sheet as such "Street-01", "Street-02"... all through "Street-50"
I want to change them all in vba to Road-01", "Road-02" etc.
View 12 Replies
View Related
Nov 4, 2008
how to change the default output values "FALSE" and "TRUE" for the AND() function ? Let say I want to change them to "FAIL" "PASS".
View 2 Replies
View Related
Aug 30, 2009
i have a table in which some value is Equal to 0. Now i want a custom format which Displayed "-"(Dashes without quotes) insted of "0" (without quotes)
View 4 Replies
View Related
Nov 15, 2008
On the sheets where 'present' needs to be checked, after linking the checkbox to the cell it now says "TRUE" or "FALSE". How do I get rid of that? There is a formula that this affects also (just FYI).
View 3 Replies
View Related
Aug 26, 2009
I use the following code to get pivot table data source throught ODBC connect to CSV. However, when I chose hide item in "Page" fields, it showed "all" instead of "multiple items".
View 3 Replies
View Related
