How to get Count of each Column value of table?

Formula

• I need to find the formula for anniversary years. Say the league started in 1980, I need my next column to show me the updated amount of anniversary years. I am tring to add time. Example; 12:48 a.m 1:24 a.m. answer, 26 minutes What is the formula to get 26 minutes with manually adding. I would like to return a value (in this case a L or W) to a specific cell based upon the background color in another cell. I can do it pretty simply with an IF statement if it were data but it is color. Is there a simple solution? I have a formula which i am really struggling with but I think im thinking too hard about it 2010 (sheet name) Col 1 - Invoice number col 2 - project 3 - msn 4-fee type 5 - amount invoice 6 - issued 7- funds due 8 - funds recieved 9 - dvb 10 - aam 11 - aam in $ 12 - status (the status feild is either "pending","paid" or "overdue") I want a second sheet named "over due" that looks into "2010" and displays each invoice that has "overdue" written in the "Status" field In the "overdue" sheet i want it only to show: Col 1 - invoice number col 2 - project col 4 - Fee type col 5 - amount invoiced col 7 - funds due many Robert Good afternoon.I hope that my question makes sense. This is the info that I have. Name DOHire Check Dates hours used John Doe 06/13/2007 08/15/2007 15 John Doe 06/13/2007 02/10/2008 12 John Doe 06/13/2007 09/21/2008 06 Jane Doe 08/10/1992 08/21/2007 20 Jane Doe 08/10/1992 11/02/2007 05 Jane Doe 08/10/1992 09/25/2008 16 I want to write a formula that will look at the names, then go and look under the check dates and give me totals of hours. (Ex. on the 2nd sheet it will have a colum that says Vacation hrs used between 01/01/2007 andAnniv. Date. or fromAnniv. to end of yr.) I hope this makes sense QUESTION: I have a database that includes a person's city of residence and date they had an appointment. I want to count how many appointments there were in one city per month. So I guess I'm wanting my formula to say look in column b if this columns says "Toronto" then look in column c and add up how many occurrences there are during December 2009. Hope that makes sense. Heres an example of my database: A B 2010-01-07 Toronto 2010-01-08 Ottawa 2010-01-07 North Bay 2010-01-07 Toronto 2010-01-07 Ottawa 2010-01-04 Toronto Ultimately I would like to be able to do this for each month and for each city. ANSWER: Let me presume - in C2 you enter desired month (say 1) in D2 you enter desired city (Say Toronto) Then to get count of Toronto in Month 01, you enter following formula in E2 =SUMPRODUCT((MONTH(A:A)=C2)*(B:B=D2)) ---------- FOLLOW-UP ---------- QUESTION: yes that is correct. Now I realize I have forgotten the third part to my question. So if i were to keep all that but add in another condition to be met. For example In column C There will be one of three conditions a 1, 2, or "blank" cell will follow. So If i were to say I want to know how many Rows contain Toronto, Date in question, and either a 1 or a 2. How do I enter that ? So it looks As follows: A B C 2010-01-07 Toronto 1 2010-01-08 Ottawa 2 2010-01-07 North Bay 1 2010-01-07 Toronto 2010-01-07 Ottawa 2 2010-01-04 Toronto 2 So my ocunt would say there are 2 appointments in Toronto during the month od January 2010?. QUESTION: I've been trying to write a formula that checks Column A for a(Match) then checks Column B for a second(Finalised) and sums all these as a total e.g. Column A Column B Match Finalised Expire Finalised Match Diarised If I was looking to see how many "Match" were "Finalised" this should return a value of 1. Can you help? ANSWER: =sumproduct(N(A1:A100="Match"),N(B1:B100="Finalised")) ---------- FOLLOW-UP ---------- QUESTION: Thanks for this. I have created a separate table to show the data on the same sheet and copied the formula onto it but it is returning "0". Any ideas? i have onefile with 50 sheets.these sheets are same format means if the sheet1 cell C10 contains the Shope name then all the 50 sheets contains different shope name in C10 cell.im trying to make one report based on this file.i need column A as Shope name,.....im using this formula (=Sheet1!$C$10).when i m draging down then it should change sheet number means next cell it will show (=Sheet2!$C$10).is it possible???? can u help me.... thanks bindhya In column A I have a student Id#. In column B I have a semster 1 and/or 2. So column A should have the same student ID# twice (once for semster 1 and once for semster 2). In column C is the Homeroom teacher name (or code). Is there a way to find if a student in column A has two different Homeroom teachers in column C? They should look like this (except student 232 has a different homeroom teacher for semester 2). Is there a formula for finding these students? A B C 222 1 704 222 2 704 423 1 708 423 2 708 232 1 801 232 2 805 I need assistance on anequation that applies to a Hotel. There are 3 columns. Column A is the resort #. Column B is the room code. Column C is the room status. I am looking for an equation that shows: Given any two rows, if Column A is 5 for both rows, Column B is 15 for the top row and 14 for the botand Column C is 1 for both rows, than the answer would be 1. It is hard to explain so please see attached and let me know if you have any questions. I really appreciate the assistance. Brian QUESTION: I am putting together a time sheet. This will log time spent per project. Since the start time can be a higher or lower number than the end time the formula must accommodate that change so that the answer doesn't change from a positive # to a negative #. ANSWER: Glad to help. You can set up an absolute number formula. Let's say you have your dates on cells A1 and B1. Then you formula will be: =ABS(A1-B1). ---------- FOLLOW-UP ---------- QUESTION: Used your suggestion but the answer came our wrong. Here is my document. TOTAL START TIME END TIME HOURS 9 11 2.00 11 1 10.00 11 O'clock to 1 o'clock whould be 2 hrs. not 10. In my spreadsheet i mark off who i paid by highlighting the cell (payment amount). Is there a formula that can check which cells are highlighted and then totals that amount and then minuses it off another total. I would like to see how much i still need to payout. Also, is there a formula that looks at a cell value if the value is zero it would automatically hide the entire row. In column A I have a list of numbers. In column B I would like a specific code to appear based on the data in column A. E.g if the number in column A1 is between 9 - 16 I would like to produce the symbol 3H to appear in column B10. I am not sure if their is a formula that will do that for me. Any ideas. In column A I have a list of numbers. In column B I would like a specific code to appear based on the data in column A. E.g if the number in column A1 is between 9 - 16 I would like to produce the symbol 3H to appear in column B10. I am not sure if their is a formula that will do that for me. Any ideas. In column A I have a list of numbers. In column B I would like a specific code to appear based on the data in column A. E.g if the number in column A1 is between 9 - 16 I would like to produce the symbol 3H to appear in column B10. I am not sure if their is a formula that will do that for me. Any ideas. Thanks Chris Ok i think i'm with you Is the below possible then? Sales: 2000 Units 30 0 .15 .20 Sorry had to do a new question as it wouldn't let me do anymore replies Dan QUESTION: Hello there, I've been trying for some time to get this IF formula to work but to no success, please can you help: 1st Target (Sales) Less than ?1000 = No Bonus between ?1000 and ?2000 = 15% bonus more than ?2000 = 20% bonus Ok thats the easy part 2nd Target (Units) More than or equal to 20 Ok the formula needs to check two cells and if in one of them the units are 20 or more and in the other the spend is ?1000 or more then the sales bonus structure kicks in. Fingers crossed you can help I look forward to your reply Many thanks Dan ANSWER: Dan First formula =if(sales =20,firstformula,0) so =if(units>=20,if(sales 2000, but units between 20 and 30 = no bonus or you have not defined or allowed for a bonus =IF(OR(Sales =1000,Sales =20,Units 2000,Units>30),0.2,0))) ---------- FOLLOW-UP ---------- QUESTION: Ah yes so i have I would like it to show the 15% bonus if they do not meet both criteria to achieve the 20% (?2000 & 30units) would that be the same as what you have given me? thanks Dan I have following problem: there is a table with following formula in space for month dates: =(IF(DATE($B$4,$D$4,COLUMN()-1) I am trying to write a formula that will count the number of times a certain text appears in a list between two dates. For example, the number of times that "MS Level 1" appears in Column I between the dates of 12/1/09 and 12/31/09. The dates for each record are located in column E. I would appreciate any help!! Kevin

