Countifs Formula Not Working When Referenced To Another Worksheet?
Mar 17, 2014
I have attached a sample workbook which shows what I am trying to extract from sheet "Service Reminders 2014". In worksheet "Results" cells b11:e11 I am trying to extract the amount of vehicles with within age ranges provided that have a magic number attached which is pretty easy everything >0 is a proper magic number and also the amount of vehicles in each age range that have "Booked" associated within the range of "Service Reminders" Y2:AH5000, The formula works fine until I add the final criteria and then it give me a #VALUE!
Because countif cannot be used across multiple tabs, I'm using the following workaround where "MySheets" is the range of tabs and E8 is the cell I want "counted".
This works perfectly, except that I really need COUNTIFS... I want Excel to count either "Fully implemented" or "Partially implemented" when in E8. I cannot get the formula to work and keep getting an error message.
I'm working on a sheet which references a sheet in another workbook. I want to refer to the other workbook simply by it's filename (Forecast.xls) but whenever I save and reopen Excel has replaced the filename with the full path (C:Documents and SettingskoconnorMy DocumentsAuto-sheet project[Forecast.xls])
This is quite inconvinient as I am eventually going to want to move both workbooks together to a different location. Is there a way to make Excel stop working out full file paths and just look for the filename in whichever folder the active sheet is sitting in?
In the Formula below I am attempting count records that are not "Closed" or "Cancelled" But records with those two status values are still being counted I have a feeling that my attempt to create a "not equal to" condition may be the problem, even though Excel did not error when I put in this formula. I am certain that the worksheet and columns that these conditions are pointing to are correct. Note that the first 2 conditions are working correctly.
I am unable to get the correct results using Sumifs and Countifs function. Below is an example:
If I use the formula as =IF(ISERROR(SUMIFS(F2:F8,B2:B8,"Africa",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"2B",E2:E8,"SY")/F9),"N/A",SUMIFS(F2:F8,B2:B8,"Africa",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"2B",E2:E8,"SY")/F9)
I get the result as 0 but the result i want is 19.67% (1234 /6275). Since i have multiple criterias which might not be available at times in the data, i get the value as 0.
Similarly i have used a formula using Countifs; =IF(iserror(countifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=3C")+counifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=2B")/F9),"N/A",countifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=3C")+counifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=2B")/F9)
And even the above formula does not work with the criterias given. What i require is, if the country is "Austria", "External", and "NoneExt" then count those lines which has product Type as "3C", 2B". So the result for the above should be 1.
Request you to kindly let me know the correct way or the right formula to extract data for the above criterias.
I have a monitoring database and I want to create a 'source' sheet in sheet 1 whereby when I enter names into a certain column they rename different tabs/sheets in the worksheet. for example, the name 'Brown' inputted in cell 'A2' would rename sheet 2, Black in A3 would rename sheet 3 etc.
The formula itself seems to be working alright. It's on the post tab in cell R74. I modified it to temporarily to sum up a random group of cells, with half of them red and it worked correctly. The problem seems to be in the time calculation? Jennifer has 54.25 hours. The hours on Monday were coded in red and were not in the same group as the rest of her hours. So I need to extract the 9.5 hours from Monday, coded in red.
Unfortunately it comes back with a value of 31.5 hours. I see now how its getting that. The numeric values of cell C74 and D74 equal 1.3125 which is equal to 31.5 hours. But I'm not really sure how to go about calculating it the correct way. The worksheet can be viewed at: http://www.buckeyeperformancehorses....HEDULECODE.xls
I am creating an Order form for my job in Excel. On this form, it displays the Top 100 items for sale. All the basic functionality of the form is working fine, my real issue is with the order summary tab.
On this tab, I would like for any Items with a Quantity on the first Sheet to transfer to a summary on the Second sheet.
So far, I am able to get the data onto the second spreadsheet. My final question is, can make the items on the summary sheet go to the top available row? Basically if I have items in row 3, row 7 and row 9 - I would like for the items to automatically fill in rows 3,4,5 respectively. (on the second worksheet)
I have seen people make this happen (on this board even) but for some reason, when I try using those formulas I do not get the results I need. I have attached the file to this post.
On the Tab entitled "Adv Piano" this the product list. On that tab, customers can put a Quantity in column G for the items they want to order and that item's details, price, and quantity will automatically appear on the 2nd tab, "Order Details" in columns I - L in the corresponding row (this is already done)
I need the information in the columns I - L on the "Order Details" sheet to appear in columns C - G on the same sheet, in the same order but to fill in towards the top with the corresponding quantities, item details and price remaining in the same row.
Upon opening after "Enable" is selected the workbook attempts to locate several nonexistent pieces of data, either internet based files or network based files. Requested data appears to be about 11 years old and would not be applicable it located.
Edit Links shows the location of the requested files, i.e., E:filename but does not show the location within the document that causes this request. A search for "E:" does not locate text in any worksheets.
The question is how to delete or turn off this problem which slows opening, saving, and recalculation of a large multiple worksheet workbook.
In H1:H4 I have 4 different functions. When the user specifies which function to use in E1 (add, subtract, multiply, or divide), I would like C1:C4 to evaluate the values in Columns A & B.
My problem is that the formula only uses the values in A1 and B1. When I copy the formula down the column, it still evaluates for the values in A1 and B1, not in A2 and B2, A3 and B3, etc...
My problem is that when i used the forumla = SUM(Q11:AW11) to add up the range of cells it dosnt do it. It only added up 2 of the numbers in the range of cells. And missed out everything else. This forumla is being used to add up all the volunteer hours for volunteers for each month and for all the other months the formula worked fine but now its just being a pain.
I have a formula that looks up a chart showing scores referenced against each other as shown below. (Scaled down version) ******** ******************** ************************************************************************>Microsoft Excel - Table Template.xls___Running: 11.0 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1B1C1D1E1= ABCDE12006-2007Altofts JnrsCarleton JnrsEmpire ColtsHemsworth Terriers2Altofts Jnrs- 3Carleton Jnrs12-3-4-0 4Empire Colts - 5Hemsworth Terriers -Results [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
The problem is when the score is i.e. 12-3 the formula is only looking at 2-3. Hence this actually then shows as a defeat. The formula causing this problen is an array and shown as =SUM(IF(NOT(ISERROR(LEFT(resultsHome,FIND("-",resultsHome)-1))), --(LEFT(Results!$B3:$K3,FIND("-",Results!$B3:$K3)-1)>MID(Results!$B3:$K3,FIND("-",Results!$B3:$K3)+1,9)))) Question. How can the formula be changed to reflect scores like 12-3 to be shown as a home win............. .
I have a spreadsheet with a number of different formulae on it. I have a macro which I want to extract which cells are referenced in the formula in the active cell and then branch based on the which cell is referenced.
Is there a way to find the referenced cells without parsing the formula string?
I have an IF formula on one worksheet that refers to another worksheet (ex: =IF(Page1!$A13="";"";Page1!$A13). The problem is when I move data from one cell to another the formula becomes #REF. (ex: =IF(Page1!#REF="";"";Page1!#REF)).
I am trying to reference a date in a formula that points to a sheet name.
See Below.
A B November 11, 2005 ='11-11-05'!$v$35
How can I automate the date to pull from column A and place that value in the date for column B? Column B is the name of a different sheet in the workbook.
I'm trying to create a list that references an existing data set where I have staff listed month by month and based on today's date, imports only if there is data in that column. I have figured out how to check if the cell is blank or not, but what I want to do now is change the cell that is referenced in the formula based on the date. Here is the format of the spreadsheet I'm working with:
A B C D E F G H
[code].....
In this case, the "Team 1" and "Team 2" and "Team 3" references what team they are on that month. If it is blank, they aren't with the company any longer. The formula I am using is intended to import this data elsewhere, and is formatted like this:
=IF(ISBLANK(B2), "", A2) - My understanding is that this checks to see if B2 has data, and if it does, it inputs the employee name (A3) in that cell.
My ultimate goal is to be able to change the column referenced after the "isblank" calculation based on the date. So if today is April 2013, I want it to check B2, but if it's December 2013, I want the formula to check J2. Is there a way to do this? I don't mind if it's two steps (like if I have to put the date somewhere in the spreadsheet in order to run the calculation), but ultimately it would be the type of thing I could do that would leverage the existing data set so that I don't have to maintain two different spreadsheets of information.
I am after an automatic formula or function which calculates values for long lists instead of me dragging down the formula all the way to the end of the document.
Hence, I have a long list of data in columns A, B&C and I want the formula in column D to automatically be calculated all the way down when the list stops.
I have 2 tables, 1 beneath the other - table 1 columns represent date ranges and their values. Columns and their data are alternating color coding. Table 2 references, by means of array formula, this data - IS it possible to include the font color as a result?
I have a problem with a cumulative countif and offset formula that I am unable to solve. See the attached spreadsheet for an example of the dataset I get out from our systems.
I want the formula to be fully automatic, so when I change the month in column K2, it will automatically count all the different outlets in column B based on criteria in column L3 and downwards. The dataset will be updated monthly obviously.
I believe that it could be solved with the usage of a countifs + offset formula or something similar.
The main idea of the spreadsheet is to track the movement of customers between different chains. It is thus important that customers that show up in January will not be counted again in February or March, thus creating an unique countif list based on a given month.
I am having trouble converting the following Countifs formula developed in 2007 to work in 2003 Excel.
Basically, I want to count the frequency of "N" in 'Data_Q2 2008'!$S$2:$S$9806, where 'Data_Q2 2008'!$B$2:$B$9806 = 'Metric Upload'!C2.
'Data_Q2 2008'!$B$2:$B$9806 is a list of physicians. 'Metric Upload'!C2 is a pick list for selecting the physican that is in the larger data set using Data Validation.
I currently have 6 formula (see below) that I want to add another variable for. The formulae add together letters in B1:B15 cross referenced against numbers in A1:A15. The first formula adds together the A's and O's in B1:B15 and cross references them with with cells beginning with 5 or 6 in the same row, A1:A15 down.
I want to cross reference each of these with a Gender variable (denoted as M or F, for male & female) in the C1:C15 range. Using the last formula as an example should tell me how many M's or F's in the range C1:C15 had B and began with either 1,2 or 3.
I have a query from an access DB that I pull into an excel sheet automatically every 15mins. Column G contains a list of 6 digit customer reference numbers and column C contains a list of the users that have modified records for that customer.
I use a summary sheet to count the number of customers a user has modified records for in that day. The trouble I am having is counting unique records using the COUNTIFS function. In my database if a user changes a record and hits save 10 times that will create 10 records. I only want to count this as 1. I am using the following formula but it doesn't seem to like the 2nd range and criteria in the countifs bit.
=SUM(IF(COUNTIFS('Daily Loss Data'!G2:G1500,'Daily Loss Data'!G2:G1500,'Daily Loss Data'!C2:C1500,"John Smith*") =0, "", 1/COUNTIFS('Daily Loss Data'!G2:G1500,'Daily Loss Data'!G2:G1500,'Daily Loss Data'!C2:C1500,"John Smith*")))
Daily Loss Data Sheet Notes: Column C = user, Column G = Customer Ref.
I am using an IF statement because my range changes after each query refresh so contains zero's that I obviously do not want to count.
Have table with five columns and five rows. The columns(C4:H4) hold Systems, Systems is a defined range, and rows (B5:B9) hold staffer names, Staffer is a defined range. Cell A5 holds period, by quarters, ie, Q1-Q4.
At present am using =COUNTIFS(Staffer,$B$5,System,C4,Q,$A$5). This works fine but as you can see I must change the Staffer starting reference $B$5, with each new row.
formula that changes column and row automatically as I drag down and across.
I know this should be straightforward, but just can't "hack the logic", whether using "Countifs" oor "SumProduct" formulae.
Attached file 140509 String Criteria.xlsx file has database of three fields (Date, Route number and Part number)
I need the formula that totals number of rows meeting three criteria: (a) A specific date from Col A (b) A specific number from Col B And the part I can't sort out: (c) Any cell containing a "string" in Col C (so needs to count the "core" even if it has a prefix or suffix).
E.g. How many rows have "Date x" in Col A, "Route Y" in Col B and a cell containing "123" in Col C?
I have a large metrics spreadsheet that has thousands of formulas using SUMPRODUCT. We are using Excel 2007/2010 now and I wondered if there was a way to convert these type of formulas to use the SUMIFS or COUNTIFS. I can do simple ones with 1 or 2 criteria where its AND, but then there is a combined OR and AND like this one has I cannot get them to work right, am I stuck with using SUMPRODUCT? My hope was to speed up the overall calculation speed. Note: The data spreadsheet 'Sheet2' contains 64555 rows, I write these formulas using VBA so that the range is finite to the actual data range is why you see 64555.
I have attendance tables that has column Y showing the shift to be worked and column Z showing any absentees. I used the following formula to count the number of people off per period.
