SUMPRODUCT(--(Table[[CAT]:[CAT]]=$C5);--(Month(Table[[DATE]:[DATE]])=G$3);--(Table[[IN]:[IN]]))
place the amount from table column "IN" into a corresponding cell in another sheet. how to add another column (OUT) in the above formula. I wish to summarize both column IN and OUT from table based on two conditions.
Workbook example: [URL]
I have coded data for age range in one column and for answering yes or no to a question in a different column. I need a formula that will count the number of yes answers for each coded age range.
a= 14-15 years old, b=16-17, ect.
Column One Column Two
age(coded) Yes or No
a Y
a N
b N
c Y
a Y
b N
I need to be able to count the number of yeses for each coded age. If the formula works it should give me the following output given the sample data above.
14-15 year olds output with the data above would be 2
16-17 year olds output would be 0.
How would I write this formula? I tried countif and sumproduct (though admittedly sumproduct I don't know at all and just tried to find a similar situation and tried it with my spreadsheet).
Have had a search around the web forums for an answer to this and am not getting anywhere.
In column A i have text - apr, may, jun etc
In column E i have general format text - a 6 figure number
in column M i have text - eg 886.97 (a currency, but in text format)
i want to:
where column a=apr and column E=212130 sum the contents in column M
I am a teacher creating a grade book in Excel. Each student is in a single row, with columns for the various assignments. For data analysis purposes, I am trying to develop formulas to make two different calculations.
The first calculation is simply the average quiz grade for all students, for all quizzes.
The second calculation is the average quiz grade for all grades that are higher than zero. This will tell me the average quiz score for quizzes that have actually been attempted.
I do need to track students that have withdrawn from the class, so column A will contain the letter "w" if the student has withdrawn from the class. If the student has withdrawn, the grades for that student should be ignored in the calculations.
Since I do accept quizzes after their due dates, a particular quiz score will have an entry of "L" if it has not yet been taken. These entries also need to be ignored in the calculations.
See the attached workbook for some sample data and the expected results.
Sample Workbook.xlsx
I am trying to create a daily task activity sheet. I would like to know, how to make the columns visible and invisible as per the selection from previous column.
For eg:
Column A has a list which has values 'TC creation' , 'TC modification', 'System Test' etc.
Upon selecting TC creation, i need to make a column visible ie 'total tcs created'.
Upon selecting 'TC modification' i would have to make the previous column invisible and make a new column visible for entering no of tcs modified.
Upon selecting 'System test' the other columns should go invisible and make other 3 columns visible for no of tcs executed, no of tcs passed, no of tcs failed etc...
I have two columns. In one people either saw Red, Blue, or Green. In the other people either chose One, Two, or Three.
I would like to get excel to read the first column, find all the times someone picked red, then look to the column next to it and see if they picked the number One. Then I would like it to count that situation. Then I want to do it for Two and Three. In the end I would have something that looked like this
---------One----Two---Three
Red-------5------8-------9
.
Blue-------4------5-------3
.
Green-----4------2-------6
Generated from something that looks like this
Red One
Blue Two
Blue Three
Green Two
Some kind of biconditional countif maybe
I've been desperately trying to figure out a formula that allows me to count the number of rows in a column that have different values, but that only counts those rows in which other conditions have been met. Probably sounds a little confusing, which is why I'll illustrate it with a screenshot:
The file contains a listing of major executives of a variety of different firms in different years from 1992 onwards. In the screenshot for example, you see the executives of AAR Corp from 1992 to 1997. Since my file however consists of 240,000 rows, there's a number of c. 2400 firms with their executives over a time period of 20 years each. Since not necessarily all executives remain with their companies for the rest of their lives, some executives show up for a number of different firms in my sample.
I want to determine at how many firms each executive worked within his current industry up to the respective year, based on the information of the 2400 companies and executive data over 20 years. The current industry is classified by 1 digit SIC codes (column U) and each firm has an individual firm identifier which is the CUSIP in column S.
For example, when applying this to the first row in the screenshot (Ira Eichner), I would need a formula that counts the number of different CUSIPs (thus firms), given that the EXECID (executive ID, identifier for each executive; column I) equals 9248, the 1 digit SIC code (column U) equals 5, and the Year (column J) is below 1992.
Im trying to have a formula look at two diferent columns and if they meet the criteria add the third column.
I tried using sumproduct but It wont give me anything but error messages or a zero.
Looks like this
=SUMPRODUCT(A:A="Stewart",(G:G="Fiduciary"),C:C)
Need it to say:
if column A = Stewart and Column B = Fiduciary then add up the amount in Column C
I want to be able to look at two columns and set a criteria to count. I want to look at column A and if its blank then look at column B and if it has a value of more than 0 then count.
A B
1 1.00
2 Yes 4.78
3
4 5.00
5 Yes 4.89
6 11.99
So this example would count 3
I am trying to return a cell address range based on values in different columns. Attached is an example.
In Column A1 I have "Range" and below that a set of numbers from 1 to 31.
In Column B1 the name "Test" is used as an identifier, below that are the letters "a" though to "e". Once the letter "e" is reached the identifier "Test" is moved to column C "C6" and the alphabet continues from "f" down to "j". This process is repeated through the rest of the alphabet and the identifier moved to the next column after every 5th letter.
What I want to do is as follows:-
I would like to know the cell addresses of all instances of "test" from Column A numbers 6 throught to 24. The result should be C7:C12, D14:18 & D20:24. The results should ignore all instances of the identifier "test" and only return the cells which contain the letters of the alphabet.
I have tried using Vlookup which works well if the letters are all in the same column and I have incorporated the Address and Match formulas to return the range if all the alphabets are in the same column, but I do not know how to do this if the identifier "test" and letters move to a differnt column.
the traditional count if statement doesnt return what I need. I have an array of values that need to be checked.
Column: A B C D E
Data: .25 .49 .18 (Criteria 1 Result) (Criteria 2 Result)
What I need to do is check the data for the following conditions and if it meets the crirteria I need excel to place a 1 in column D or E.
Criteria 1
If any of the coulmn data contains a value less than .5 I need a 1 placed in column D
Criteria 2
If any of the column data contains a value greater than .5 but less than 1.0, I need a 1 placed in Column E. I tried using an IF/ Count If statement, but cant seem to get it to return the result I need.
I would like to highlight cells is two conditions are met:Cell = 0Offset(0,-1)>0I tried the conditional format wizard and entered a formula: =IF(AND($J2=0,$I2>0)) But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard
View 2 Replies View RelatedMy data looks somewhat like this :
X Y Z
DOU Quarter Sum
0 Q1 = sum of 1's in Q1
1 Q1 = sum of 1's in Q2
1 Q2 = sum of 1's in Q3
0 Q2 = sum of 1's in Q4
1 Q3
1 Q1
1 Q4
0 Q2
A cell in the SUM column must contain the sum of 1's in column DOU if under column Quarter the value is Q1. Similarly for Q2, Q3 & Q4 if only the corresponding value under DOU is 1.
Its like.. count the number of 1's per quarter and come up with the sum in 4 distinct cells for each quarter.. i.e. quarter-wise sum/count of 1s
I want to calculate the sum of column with two conditions on different
worksheets,
Example
Data
Stock #QTYMonth
01335208Aug-06
01337402Aug-06
01337392Aug-06
01335400Sep-06
01337100Sep-06
01337102Sep-06
If the stock # is equal to the stock # and Month equal to month
I need to get the result in another sheet as below.
Stock #Aug-06Sep-06
01335208400
01337794202
what would be the most effective and easiest formula to use if I want to sum a range of numbers meeting 2 conditions (or more).. eg.. sum all the unpaid invoices (condition 1) due on March (condition 2).
View 9 Replies View RelatedI have the following code:
=SUMPRODUCT(--($A$2:$A$400="OKC"),--($C$2:$C$400="AF33"),$G$2:$G$400)
It works great, but I am needing to now add a 3rd condition, or filter... In addition to it looking for "OKC" and "AF33" I also need it to sort by month. example; "March". I need it to be in code that works on old versions of excel as well...
Although I am not getting any errors, I am not having success returning proper answers with the formula below. Perhaps I cannot do what I am attempting.
=SUMPRODUCT(IF(AND($K$22:$K$169="A&B",YEAR($G$22:$G$169)-YEAR('Info'!$F$9)=0),(MOD(ROW($R$22:$R$169),2)=0)*($R$22:$R$169),0))
where: K22:K169 are user entered categories from a drop down list
G22:G169 are user entered dates
F9 is a user entered update date
column R are monetary values with first row being a revenue$ second row margin$
I need to summarize every other row (one row is revenue, one is margin) if the two conditions are met.
I'm (and you are too!) still helping to streamline a charity's spreadsheet. There are two worksheets that this question refers to: "DD Tally" and "List of DD Donors" (where DD stands for Direct Debit). In the DD Tally worksheet we are trying to summarise the information contained within the List of DD Donors. We would like to be able to estimate how much, in direct debit donation, we will recieve in the financial year (currently, April 06 to March 07). We would like this to be seperated into our monthly, quarterly, semi-annual and annual donors.
The challenge is knowing how to search through a column to find the, for example, "Monthly" entries and once these have been found to find the quantity of the donation(s) in the rows with "Monthly" in them, and then, to determine the number of months they have been paying for during the financial year (their first payment dates are listed as 01/MM/YY) - i.e, the difference between the end of the financial year (03/07) and the start (04/06) or between the time they joined during the financial year and the end. Then to multiply the number of months the direct debit has been active during the year by the amount they donate.
I've been writing a piece of VBA code to process a timesheet for my office.
THe sub routine has a number of variables passed to it, two of which are called CFhrs and Limit. The first contains in integer representing a number of hours. THe latter is a number of hours that can be claimed. In short if CFhrs is less than Limit, the CFhrs should be left alone, but if CFhrs is more than limit then CFhrs should then equal Limit.
First I tried this:
If CFhrs = Limit Or CFhrs > Limit Then
CFhrs = Limit
CFmins = 0
End If
This seemed to be totally ignored when run, and even when CFhrs was greater than Limit, CFhrs was left just the same. So I tried:
CFhrs = IIf(CFhrs < Limit, CFhrs, Limit)
CFmins = IIf(CFhrs < Limit, CFmins, 0)
I am having trouble with formulas below, when i try to use AND, OR, i having trouble .
=IF(B1=SG,IF(A1="I",0,IF(A1="A",0,IF(A1="P",0,IF(A1="D",0,IF(A1="W",0,IF(A1="O",0,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
=IF(B1=LQT,IF(A1="I",150,IF(A1="A",150,IF(A1="P",150,IF(A1="D",44,IF(A1="W",44,IF(A1="O",44,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
=IF(B1=MLT,IF(A1="I",151,IF(A1="A",151,IF(A1="P",151,IF(A1="D",0,IF(A1="W",0,IF(A1="O",0,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
=IF(B1=IBW,IF(A1="I",151,IF(A1="A",151,IF(A1="P",151,IF(A1="D",10+(C2*15%),IF(A1="W",0,IF(A1="O",0,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
=IF(B1=JL,IF(A1="I",151,IF(A1="A",151,IF(A1="P",151,IF(A1="D",0,IF(A1="W",0,IF(A1="O",0,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
I have a table(see attached). I want to sum the total hours worked in Col C. Col B has the number of people on the job (either 1 or 2). I need a condition, I guess , so that if Col b has a number 2 in it, then the adjacent cell needs to be multiplied by 2, then summed into the total hours worked.
I am not sure on how i would write a formula for this. Looking at the sample if I add the total hours from col C at the moment I get a total of 18 Hours but as cell C3 & C5 have a "2" in their adjacent column, the total hours answer should be 27 hours.
I have a table (look at attached file for better info) with TEXT values on Column A (A1:A10) and NUMERICAL values on Column B (B1:B10).
I want to find the MAX value between a value that I set (in cell A14) and the last record (as well as the opposite, between the value I set and the first record).
What this means is that if I set for example D as my value (see attached file for better understanding) I want the formula to find the MAX value between B4:B10 (and another formula that finds the MAX value between B4:B1) based on these conditions
The first and last values (i.e B1,B10) are not included in the result.
The value I set is not included in the result as well.
What this means is that if I set the value D which can be located in cell A4, I want to find the MAX between cells B5:B9 (and with another similar formula the MAX value between cells B3:B1).
The value I set changes every time.
I have created a formula as you will see in the attached file but that works only if I create a copy column A to C and then again I can't find a way to exclude the value I set from my results, so if the value I am looking for happens to be the MAX I get that as a result.
I have been trying to create a report that involves three conditions, but so far I have had no luck using SUM and IF conditions to do this.
I have attached a file with an example of what I would need. Basically, I would need the "Resolved" and "In-Progress" quantities filled in below the "Country Report" for each respective country.
What formula could I use to return the following results.
1, 2, 3,4, 5, 6 = Manhattan
7 through 12 = Bronx
31 = Staten Island
I have a list of item numbers in a column which is Autofiltered. I can go to the autofilter drop down and then to custom and select two item numbers to allow through the filter, however I have about ten item numbers that I want to allow through the filter.
View 10 Replies View RelatedWorking in Excel 2003. I have a VBA code that, if a particular option is chosen from a drop down box, then a message box appears. What I'd like to do is alter this code so that if cell J5 has "Text 1", "Text 2", or "Text 3" then the message box does not appear. Here's my
View 3 Replies View RelatedI am trying to create a COUNTIF formula which will work with two conditions. If you see the attached spreadsheet you will find the data that I am trying to apply a formula to. I have my data in the table on the right. The table on the left is supposed to show the number which the number of destinations that had a certain range of visitors.
As you will see that there are 3 destinations that had 12 or more million visitors, this was counted using a basic COUNT IF formula but for the rest of my data how can I apply the formula so that the correct number of destinations are counted. For example what formula would be needed to count the number of destinations that have had 8-11.9 million visitors. I am guessing that the formula will have the conditions ">=8" and "<=11.9"?
I have two different tables, 1 table gives the conditions you need for specific stations. Stations is shown vertically (10 to 150), and conditions people must have are shown in the top row. (Alphabet). This data is given and can change alot. If I compare this data, a new table will be created that compares the conditions per station with the people if they have it too. Then it will need to give an answer, 'Yes or 'No''. Yes means he has met all the conditions needed, so has all the competences needed for the station.
I tried to do this with normal formulas, but no success. I can do it by doing like 100 IF's in 1 formula, but that isn't efficient.
Also I have done a formula:
IF(SUM(IF(Competentions!$D$21:$BT$21=Competention!$D10:$BT10,0,1))=0,"x",""),
But that only compares if the person has exact the same competences, if he has more then the needed on 1 location, a NO will be returned. Same with count, that doesn't work either, if he has the same amount of competences needed for example station 10, he is allowed to, but he doesnt have the competences that are needed for that station..
I have a formula in cell A1 and text in either cell B2 or C2. This text continues down the rows until there is no text in either columns B or C. I wish to copy the formula in A1 into A2 and then continue copying down column A until both cells in column B and C are empty. What is the excel VBA code?
View 2 Replies View RelatedI'm having toruble developing a formula to allow me to add all the values in cells A1 through A100 if their respective quantities are more than 0.
What i mean is if i have the following columns of data:
A B
6 2
2 1
4 0
A being the values added and B being the quantity then i only want A1 and A2 Added together. Therefore the value would be 8.