Add Another Variable Into Existing SUM (COUNTIFS) Formula
Jul 23, 2013
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 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 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 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!
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.
Using COUNTIF/COUNTIFS how to counting data with 3 mode ;
name property checking
[Code]....
I want to count with criteria based on adjacent value "name" column related with "checking" column
1) counting data "name" with "yes" criteria? 2) counting data "name" with "yes" & "no" criteria? 3) counting data "name" with blank "" criteria? 4) counting data "property" with criteria contains "name" and "yes" criteria
The attached file Countifs issue.xlsx shows my attempt to construct a "CountIfs" formula that finds the one record out of ten that matches the following criteria:
a) Col A has 10 IDs. 3 of them are between 40 - 49, and another 2 are alphanumeric. This formula SHOULD identify these 5:
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 have an existing formula in a cell linking back to a start date only. determining how to add an additional formula to exclude weekends, and possibly holidays.
I am trying to count how many work orders we have created from 3 months (90 days) ago. In an effort to make the input work minimal. I wanted to use the =Today() formula and then subtract 3 months.
So I currently have =Today() in A1 and then (A1-90) in cell B1 and I get the date I want.
However, When I use a countifs formula and my criteria is
This task joins a string together based on a number of characters per cell in the range.
I want to isolate one range, Col N, and add an IF condition to it.
There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference....
I need a macro in a workbook to look at all the files in the same folder that have "*att*.xls" in the name and determine and copy from the range A15:W515 only the rows that have data in at least columns A, C and D. Each file will vary as to how many rows there will be and there are more than the files with "*att*.xls" in the folder. The data will be on the only worksheet in each file and the worksheet is named "G2WAttendee_xls" the data from all the files need to be copied to the file called "Consolidated webinar reports.xls" (I am using Excel 2003) and to a sheet called "Attendance Data" and added to the end of the last paste.
At the start of the macro the current file "Consolidated webinar report.xls" should be saved to a sub folder of the current directory and have the date saved added to the name. The sub folder is called "Completed reports". The data in the original file on worksheet "Attendance Data" should be deleted.
At the end of the process all the files that have had data copied from them should be moved to the sub folder "Attendance reports consolidated" (This could be done as each file is closed if that is easier).
I have headings in row 1 of the "Attendance Data" worksheet that match the headings in the various files in the folder (which will always be in row 14 of the individual "*att*.xls" files).
The folder with all the files and the "Consolidated webinar report.xls" file is at path "Z:P and S MEvaluationsWebinar series 2012-13TB".
In my attached spreadsheet example below, in the Rollup Data sheet, I have a formula (Cell: C17) that will give me the actual cost totals that has been invoiced on WBS Element (DWRRI-BW096-231) for a total of $35,004.81.
What I need the formula do is run a redundant check procedure on my new WBS Element (DWRRI-BW096-231) actual cost total formula in the Rollup Data sheet (Cell: C17) against/vs. what the SAP system provides in the raw SAP Excel export contained in the PTD-Actual Cost sheet for the WBS Element (DWRRI-BW096-231) actual cost total of $35,004.81 (Cell: F9) that's highlighted in the yellow row.
In the Rollup Data sheet (Cell: C18), I need this formula to perform a similar function the as the previous formula above it (Cell: C17), but it this formulas function (Cell: C18) would have a separate operation that would only look for and return the single/sole WBS Element (DWRRI-BW096-231) actual cost total contained in the PTD-Actual Cost sheet (Cell: F9).
In the Rollup Data sheet I could then compare my two WBS Element (DWRRI-BW096-231) actual costs totals, the first from the automated addition of all the individual matching WBS Element actual costs into an actual cost total (Cell: C17), the second (new formula I'm requesting) from the matching and return function of the single/sole WBS Element actual cost total (Cell: C18).
So, if the two separate WBS Element actual cost totals (Cells: C17 vs. C18) both equal $35,004.81, the formula in C17 is a success and has correctly identified and added all the actual costs contained in the PTD-Actual Cost sheet, but if the two separate WBS Element actual cost totals (Cells: C17 vs. C18) both do not equal $35,004.81, the formula in C17 failed for some reason and has not correctly identified and added all the required actual costs contained in the PTD-Actual Cost sheet.
How to Add Total $ Value Formula into an Existing SUMIFS Formula.xlsx
I currently use the following formula: =workday(H21,H23,J20:J34) which calculates my date based the date entered in cell H21. While testing I realized that I needed to add another variable to the formula for a specific scenario. When my Date in H21 is less than todays date I require another workday formula to be calculated. How can I add this if statement to the existing formula?
I'm trying to add either a new condition to a formula or modify existing formula, so blank cells can't be taken as a number like 0 Here is the formula =IF(N14>0,1,0)
***This is what I do with formulas with similar problems =IF(COUNT(Y14,A14)=2,Y14/A14*100,"")
unfortunately the above condition its already a condition by itself so I don't know what to do whether to modify condition or add a new condition which I don't know how to do.
I have a very simple lookup formula (eg: ='data'!C1) that I need to copy down about 4000 rows. The difficulty is that I need to miss out 5 rows in between each paste but no matter what I try when copying it down, it throws itself out of numerical order. I have tried to type the formula manually for every row but its taking forever and is bound to go wrong at some point. I have attached a very small example to show what I mean - Imagin this example needing the formula to run down in order up to row 4000?
I have lots of data in the form of names, and i want to extract all rows of data for that name. So if name "john smith" appears in column A 7 times, i retreice 7 rows of data.
This works perfectly and it gives me my 7 rows of data if i type in "john Smith" into A10, however, i want one more condition to be met in column C, whereby Column C is made up of either 1, or 0. If its a 0 i am not interested, if it is a 1, i am.
I need to replace the values in the braces with the values in Column A. Is there an easy way to do this or am I stuck with doing each one manually? There are 92 lines in Column A.....
I am trying to write a macro that should be able to copy existing row and insert values and formula into a new row just below the row. As an example below:
A B C D E
001 ADESF 500.00 1001210 EMF
The values in column C and D for the new row will be the same.
I'm preparing Quarterly Evaluations, and am having an issue because an Analyst has superseded an overall percentage goal. In the attached example, it is the overall evaluation with the 'problem data' in red D19 and highlighted.
I'd like to add a condition that limits the points in D19 for 'responsibility 3' to 150.
=IF(ISERROR(C19/B19)*(B10),"-",(C19/B19)*(B10))
*just a note for clarification, the other two responsibilities are a 'per hour' weekly goal, whereas the 3rd is an overall personal effort divided from the overall team effort.
