I want to count the number of cells in column C with one forumla that answers the question, "How many of the cells in column C contain the words, APM, BPPM, CAPM, and NMS while exluding any results that have "OUTLOOK" in Column K?
View 2 RepliesI though I could do this with a nested IF statement but it is too cunfusing for me. What I am trying to accomplish is this:
Experiment
Is Steward
EU ID
Location
Data Quality
GE
Entry Order
[Code] ........
I want to have a screen pop-up asking me what my limit < would be for column "ESTCNT" so if I put in 25 or any other number that it would highlight all the rows that are less than 25, then look at the row above and below and if it matches the same number (that is in the cell "Range" of the highlighted column) in column "Range" then copy that row to a new sheet. Meaning all tha rows that match the "Range" would be in the same new sheet.
The rows might be different lengths and that there will not always be a number in cell "ESTCNT". Column headers will always be the same but might not be in the same column each time. And if it is not to hard once it is completed to find column "SPPLOT" in the new sheet created and asking what I want to autofil the column with.
I need to count a column of text excluding duplicates, but only if they are duplicates of the cell directly above them in the column. Example:
a
b
c
c
d
d
a
b
I want this to return 6 instead of 4. Is this possible?
I am trying to come up with a formula that will count everything excluding 1 in one row, while looking at another row to determine the group.
The attached example explains things a lot better.
I am going to have 2 formulas. 1 for the "Big" group and one for the "Small" The formula needs to look first at the column that has the group in it. Then it needs to count everything is column A excluding "Snake" And return the value.
I want to count the nos of cells after selecting some specific parameter in autofilter. I thought this is a simple problem & searched here in various thread but nowhere I found good solution which works nicely. I have attached a excel file where I applied autofilter in the column C & I need to count the nos of cells when I select specific parameter in filter in Column C.
View 5 Replies View RelatedI made a quick little spreadsheet that explains what I am trying to accomplish. Basically I need to search a column for a known number, when it is found, I need to look at another column on that row, and if the cell is a specific item , count it.
View 9 Replies View RelatedI have a macro that adds a row with predefined formulas and formating. The macro is launched by clicking on a button. However, I would like to make it possible to add more than one row at a time. My plan to do this was to use the number of selected row as input to the current macro. If the user selects row 1,2,3 and 4 (or 15, 16, 17 and 18, and so on) four new rows should be added. I would just add;
View 3 Replies View RelatedI am using Excel 2010 and am looking for a formula that can solve this matix in column below. The yellow is where I would like the answers.
A
B
C
D
E
1
DEP
SPS
SUB
2
123456
[Code] ........
This is what it would look like when the formula were applied
A
B
C
D
E
1
DEP
SPS
SUB
2
123456
[Code] .........
I have a COUNTA function that I'm using. However, I want to exclude "N/A" from my count, what is the function that I need to use to do that.
Currently I have =COUNTA($C$10:$C$122). How do I modify this formula so it doesn't include "N/A" in my count.
I have pivot table that is pulling data from a page that is using the vlookup formula. I would like the table to only include fields that have data in the count. However, the pivot table is registering cells that have "0" (i.e., there's no actual data in the cell it is pull from) as having data. How can I get the pivot table count to ignore these cells?
View 1 Replies View Relatedhow to count days excluding only sundays in a calender period.
"weekdays" excludes both saturdays and sundays.
Have several rows
Some have data some do not
Some rows are hidden (Hidden rows also may or may not have data)
I wish to only count how many rows are not hidden.
I have a holiday and sickness chart for all of the guys in my maintenance department for the whole of next year (2014). The holidays part works fine. The sickness is split into two areas; the number of occurrences of sickness and the total number of days sick. To make the formula work for the number of occurrences it is necessary to enter sick in both Sat and Sun if the sickness spans the weekend. But we do not work the weekend and so I do not want to count those two days. The formula needs to cover the entire year (cells BL5:ZL5). The calculation that I am currently using is =COUNTIF(BL5:ZL5,"Sick")/2 (divided by two as we log a.m. and p.m. and the answer is in days). In the example attached, it would be 1 occurrence of sickness and 3 days sick, not 4 as shown.
For completeness, the formula that I am using for the occurrences is =SUMPRODUCT((BL5:ZL5="Sick")*(BL5:ZL5<>BK5:ZK5))
I have a list of courses that are run each month, I have a formula that counts the occurrences of these words to show how many times each course is run per month.
However, there are 'other' courses that will be run adhoc which I want to count the occurrences of. It basically needs to count if there is text there but exclude the normal courses which are run.
I have attached a spread sheet as an example, the list of the normal courses are on the right. I've highlighted in yellow where I need the formula.
I have a list of invoices numbers in column B, some are duplicated
I want to count the the number of invoices excluding duplicates.
I was wondering if was possible to create a conditional calculation excluding hidden columns.
For example in C4:HA4 the cells may have a number of values L, S, U etc.
When a user access the spreadsheet certain columns a hidden based on comparing their PC user ID to an access list in the workbook.
What I would like to be able to do would be to count the instances of L in C4:HA4 but exluding the hidden columns.
I'd hoped I might be able to use SUBTOTAL in a similar way to Domenic came up with here:
=SUMPRODUCT(SUBTOTAL(9,OFFSET(C2:C100,ROW(C2:C100)-ROW(C2),0,1)),--(C2:C100
I am trying to set up a sick leave list which will work out the cumulative total for the last 12 months (from last day of last sick entry). The problem I have, as the is is added to, I need a function/formula to work out 12 months from last date and then add up the no. of sick days from then to the last date. Hope this is clear. The next problem is I need it to work out total days and then without weekends. Below is the chart I have to work out.
Column 1 Start date of sickness
column 2 End date
Column 3 the number of days sick in period (column 2 less column 1 in days)
column 4 the number of days sick in period above excluding week ends (column
2 less column 1, less weekends in days)
column 5 total sick days in last 12 months from date in column 2 going back
a year including weekends
column 6 total sick days in last 12 months from date in column 2 going back
a year excluding weekends
What function or formulas can I use to calculate column 3,4,5 & 6. I am a not very experienecd in Excel
How can I pass a column as a parameter when I execute the sub? ex:
View 2 Replies View RelatedFormula to count the number of consecutive zero starting from the last cell with non zero value.
View 14 Replies View RelatedIs there a function that will count a column of numbers where that will eliminate counting empty cells based on a certain date that will also capture any numbers that is added after refreshing the table from Access? I have attached a spreadsheet for an example. I need to count on the number in column V that equals 13 for the date of 12/22/09.
View 5 Replies View RelatedI have the below table of data and what I am looking for is a formula that will count the number for unique numbers (col E) depending on a several criteria. So far I have got this formula but it's not working!
=IFERROR((SUMPRODUCT(--(A3="2014"),--(B3="1"),--(D3="Boc Limited"),)),"")
A
B
C
D
E
year
week
smsupplier_code
supplier name
orders_no
[code].....
I am having trouble figuring out a way to get around this situation:
I have a to find the minimum in a range. I have used =Min(range)
However, i wish to exclude 0 values and n/a errors in the case no data figure in one or more cells of the range.
Could you please help me figure out a way?
I have named/(grouped?) a column (J) "DeltaNet". I want J2 to sum everything below (excluding the "Net" Header). I tried =sum(DeltaNet) but get a circular error. I'm also trying to get cell A1 to do a =countA for all below it.
Sample: Count Formula
Sum formula
Ticket
Date
carrier
CarrierID
Product
Net
12345
6/1/13
UP
CEFX123456
CoarseFaction
114.35
Previous question regarding dynamic ranges within a table to create a dynamic chart.
I've now come across another issue regarding the same table but creating a new graph to display a new graph. This time, I need the graph to only include labels with data in them.
The data is based on months including achieved targets and forecasted targets. I need to show a graph for the achieved targets to date and exclude any future months from the graph. e.g. For this month, I would only want to show January to July values and not include August to December.
How easy is this to do? I don't know how to use the OFFSET function but think that this might be a possible solution.
Project: x
Activity: xActifity TypeReference FormDec-11Jan-12Mar-12Apr-12May-12Feb-12Mar-12W/EW/EW/E2-Mar9-Mar16-Mar23-Mar30-Mar6-Apr13-Apr20-Apr27-Apr4-May11-May18-May25-May77%91%91%R/S95%100%100%R/S100%100%100%100%MMMM100%71%71%R/S100%100%R/SMMMM97%81%100%R/S100%MM100%R/S75%85%R/SR/SMM100%100%96%Average
Basically what I want to achieve is the average % of Dec 11 (D11:D22 on the sheet im working on) scores but excluding any blank fields (not excluding potential scores of 0%)
I know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}
however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.
1 workbook, 2 worksheets (or tabs). On tab 1, I want a formula/alert that tells the user if any duplicate values exist in Column A of tab 2
Tab 2, Column A, has Unique ID's (6 digit numeric values)
The user manually inputs the ID's on new rows in Column A
Row 1 is reserved and in use for something else
Row 2 is my header, so cell A2 says "ID"
Row 3-623 currently contain unique ID's
When the user inputs a new ID into cell A624, then they return to Tab 1, I want my formula/alert on Tab 1 to tell the user that they have duplicates in Column A of tab 2. I know the Conditional Formatting, but if the user copies in 100 new values, they won't necessarily see the highlighted cells. My tab 1 is my "checks and balances" and the last place the user is suppposed to look to ensure that they haven't created any duplicate ID's. If the user sees a warning message that says duplicates exist, then I'll tell them that they need to look at column A (for cells that have been conditionally highlighted).
One issue that I'm running into with the conditional highlighting is that I want cells A3:A1048576 to already have the conditional formatting - this way when the user inserts a value into Cell A624, then A625, etc they conditional formatting is already there. Right now with data in cells A3:A623, cells A624:A1048576 are all highlighted with the Red/Bold Red Font (which is okay I guess), but ideally it would be nice to not count 2+ empty cells as duplicates and I'll have to have my formula on Tab 1 not include the blank cells.
I DO NOT want to use the Remove Duplicates feature of Excel 2010. If I remove them I could be removing data in columns B, C, D, etc that belong to the Unique ID. I just need the user to be told in Tab 1 that they DO have duplicates and I'll train the user how to research this and fix it.
The reason I want to look for duplicates in the entire Column A is because the list of Unique ID's will grow over time.
I have a spreadsheet with multiple coulmns of data with rows which equate to each site location.
Basically I am after searching one column which says "Earth Rods Fitted" which is located in Column K which goes from K4:K767
In each the rows its either yes or no answer.
So the search would count number of "no" entries in column K.
But then it would search also on column Y (Y4:Y767) for a range of values of risk assessment.
So in column Y you could have each row with different assessment risk score from 0-300
So my search would need to count coloumn K for no earth rods fitted and then count within this range the number of cells in column Y which have risk score say between 200 to 400.
Column K Coulumn y
Earth Rods Fitted Risk Score
No 350
Yes 55
No 222
No 90
So in above case there is 3 enries with no earth rods in column K and in Column Y we are counting rows which have a value in range of 200-400 which above there is 2 entries. So basically I know there is 3 sites needed doing and my worst to based on risk is the two which are 350 and 222.
I have messed around with COUNTIF functions which I can search column K ok, but doing the range search on y in conjuction with K I am finding hard. Someone mentioned use Vlookup but not sure how to do it.
I need to find month / year and count the number of times it occurs..
This is what I have so far..
=SUMPRODUCT(--(main!D:D="name"),--(main!M:M="dormant"),-- (ISNUMBER(SEARCH("??????????",main!h:h))))
the problem is that the date column i'm searching had the following format: 28/1/2009 11:37:00
what do i need to replace the ????????? with to to make it look for the month and year only from the format above.
Is there a way to do this without using a macro, but I need it to be in a macro.
Column A has a value I am calling a label, ex. ABCDEF which occurs over and over. Column B has a list of animals, many of which repeat AND will be together if they do repeat. In other words, all rows in Column B with Cows are together, occurring in consecutive rows. I need a macro that will look at each row in column C and increment +1 starting at 0. That will be concatenated with the value in Column A and pasted as a value in column C.
See the linked spreadsheet tabs for Before Macro and how it should look After Macro is run.
[URL] ........