I've used the sumproduct formula very sucessfully in a workbook. The workbook is used to monitor discrepancies routed to other departments. Column U has the status of the discrepancy (Open, Closed, Cancelled etc). The below formula returns the number of discrepancies raised to a particular department. Now I need to tweak the formula to exclude values "Cancelled" found in range $U$119:$U:417.
Question is...in the below formula the red are pointing back to name ranges in a different tab on my spreadsheet. On that spreadsheet I also have a name range called DTCCSUBSUPPREF
I would like to add within my formula, a string which will point to that name range and exclude values if they fit my criteria. Basically I need to see values only where comp or tri dont show up. comp or tri could be the only value in the cell, or could be in the beginning of a string, at end, or in between. Below examples. I have tried (--(DTCCSUBSUPPREF<>"*comp*")*(--(DTCCSUBSUPPREF<>"*tri*") , and I would have thought the * characters would have served as a catch all.
Below is the entire forumla just in case.
11111111FT04comp comp tri 11comp 11tri tri11111111FRP06
I need a formula that calculates the values of 5 seperate cells located in different places throughout my sheet. The value will either be numerical or display a text value (normally "RD"). If it displays text I do not want that value counted. So it literally just totals the number values.
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".
I am trying to sum up column V, when for a specific date in column z, and only sum when the two names EEM, Merri1* are involved. I used the * because there are a couple of variations for MErri1 after the 1. The formula is not working. I believe it's the way I'm combining this
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.
How do I adjust this formula so it copies & paste special values rather than copying formatting etc? I am very new to this and I have looked at other examples but have found it hard to adjust my code using those examples.
VB: Sub PrepayjournalKW() ' ' PrepayjournalKW Macro ' Range("A6", Range("A" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Journal").Range("A1") Range("B6", Range("B" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Journal").Range("C1") Range("AB6", Range("AB" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Journal").Range("D1") Range("AF6", Range("AB" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Journal").Range("E1") End Sub
I have looked on here and I can find the minimum value in my range but what I am trying to do is find the minumum two values. Or rather, i want to find the average of the remaining values.
If the lowest value happens THREE OR MORE times, i still only want to exclude two of them. Same thing goes if the second lowest happens more than once, i only want to exclude at most two values from the avg.
currently, i have 12 values in my list.
example:
list --> one in each cell going across
25, 50, 87, 56, 99, 80, 81, 82, 50, 100, 98, 99
i have the answer as 83.2 doing it by hand. the 25 and one of the 50s would be dropped and the rest would be averaged.
I have a scenario where I have a rolling list of sales figures which get added to each week that passes.
I need a formula that will calculate the last 5 weeks of sales and generate an average - which I think I have an idea how to do.
The sticking point is that so as not to skew the averages, when there has been an exceptionally busy or quiet day for a reason we know about I exclude the sales from that week.
This then interferes with the averages as it either takes it as a zero and lowers it or seems to stop formulas from working.
So to summarise:
Average of last 5 weeks sales Excluding any blanks Dynamic enough to always pick up the last 5 values in the list (i.e. the last 5 weeks)
Is there a way to do a Formula (SUM) but exclude cells with invalid values? I would like to add up all values in attached spreadsheet for cells A14 to A28 and again for A49 to A63, as well as get an average C-14 to 28 and again for C49-63.
As some of the cells contain text rather than numbers, the formula doesn't work. how to exclude these cells?
I have been asked to create a worksheet to calculate the percentage of the utilisation of a number of loan cars per month. Each of the cars may be used on each and every day of the week but the percentage utilisation is to be calculated on working days only. However it is essential to record the name of the driver of the car at weekends and holidays. Column A is populated with the dates of the month. Colum B is populated with the relative days of the week. In column C and subsequent columns I record the name of the driver of a particular car registration. Is there any way that I can count the number of drivers of each car on working days only and not weekends or bank holidays?
Names Quarters Nickels Dimes Pennies Record exc 0s
John 0 1 2 3
[Code] ........
In my table above, I would like to use the countif formula to count records based on the person, but I don't want the count to include values if the sum of quarters, nickels, dimes, AND pennies equal 0.
I have 16 columns in a single row that I am trying to apply to an array formula. That works, but I need to exclude all cells with the number "0" from the array range. I think I can do this myself in VBA but I don't want to run a macro every time something changes. Is it possible to do this in excel?
{=10*log(10^(A1:A16/10))}
It is possible that A1:A4 is a non-zero range and A12:16 may be a non-zero range. (Zeros will always be grouped in 4s)
i have information across three columns the first has user-names in each row the whole way down, the second has between 1-7 activity codes (when not eacher user will use), the third has the times they have been on these codes.
what im trying to do is match the name, code and get the time to be displayed in a fix table, as the reported information is not always in the same structer
now i've got it in my head that sumproduct iwll be the best way to get it, but i cant seam to get the third array to work properly, and always comes up with either value or NA
I'm trying to use SUMPRODUCT as a sort of SUMIF/VLOOKUP hybrid. The following formula:
Code: =ROUND(SUMPRODUCT('[12 GB BUDGET 15 V1 LOCAL VERSION.xlsx] Revenue'!$G$3:$FM$3=$K2)*('[12 GB BUDGET 15 V1 LOCAL VERSION.xlsx]Revenue'!$G$27:$FM$27);0)
... should check for the value in cell K2 of the current sheet, compare it to all the values in row 3 of the Revenue sheet, and add up any and all values for those columns in row 27 where there's a match in row 3.
I don't get an error, but it's not adding anything up, either.
Is there a way to sum a list that contains both text and values using the SUMPRODUCT function? My efforts yielded the #VALUE! error. SUM and SUMIF will ignore the text but I have multiple criteria.
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'm working on a spreadsheet to rank stores based on how they perform in certain metrics. These metrics are weighted, and occasionally a metric for a store will get waived. I'm having trouble figuring out how to handle this without making a custom formula for each occurrence.
Is there a simple way to include the values of an entire range?
This is what I tried, but I get #name
Code: =SUMPRODUCT((Datasheet!J2:J65000 = Range("C3:AA3"))*Datasheet!F2:F65000) If I use a single cell it works, but only with what matches that cell.
Code: =SUMPRODUCT((Datasheet!J2:J65000 = C3)*Datasheet!F2:F65000) I'd like the criteria to match anything in that range C3:AA3. Do I need to specify each cell individually or is there an easy way have it use the entire range's values for its criteria?
Sumproduct formula with selection criteria of "A", "B"... in the first column and numeric values in the next colum. The selection is controled by a List where the user can choose "A", "B", ... ,or "ALL". What wildcard-type (pseudo) is needed to select all values when "ALL" is chosen?
I'm using Sumproduct because there is other selection criteria, but it should not impact this part of the formula.
Example: Sumproduct((A1:A100=X1)*(B1:B100)) , where A=selection aray, B=numeric value, X1=corresponding list selection to A