• Answer:

  Not clear what the question is - are you looking for the number of years between two dates? If so, the quickest formula is probably the YEARFRAC one which returns a decimal fraction of time between two dates (so one and a half years returns 1.5) =yearfrac(StartDate,EndDate) In older versions ofthis would need the analyis toolpak to be installed (tools, add-ins), and you PROBABLY want to only show the whole number part, so =Int(yearfrac(StartDate,EndDate)) would give you this. Answer should be 36 minutes. Whenever you are typing Time value don't type the am and pm as a.m and p.m like that type it as am and pm ok. That is you should type it as 12:48 and type a space and type am. 12:48 am or 12:48 Pm like that. Select the Column A and format it as Time. In A1 cell type the below 12:48 am Copy the below formula and paste it in A2 cell =TIME(HOUR(A1),MINUTE(A1)+36,SECOND(A1)) OR =IF(OR(A1="",ISERROR(TIME(HOUR(A1),MINUTE(A1)+36,SECOND(A1)))),"",TIME(HOUR(A1),MINUTE(A1)+36,SECOND(A1))) Place the cursor in cell A2 and extend the selection below upto a10 by holding shift+down arrow and press Cntrl+D now the time will be applied by adding 36 minutes in every cell based on the above cell time. Hope it's clear to you! benjamin, There is no built in function that will read the background color of a cell. Such a function can be crafted in VBA and then used like a regular worksheet function. This would be fairly simple as far as VBA is concerned - but you may not want to use VBA in your workbook because of security warnings and so forth. so you could put this in a general module in the VBE Alt+F11 to get to the visual basic editor (vBE) in the Menu there, Insert=>Module. this gives you a general module. Put in code like this Public Function ColorIndex(r As Range) Application.Volatile ColorIndex = r(1).Interior.ColorIndex End Function now do Alt+F11 to get back to Excel. in a cell put in =Colorindex(A1) now change the background color of A1 and hit F9 to force a calculation. (changing the background color does not causeto recalculate) -4142 would indicate that the cell has no background color (it has no fill) otherwise, you should get a number between 1 and 56. in Excel 2007 and later,can handle a million colors in the cell background. So many colors can be mapped to the same color index. If you need more information on this, let me know. If the color in the background is caused by conditional formatting, then even VBA has no support for reading that color. It is best to use the conditions/formula specified in the conditional formatting rather than try to read the color. in sheet "over due", cell I2 (leave I1 blank; and when done, hide column I): =MATCH("overdue",OFFSET('2010'!$L$1,I1,0,1000,1),0)+I1 and fill down quite far Hide it so the #N/A's don't show in cell A2: =IF(ISNA(I2),"",INDEX('2010'!A:A,$I2)) B2: =IF(ISNA(I2),"",INDEX('2010'!B:B,$I2)) C2: =IF(ISNA(I2),"",INDEX('2010'!D:D,$I2)) D2: =IF(ISNA(I2),"",INDEX('2010'!E:E,$I2)) E2: =IF(ISNA(I2),"",INDEX('2010'!G:G,$I2)) fill A2:E2 down as far as you want. Row 1 can contain the headers. HTH Jordon I don't have quite enough info to give you an exact formula but I will suggest what you may do. I believe, based on what you have given me, you can use the sumif formula Assuming your data is in columns A thru D and in rows 2 thru row 100. The formula might look like this =+SUMIF(A2:D100,"john doe",D2:D100) The formula says go to the range A2 thru D100, and if column A contains John Doe, then add up the amounts from column D. Using the data from your example the result would be 33 (15+12+6). Hope this is what you needed Just try taking sumproduct of three entities.... in D2 you enter desired month (say 1) in E2 you enter desired city (Say Toronto) in F2 you enter desired Code (Say 1) Then to get count of Toronto in Month 01, you enter following formula in G2 =SUMPRODUCT((MONTH(A:A)=D2)*(B:B=E2)*(c:c=F2) send me the wb - [email protected] - use subject of "AllExpertsQ" You can use the INDIRECT formula and a helper columns for this. In the helper column type "Sheet1", and then drag it down so it becomes Sheet2 to Sheet50. Now, next to each cell on that range you can use a formula like: =INDIRECT(A1&"!$C$10) This should give you the Shope number from the corresponding sheet. Sure. You could use the COUNTIF function to determine how many homeroom teachers each student has. You can use the SUMIF and COUNTIF functions to add up or count values based on specific criteria, respectively. Here is a free tutorial that explains how to use both: http://www.599cd.com/tips/excel/sumif-countif?key=AllExperts the only way I get it is if you include the following formula con cell D2 =IF(AND(C2=C1,A2=A1,B1=15,B2=14),C2,0) Drag it over the rest of the cells and then this formula on cell J16 =SUMIF(A:A,10,D:D) and change it depending on the resort # I'm sorry. I misunderstood your question the first time. Now. I can advice yoo to use dates and time formats such as dd/mm/yy hh:mm am/pm and then you'll never get a wrong result. Still, If you want to go ahead with your current format you can use a conditional formula such like this =IF((B2-A2) =9,A1 =9,A1 =9,A1 or(units and the highest bonus with And(units>30,sales>30) which leaves everything else at a bonus of .15. The formula for that is: =if(or(units 30,Sales>2000),.20,.15)) Dan, No, because you have two dimensions. In a normal single dimension IF construct, you can progress across the the scale and if you meet a criteria, you know you are there because you have failed to meet lower criteria. =if(sales 2000 Units 30 0 0 .20 this is where you are right now: =IF(OR(Sales =1000,Sales =20,Units 2000,IF(Units>30,0.2,0.15),0))) When you see ###### in a cell in Excel, that usually means that the COLUMN is not WIDE enough to show all of the data. Try increasing the width of the column and see if that helps. As far as your formula goes, I really couldn't test it without seeing your sheet. Scott, There are basically 3 parts of the existing equation. The first part divides the row number by 36^2 or 1296. The second part divides the row by 36^1 (36) and the last part doesn't specify that a division occurs, but essentially it divides the row by 36^0 or 1. To add a fourth character, just follow the pattern and precede the first three parts with 1 that divides to row by 36^3 or 46656: =CHAR(INDIRECT("A"&MOD(INT((ROW()-1)/46656),36)+1))&CHAR(INDIRECT("A"&MOD(INT((ROW()-1)/1296),36)+1))&CHAR(INDIRECT("A"&MOD(INT((ROW()-1)/36),36)+1))&CHAR(INDIRECT("A"&MOD(ROW()-1,36)+1)) The problem that you will run into is that 4 characters will give 1,679,616 possible combinations. The latest version ofonly has 1,048,576 rows, so the highest combination shown will be MH33. Previous versions ofonly have 65,536 rows so the highest combination shown will be 1EKF. If you need to show all of the possible combinations on one worksheet put the following formula in Cell B1, then copy it and paste it in the range B1:AK46656: =CHAR(INDIRECT("A"&MOD(INT((COLUMN()-2)),36)+1))&CHAR(INDIRECT("A"&MOD(INT((ROW()-1)/1296),36)+1))&CHAR(INDIRECT("A"&MOD(INT((ROW()-1)/36),36)+1))&CHAR(INDIRECT("A"&MOD(ROW()-1,36)+1)) Best Chad Welch =SUMPRODUCT((E1:E1000>=DATE(2009,12,1))*(E1:E1000 =DATE(2009,12,1))*(E1:E1000 =DATE(2009,12,1))*(E:E =DATE(2009,12,1))*(E:E<=DATE(2009,12,31))*--(I:I="MS Level 1"))