I've got no clue about all this, but I've had to get specific formula examples and fill in the blanks in order for my timesheet to work. There's just one final problem if somebody could please help.
This is a timesheet for a 5 day work week. I need to count the number of unique log numbers for a specific activity. The log numbers counted must be unique across the entire week, not just for each day, which means I want the formula to count the unique log numbers across multiple sheets.
The formula also has multiple conditions. I got 2 columns. The first part of the formula needs to verify a word, say, "split" and if it does it checks the adjacent cell for a unique log number. If both arguments are true, it counts the log as 1 unit.
Here is a working formula for only one page. =COUNT(IF(D4:D29="split",IF(FREQUENCY(C4:C28,C4:C28)>0,1,)))
Here's 2 problems with this formula: 1. I will count if it encounters a blank cell in the Log numbers the first time (which will happen as not every activity we do has a log#), but it will stop counting if it encounters a second blank cell.
2. I don't know how to make it work across several sheets.
This is an alternate formula which works and skips the blank cells, but I don't know how to add the multiple condition of "split" and to have it work across multiple sheets. I just copied it Microsoft. As I said, I don't understand it, I just fill in the blanks.
I have 3 sets of data - Process, Step, and Time Range. I am trying to generate schedules based on Process, with Step being the vertical axis, and Time Range being the horizontal axis. Hence, I'll have schedules showing that for each Process, the number of cases that each Step that has taken, for example, "0-7 Days", "8-14 Days", etc.
I have four Processes in total - A,B,C, and D; 15 Steps from 1 to 15; and 7 Time Ranges. I have attached a sample .xls showing the schedules that I would like to popuple the counting onto. A little more details, not all Processhas all 15 Steps, i.e. Process A has Step 1 thru Step 9 only, Process D has Step 1 thru Step 15 excluding Step 11 & 12I am actually creating a template where data will keep on expanding and updatingwould prefer excel formula rather than VBA code as I am not very familiar with what to do with VBA codes
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 want to count all instances if the following conditions are true. In quotations, are the names that I am using for column ranges. Here are my conditions, I want to count the rows that have the following conditions.
When "dates" or J2:J25 is less than or equal to today's date AND "HTeam" or W2:W25 is equal to Civil AND "Percent" or K2:K25 is equal to 100
I'm having a problem with a spreadsheet in Excel. I have in column terras, codti problem and several cells that are repeated. I'm stating that terra appear only once, the information in column each issue should appear in different columns with the sum of how often they appear and codti according to terra.
In excel is best illustrated what I mean!
In total, 5267 lines and need to do this with all. You can do this in excel?
I am trying to count the number of unique entries in a sheet, that also satisfy 2 other conditions.
I've attached an example sheet : Example email report.xlsx
The result I need is: The number of unique values in column E, that also have NULL in column G and NULL in column I.
So, in the example, the result would be 7.
I know I can do this by conditional formatting and filtering, but would prefer a formula, and perhaps also a macro that I could apply as the number of rows in each sheet is up to 20000, and each month's sheet will be a different size.
I need to modify the underneath Count Array Formula to count unique values based on multiple conditions. I can get the formulas to work with NUMERIC values in Column A in the N1 & N2 tabs. However, I cannot get the formula to work when column A contains TEXT values in the TX1 & TX2 tabs.
I've attached the XL file for your review of the project.
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 am trying to get sumproduct to work on a table where two conditions in the same column must be true (ie two employees must both have worked on the same project) in order to calculate a result. Trouble is my formula doesn't produce anything but a big fat zero.
Column A contains the list of projects. Column B has the list of employees. And column C has each employee's cost. So:
=sumproduct((column A = project1) * (column B = Joe) * (column B = Mark) * (column C))
=total cost of Project1 when both Joe AND Mark work on it.
Unfortunately, when I structure sumproduct this way, it returns zero.
I have a problem with the attached spreadsheet. I have certain letters (A,B,C etc.) that are shipped to various regions. I would like to have a count on top to count the total number of orders, but one that also counts the total number of unique orders. However, this unique count has to be dynamic and must be able to adjust accordingly to the filters (by default, if no other filters are applied, should be 15). For example, if I apply the "Ship To" filter to Canada, the total number should be 19, but the unique count should be 12. If I change the "Ship To" filter to US, the total number should be 9, and the unique count should be 7. I've tried to use the advanced filters but if I apply the unique entries filter, it is only a one time calculation. Also, the advanced filter gets rid of my other filters.
I have a few varieties of SumProduct-formulae that are very helpful when dealing with numbers and/or letters in set conditions.
Like this one, which counts rows where conditions are met in four columns: =SUMPRODUCT(--(Z413:Z432="G");--(AB413:AB432<-3);--(AC413:AC432>0,4)*(AD413:AD432>5)) and this one, which sums the values of the same =SUMPRODUCT(--(Z413:Z432="G");--(AB413:AB432<-3);--(AC413:AC432>0,4);--(AD413:AD432>5);(AD413:AD432))
How does one make these two do their respective jobs when one or some of the columns are conditions for the next column - with their changing values, and thereby changing conditions?
Instead of "bigger than 0,4" as the condition for the AC part, it should be something like "bigger than it's AD counterpart" (AD value on the same row).
I have a few combinations of these "internal" measurements to do (the AB in addition being < than a certain percentage of the AC, and so on) but first of all I need a solution to the placement of the > and < signs.
see my attached sheet cotaining the following questions. in a day report sheet how should i count request matching the crateria of date and other conditions. in a monthly report a heavy conditional sum calculation which make slower sheets how can i make it faster.
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 ).
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.
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 dont understand why this code is not working. i get run time 1004 application or object defined error. basically i want to count column 11 if there is a value in column 2.
Public Sub offloadDoor() Dim unassigned As Long unassigned = 0 For rowvar = 18 To 504 If IsEmpty(Sheet2. Cells(rowvar, 11).Value) = False Then If IsEmpty(Sheet2.Cells(rowvar, 2).Value) = False Then unassigned = unassigned + 1 End If End If Next Sheet8.Cells("b3").Value = unassigned End Sub
I am trying to use SUMPRODUCT on one of my excel files. the condition is that it will try to count all those cells that contain certain letters. i tried using this formula, =SUMPRODUCT(--('All Computers'!$A$2:$A$100000="USA*"),--('All Computers'!$E$2:$E$100000="June")), but I am getting a 0 value.
I am trying to exclude unusable data from my analysis. I have flagged data in the flag column on sheet 1 by a "1". A "0" is good data. On sheet 2, I have counts for the amounts of a certain code per partner. I used a sumproduct formula for the counts, but now I want to exclude all data with a flag "1".