i have my data layed out as per below, is there a dynamic formula which can be used for each bank to retreive the the count against each criteria, so if there are more rec breaks for any of the banks the formula will adjust accordingly ....
1: NAME 2: DESCRIPTION 3: DATE "12/15/2007" format
I would like to know if anyone knows how I can perform the following. And please excuse me for not writing this very technically I am a bit lost.
if name = "JOHN" and description = "APPLE" count how many apples there are for john.
Now it gets complicated for me because the dates are as follows. Lets say it starts 1/1/2004 the next cell might be 1/3/2204, 1/6/2204, etc all the way to 2008. I would like to find a way to say
If date = day/month/year I would like to count the whole month instead of the individual days. am I making sence? Let me give you another example.
I have attached a spreadsheet with a small indicative data set to assist in understanding. I am trying to count the number of documents each individual has assigned to them that are not yet 'completed' (ie REGISTERED, IN WORK, REVIEWED). The problem I am trying to overcome is that the document state can be 1 of several values indicated in the same column.
I have tried using this SUMPRODUCT formula: =SUMPRODUCT((($E$2:$E$11="REGISTERED")+($E$2:$E$11="IN WORK")+($E$2:$E$11="REVIEWED")*($B$2:$B$11="Jones"))) but it is generating incorrect values!
Specifically: - Jones shoulld return 1 - Franks should return 3 - Smith shoudl return 0
I am trying to count the number of rows that have values greater than 10/01/2008 in either of two fields. I tried following formula but instead of giving total number of rows, it returns a random date.
I have been trying without success to use Vlookup to lookup a value in one column, say the number 15. 15 in this column will fill cells in several rows as it refers to several different values in the second column. The second column only has one instance of each value, for example 42. Then with this information I have been trying to get Excel to retrieve a third value and return it to the cell from which I am entering
the formula......An example... 15............. 42 ................ yellow 15............. 42.5 ............... blue 15............. 43 ............... red 16............. 42................... purple 16............. 42.5................. green 16............. 43.................... pink
Is this asking too much of VBA? I don`t think so but cannot figure out how to properly enter a formula that will do this. I have tried Vlookup several different ways with no success. If I could get it to return the cell reference instead of the cell value on the first lookup excersize I could then make the second vlookup start at this cell reference to get the third number but I can`t even figure out how to do that.
I have numbers in range B1 to B100. How do I search for the first negative number (not the smallest negative number) within the range? Then, retrieve the Date indicated along A1 to A100?
I spent couple of hours trying to figure this out, but without success. Please check the attached example. I need a formula that will find the max value (number of views in English anguage) in the column "I" and display the corresponding "Headline" value in the column "B". In the example it is "Headline 5".
I can not change position of the columns, i.e. "B" must remain "B" and "I" must remain "I". I experimented with Index and vlookup but to no success. I have outlook 2003.
I'm trying to get the count of all cells in column F of one workbook that have a value of "RES" or "RET" and put that count in cell B2 of another workbook. At first I tried COUNTIF but quickly realized that it doesn't work when the other workbook is closed. I tried this, but it gives a REF! error:
The workbook and worksheet referred to definitely exist in the directory in the path, and the F column has a mix of values including the two I'm looking for. Once I have a working formula, it will ultimately be populated into a large range of cells by a VBA sub (that part I can do ).
I should be able to do this by now but I keep getting an #NA error. I am trying to use this sumproduct formula =SUMPRODUCT(($I$7:$I$2585=921),--($L$7:$L$2585="Fb")--(AS7:AS2585)) where I am looking up store #"921" and Attribute "fb and sum where these two criteria meet. The column I want to sum is AS7:As285. What am I doing wrong.
In Column A1:A20, these are included 5 types of data (red, orange, yellow, green, blue); in column B1:B20, these are included the total salary amount, and in column C1:C:20, these are included "Sole" , "Joint" or "Partnership", so if column A is included red/yellow/green (excluding orange and blue) and column C is included Sole/joint/Partenship, and total salary is between 6000 and 9000, then calculate the total salary of all the customers. How can I use sumproduct function to fix it?
I'd like to use a sumproduct function to count 2 conditions. I want to add the number of times the number 0 is entered in Column D when a 1 is entered in the same row within Column C next to it.
I'm using the formula below yet its wrong.... it gives the answer of 7 rather than 1 (see data in attached file).
The below formula is a small piece of a formula but it's where it's causing the error to occur. When I'm trying to count the months within the SUMPRODUCT formula it works fine, but when there is a "TOTAL" it's returning an error.
*ABC53TypeCost$Bought54Orange3Friday55Orange5Sunday56Apple4Friday57Orange5Friday58Orange4Sunday59Apple4Friday60Banana3Tuesday61Orange4Sunday Excel tables to the web >> Excel Jeanie HTML 4
Now, i want the Sum and Count of Orange which is bought on Friday
The problem facing by me that I have a worksheet in which I count some values through sumproduct function in vba but its not working but if i manually put in this in sheet it works.here is the code.
Dim Sal As Workbook Dim rng As Range Dim rng1 As Range Dim Dept As Range Dim Dept1 As Range Dim rg As Range Dim i As Byte
Sub salries() Application.DisplayAlerts = False On Error Resume Next Set Con = Workbooks("Branch Wise Deparment Wise No. of Staff.xls") Set Sal = Workbooks("salarysheet.xls") Sal.Activate Sheets("Working").Delete Sheets("GT").Activate Range("B3").Select Set rng = Range(ActiveCell, Selection.End(xlDown))..............
I am trying to match 3 values, and return a matching 4th from a chart. It will first look at one value and see if it falls between values (the LOAD value in cell B1..it will look to see if it falls between the "low limit" and "high limit" in columns A10 to A135 and C10 to C135, respectively. Then it will look for a match for the values in cell A2, and in cell A3. the return value will index to the corresponding value in column G10 to G135. I have written a formula to do this in cell D2....but it will only ever return the value in cell G10.
I am looking around any way wherein I can sumproduct the values as given in attached sheet, basically I wanted to know the total MRP value of Sale and Stock
I'm working on a spreadsheet at work and I'm trying to sum only the values in a column that meet criteria in the column next to it. For example, in the attached spreadsheet, I would like to have a totals line at the bottom of the spreadsheet for all three Facilities and the total next to them. I know I could do a pivot table but I know this approach would be more aesthetic and easier for my supervisor.
I am stuck - I have a large amount of data for a group of physicians I work for. I am trying to set up a monthly trend report to be able to run quickly after I plug in the data. I want to use some sort of lookup to look up two things - 1) the physician's specialty and 2) the month.
Can anyone look at the attached example and tell me how to do this? I have started a SUMPRODUCT formula, but am stuck on how to tell it to find only that month's data.
I have the following sumproduct formula that's providing solid results but I would like to add a 3rd column of criteria. I'v tired with little succes.
The following formula <=SUMPRODUCT(('IW 38 DUMP for Planning'!$A$1:$A$10000="2A")*('IW 38 DUMP for Planning'!$E1:$E10000={"PAA","RS","RSNR","S","SAM","SAMT","SAO","SAT","SOR","WKS"}))> totals all of the work in plant area "2A", in this case 52 records. I would like it to filter further with values in $H1:$H1000 matching criteria "CONTRACT", "MACH" OR "HTSMET".
The data is easy to find with pivot tables but I would like to take that manual step out of the reporting being doen from these records.
I was wondering if I could use a range of cells as my criteria as opposed to inserting quotation marks with each criteria. For example in the syntax below can I do something like this? Report!$C$3:$C$5000=B45:B51?
I have the following sumproduct formula which looks in one column to search for a series of dates (greater than one date and less than another date). It then looks in column O to see if there are certain criteria if the date criteria is met. The problem is I have a lot of blank cells in column O which need to be captured.
I'm trying to create a budget worksheet that pulls actual data from another sheet within the file for comparison (Budget vs. Actual). There are two criteria: 1) the actual transaction falls into the same category of transaction as the budget line item (e.g., mortgage payment) and 2) the date of the actual transaction matches the month in the budget (e.g., a January or March transaction isn't pulled into the actual data for February budget information). From there, I'd like it to sum any charges or reduce by any deposits for those given criteria.
I've tried numerous things from DSUM, to SUMIF with IF, to SUMPRODUCT.
I received an answer to my original question and now have a new question but I wanted to reference my original for the history. I posted my new question at the end of my original thread.