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.
I have a data set that is coming from an Access cross-tab query, with attribute values in the first four columns and two years of sales $ in the subsequent 24 columns, much like:
Product | Category | Region | District | Jan10$ | Feb10$ | Mar10$....etc
I am summarizing the data using multiple criteria, so I've been using SUMPRODUCT formulas that utilize dynamic named ranges. All the summaries have required summing the data by individual months only, so I created only one named range for the sales data and use OFFSET to determine which column to pull it from in the SUMPRODUCT formula, such as
...where A3 represent the number of the month that I'm summarizing (i.e. October = 10, etc). This method worked great, but now I'm being asked to summarize data YEAR-TO-DATE. I thought this wouldn't be difficult, as I'd just use the width part of the OFFSET function to expand the range I want to sum. But its not working. I attempted this variation, where I'm trying to sum Jan10 - Oct10.
...but it just gives me the dreaded #VALUE! error. I stripped down the formula to try to find the problem until it was just
=SUMPRODUCT( OFFSET( rngSalesData, , , ,$A$3)
And that gives me the sum I expect for the 10 months of data. The problem seems to be when I add in the additional critiera like --(rngRegion = $A$1), then the #VALUE! error pops up. So I'm guessing the issue is with the sizes of the named ranges in the formula being different even though they aren't, and they work just fine when I'm not trying to leverage the width portion of the OFFSET function. Should I try using a different technique to sum these multiple columns together?
I have the the following formula =SUMPRODUCT(--(dealer1=$B36),--(make=$A36),--(company<>nominated))
The first two statements work alone, however I have an array which contains a list of customers that I wish to exclude from the total. I've put those in an array and called it nominated, However I just get #N/A.
I have multiple rows of data per each column. I'm trying to count rows that match a value that ISN'T any of the expected values in column A as well as a certain value in column G. This is in case future data is introduced that doesn't match the existing codes.
I'm trying to count all rows that DON'T have a value in column A beginning with PUA or P9V, so far I am trying this:
But it only seems to work on the "<>P9V" part. When I put P's in the column G of which the column A matches PUA*, it still counts the cell. I've tried a couple of AND variations but none are working.
I am attempting to count how many cells have different values in a table while not counting the N/A's and 0's found in the table. From what I'm finding online, I see lots of formulas set up with frequency functions, but none of them are set up to exclude anything - just find unique values. I need to count the cells with values other than 0 and N/A going across each row and not count the same value twice
Here's an example of what the table looks like (the real one is over 1,000 rows and 50 columns) with column C being where the formula needs to go. I put what I would like to have returned in the cells.
1 workbook, 2 worksheets (or tabs). On tab 1, I want a formula/alert that tells the user if any duplicate values exist in Column A of tab 2
Tab 2, Column A, has Unique ID's (6 digit numeric values)
The user manually inputs the ID's on new rows in Column A
Row 1 is reserved and in use for something else Row 2 is my header, so cell A2 says "ID" Row 3-623 currently contain unique ID's
When the user inputs a new ID into cell A624, then they return to Tab 1, I want my formula/alert on Tab 1 to tell the user that they have duplicates in Column A of tab 2. I know the Conditional Formatting, but if the user copies in 100 new values, they won't necessarily see the highlighted cells. My tab 1 is my "checks and balances" and the last place the user is suppposed to look to ensure that they haven't created any duplicate ID's. If the user sees a warning message that says duplicates exist, then I'll tell them that they need to look at column A (for cells that have been conditionally highlighted).
One issue that I'm running into with the conditional highlighting is that I want cells A3:A1048576 to already have the conditional formatting - this way when the user inserts a value into Cell A624, then A625, etc they conditional formatting is already there. Right now with data in cells A3:A623, cells A624:A1048576 are all highlighted with the Red/Bold Red Font (which is okay I guess), but ideally it would be nice to not count 2+ empty cells as duplicates and I'll have to have my formula on Tab 1 not include the blank cells.
I DO NOT want to use the Remove Duplicates feature of Excel 2010. If I remove them I could be removing data in columns B, C, D, etc that belong to the Unique ID. I just need the user to be told in Tab 1 that they DO have duplicates and I'll train the user how to research this and fix it.
The reason I want to look for duplicates in the entire Column A is because the list of Unique ID's will grow over time.
I have a userform where I can select multiple items in a listbox and add them to another. I also have the ability to filter the first listbox to make finding items easier. The issue I am having concerns the clear filter button. As currently designed, the clear filter button will reset the initial listbox back to its default values. Ideally, I would like it to reset to the default values excluding those values that currently in the second listbox.
The entire code is below for reference, but it's the sub ClearFilter_Click that I am struggling with.
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
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.
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 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'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.
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
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'm looking at the formula in Sheet3, cell F5. You'll see that the criteria "--(Sheet2!I:I<>Sheet1!O:O)" does not seem to be working. I need the formula to not include any values from Sheet2!E:E, where the corresponding value in column I does not equal any value in Sheet1!O:O. Also, these values can change by user so its not as simple as just typing them in to the formula as an array...
That VBA part of this is that it's in a macro, so if there is any way to make this easier using code instead of a simple formula.
I have 3 ranges of data which each have a quantity and a length. I want to create from these ranges a list of unique values with the total quantity required of each value (as shown). Inputs on any column may be blank but where there is a length, there will be an adjacent quantity to the left.