I tried Count if statements and sum statements, but nothing is working. I
have 2 columns Ex: If "Jane" from column 1 and "Dog" in column 2 then print
the total number it repeats. This sheet is large. Here's and Example:
Here's the data
Jane Dog
Jim Dog
Jeff Dog
Jane Cat
Jim Dog
Jane Dog
Here is what the cells should print (just the number)
JaneDog = 2
JimDog = 2
JeffDog = 1
JaneCat = 1
I am trying to creat a woorksheet that will calculate the number of time a test question was wrong. I am trying to acount for the various methods the data will be entered, with spaces or commas ect... this is the current fumula that I am using but it is not performing the desired effect. The problem is it is ramdomly counting 1,2,3,4,5,6,7,8,9 when the double digit numbers are entered. is there a better way to achieve this than the current formula bellow.
starting from 1
=COUNTIF($C15:$C66,"*"&1&"*")+COUNTIF($C15:$C66,"1")
ending at 50
=COUNTIF($C15:$C66,"*"&50&"*")+COUNTIF($C15:$C66,"50")
I am trying to get excel to count a value if (and only if) two logical conditions are fulfilled on two different cell ranges.
1st condition: In cell range x, the value should = C2
2nd condition: In cell range y, the value should be greater than 0
I've tried an AND statement and failed, and I've also tried COUNTIF statemetns and had no more luck. This could well be due to my skill level however.
The data:
There are a number of columns, but we'll use the first 2 first. Column A has the dates from 1/1/2009 to 12/31/2009. Column B will has numbers, mainly 0-100. One cell will have a 'report date' which is just a date.
The formula needed:
I have a few cells labeled wk1, wk2, wk3. Week 1 will start with the 'report' date. wk1 I would like the Average of Column B IF the dates in Column A are within the 'wk'. wk1 = report date TO report date + 6. wk2 = report date + 7 TO report date + 13 and so on.
My goal is to have the formulas all set so that when we move to another report date and the raw data is added, I can just change the 'report date' and everything (the wk numbers) will automatically update with the new raw data.
The furthest I got was this, but it didn't work.
=AVERAGE(IF('A3:A33>=VALUE(D3)
I need a way to find a value in a column which has one or more corresponding values in an adjacent column. Then take all of the corresponding values found and count all occurrences of the found values in another column. But I only want to count the entries if an adjacent column is not blank.
Not the easiest thing to describe. Starting to wonder if I need to think in reverse. I hope the attached example makes more sense.
Book1.xlsx
I need an if statement that says if cell a1 is Blank or negative, if true a2, if false a3
View 2 Replies View RelatedI've hit a wall for the first time in my excel using career, I’ve searched the net high and low, asked the guys in my IT department and am so far stumped for a solution.
The problem is thus:
I have 3 columns, A, B and C.
Column A and B contain two numbers. Column A is "telephone calls offered" Column B is "Telephone calls Answered".
Column C is a calculation of the percentage of calls NOT answered. The formula I am using for this is =(A1-B1)/A1*1
This gives me a % as an answer.
Rows 1 to 66 are different time periods through out the day.
The problem is that for some fields, we don’t receive any calls, at all. Or we receive a call in one time period, and it is answered in another.
so Lets say cell A1 is 0, and Cell B2 is 3. Or A2 is 0 and B2 is 0.
How is it possible to get excel to display 0% if, after the calculation the value is < or = to Zero?
I have tried the below "IF" function but it returns a #Value error
=IF((A1-B1)/A*1<=0,"0%",(D67-E67)/D67*1)
I now leave it to the capable hands of the anonymous internet gods of excel
I have three IF statements as below. the problem is if the first statement is true I want it to skip the next two statements or the result will be changed again.
View 2 Replies View RelatedSheet one will contain the following:
DATE A B C D E
2/22/2008TRUEFALSEFALSEFALSERon
2/23/2008FALSETRUETRUETRUEPhill
2/24/2008FALSETRUEFALSEFALSETracy
2/25/2008FALSEFALSEFALSEFALSESharon
2/26/2008TRUETRUEFALSETRUEBill
On sheet two I need to list any date that has three or more true statements with the coresponding name.
EXAMPLE:
2/23/2008Phill
2/26/2008 Bill
I require a Formula to calculate the INTERVALS (the number of Rows between
the LAST instance and the PREVIOUS instance in a column) between each
individual occurrence of any designated PAIR of Numeric values (single-digit
/ double-digit) in the same Row of the Named Range "Results" and return each
calculated INTERVAL result to a separate Column on the same Row of a New
Sheet - starting with the most recent ( the LAST) occurrence.
For instance, each time 80 and 87 appear together in the same Row, return the
INTERVAL by calculating the number of Rows between the LAST instance and the
PREVIOUS instance in a column - locate when both Numeric values LAST appeared
together and Count back to their PREVIOUS appearance together to get the
required Count; i.e. count from the Row ABOVE LAST appearance to the Row
BEFORE PREVIOUS appearance.
The results are returned to a chart / matrix layout: I have the criterion
vertically and horizontally and they are referenced using the horizontal and
vertical cell address that houses each criterion, and the results are
returned across the Row of the intercept of the vertical and horizontal
criterion. At some point both criterion values being referenced will be the
same, can the Formula return empty text "" when this occurs?
Example Chart / Matrix Layout:
Cell Ref. A2 and B1 criterion 80 and 80
Cell Ref. A3 and B1 criterion 81 and 80
Cell Ref. A4 and B1 criterion 82 and 80
Criteria B1 houses 80
A2 houses 80
A3 houses 81
A4 houses 82
A5 houses 83
How can I combine the two below statements like these to make one "Or Statement"? Sorry...I haven't done excel and/or's in forever?
=IF(('Proj Info'!L10="main")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)
OR
=IF(('Proj Info'!L10="serv")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)
I need a Macro (not formula) which compares the comma separated values present in Column "I" with individual values present in Column "D" and generate the count of unique values in Column "J".
The sample sheet has been attached for reference.
I'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.
My current formula in Summary tab D4:D19 is
{=SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$H$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))
+
SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$I$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))}
This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).
The results in the pink highlighted cells (Summary column D) should be:
Names starting with A - 3
All others - 2
I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.
I have an array that I'm using - and it works perfectly: =SUM(If((FREQUENCY(If(LEN(B10:B100)>0,MATCH(B10:B100,B10:B100,0),""),If(LEN(B10:B100)>0,MATCH(B10:B100,B10:B100,0),""))>0),1))
Now, what I would like to do, is see how many of these unique numbers relate to another number in column D
See attachment.
I would like to achieve the following without using vba if possible, though I realise it may not be possible.
Using a formula, I want to count the number of rows which contain one or more of a number of options.
[URL] ....
In the picture there are 6 or so clients: The columns to the right indicate with the letters A, B and C whether these individuals have traits 'A' 'B' or 'C'. So effectively I just want to count the number of clients(rows) which have one or more trait.
Question: Can I do this using just a formula, and if so, how?Workbook attached.
Linked Post: [URL] ....
I couldn't find the right formula for this... I'd like to sum top 10 values from a range of values in cells. Currently only way I can think of is to sort all the values first and then count the top 10 cells. But is there a way to find the top values with a automatic formula, which would fit to one cell?
Example:
A
4
6
8
3
13
4
16
11
9
2
8
8
10
5
Sum from top 10 values would be 96... But how to reach this with a formula?
I am trying to count the number of the cells below EXCEPT when the value equals "OFF". So the answer should be 6 (8 cells - 2 OFF's). Would anyone know how to make a formula that would do that?
$0.10 BALANCEDOFFOFFBALANCEDBALANCEDBALANCED($1.00)
I am trying to enter a formula that will count the number of values in a column and multiply the total by a decimal. For example if A1:A5, contains the following A, A, B, C, B then I need a formula that would multiple the number of occurrences of "A" (2) * .25, the number of occurrences of "B" (2) * .125 and the number of occurrences of "C" (1) *.0625.
View 2 Replies View RelatedI am using and index and match formula to find what date the value is less than zero. However I am not using a continous time series in my figures but a time span for example 2008-01-30 - 2008-02-05. The formula works fine with a continous time series but not for time spans. Does anyone have a clue to get round this. I am enclosing an example where the formula works and also an example where it does not.
View 5 Replies View RelatedI am trying to do up a summary for an attendance sheet that I have.
The problem is I have a number of values that could all qualify as a valid attendance marking and others I need to ignore
I have that list of value in a named range "Present" - Column G on the Lookups Sheet
And I have three sheets I need to add together
The layout of the three attendance sheets is directed so I can't mess with that - and we need uniformity across all the organisations that record attendances.
On the first sheet/tab I have entered some example markings - the "C" markings need to be ignored as they aren't in my list of valid values
The Summary sheet is still counting them as it is currently counting anything <>"".
I guess in short ... how doe I change <>"" to my named range "Present"
I have zipped the workbook as it is 445kb (as a result of some lots of conditional formatting and terrible layout)
I'm working with a data set (attached) that tracks financials at both the job and item level (in my customer's data - a unique job can be made up of a single or multiple items).
I've successfully built a formula (in column C of the attached) that counts the total number of items and their associated $ values that were invoiced in a given month based on my customer's requested item-type categorizations (i.e. "Exact Match", "Similar", "Inferred", "Cost-Plus").
The problem I'm running into is building a formula that counts the first unique instance of the Job ID associated with those items. For example - in cell C33 on the attached - The total number of items invoiced in June that were "Exact Match" was 10. These items were spread across 9 unique jobs - and that's where I'm struggling - to build a formula that only counts the first unique instance of the corresponding Job ID in Column C.
The formula I'm having trouble with is located in Cell D12 on the "Dashboard" tab of the attached.
The formula as I have it is returning a result of 8 - because it's summing the "1"'s in Column I on my "Invoice Data" tab - however it should read 9 (Filter Column Q on the "Invoice Data" tab to "June", Filter Column AQ on the "Invoice Data" tab to "Exact Match", and then sum the unique job IDs in Column C).
Excel_Forum_Example_Dataset_7.24.14.xlsx‎
I have a string of dates and I want to count how many falls between specific dates.
View 8 Replies View RelatedI want to look to a range and if there is a value greater than zero I want to count it. I keep going round in circles trying to do this and now I give up.
View 4 Replies View RelatedI grow my business by dialing a massive amount of leads everyday which I manage in Microsoft Excel. In row "E" I have it labeled as Dispositions and each call I make gets labeled as..
WN = Wrong Number
AM = Answering Machine
UA = Unavailable
NQ = Not Qualified
DNC = Do Not Call
IV = Invite
SA = Sale
I would like to create functions or formulas which displays the total number of each of these dispositions from column E and shows the totals in separate cells so that I dont have to count them manually anymore.
So here is my dilemma and I haven't been able to find anything to exacly help me out:
I have a large (100k+ records) sheet of sales invoice details.
I need to add a total item line per invoice number to each record ....
Card Number
XXXX5512
XXXX5512
XXXX4211
XXXX4510
XXXX6134
XXXX9035
XXXX6002
XXXX8653
XXXX7826
XXXX8819
XXXX8315
XXXX0274
I'm looking for a formula to generate the number of unique card numbers are in the column...I'm using this daily, simply pasting the new data in so if I want to keep it as automated as possible.
I'm looking for a VBA code or formula that will count consecutive values in a column.
Please look at my example sheet.
I'm trying to use this formula to count consecutive values but somehow it doesn't work properly.
Does anyone see what's going wrong here or is there a better formula to this?
I want to count data that corresponds to 4 criteria for data located in Columns B, C, D and E. However, I only want to count only the unique values in Column A. How do I improve on this formula?
=SUMPRODUCT(($E$2:$E$5=J$2)*($D$2:$D$5=I$2)*($C$2:$C$5=H$2)*($B$2:$B$5=G$2))
See data below
Is there a formula that can be used to count all unique values in a specified column.
Example:
Col. A
123
123
Test
ABC
ABC
ABC
Result: 3 (since there are 3 unique values in col. a)