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.
View 7 RepliesI 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
=SUMPRODUCT( --( rngRegion = $A$1), --(rngProduct = $A$2), OFFSET( rngSalesData, 0, $A$3))
...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.
=SUMPRODUCT( --( rngRegion = $A$1), --(rngProduct = $A$2), OFFSET( rngSalesData, , , ,$A$3)
...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.
Good afternoon Gentlemen, I have a column of data, with a varying number of percentage values that add up to 100%, separated by "NA", i.e.
33%
33%
33%
NA
25%
25%
25%
25%
NA
100%
NA
20%
20%
20%
20%
20%
NA
Now... when I change one of the values I would like the others to even up, i.e. in the last example if I change a 20% to 50% I would like the others to change to 10%... any ideas?
How to express what I am trying to do in a sentence but basically I have this formula
[Code] ..........
I need B# to change automatally like it does when you copy the formula but i need the N1 and N2 to stay N1 and N2, how do i do this?
I have a row of data with the cells I want to find a MAX for are separated from each other:
a1=300 d1=500 g1=800 j1=10,000
I want to find the MAX value for a1,d1,g1,j1, but exclude any value over 1000. So instead of =MAX(a1,d1,g1,j1) returning 10,000, =???(???) will return the next highest max of 800.
All i am trying to do is create a function that will provide me with the minimum invoice value between cells H17:H150 which excludes negative values i.e. refunds ...
View 6 Replies View RelatedBelow is the criteria I need a formula for.
First Criteria - If column D=John Smith,
Second Criteria - Excluding duplicates in column A, (red are the unique values I need)
Third Criteria - Sum of column B that meets the above criteria first
Result I am after is
John Smith - $154,330.00
James Brown - $150,520.00
David Williams - $161,390.00
A
B
C
D
[code]....
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:
=COUNTIFS(A:A,"<>P9V*""<>PUA*",G:G,"P")
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 have an array that is 1 row high by 16 columns wide.
Each cell may contain a positive value, or a zero.
I need a formula to find the "Minimum value that is greater than zero" in
columns 1,3,5,7,9,11,13 and 15.
=MIN(A1,C1,E1,G1,I1,K1,M1,O1) will always return the zero value while I
need the minimum value that is greater than zero.
If I use nested IF functions to exclude zeroes I run foul of the max of 7
allowed.
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.
C
D
E
F
G
H
5
2
N/A
N/A
0
43263526AF
324GFDS
[Code] ........
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 am trying to add values
and I am having a problem when I introduce a blank lookup as in status here.
I have included the formula below ...
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.
[Code] ....
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
eg
user1 code 1 0:02:00
user1
user1
user2 code 3 0:05:00
user2 code 6 0:20:00
user2
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.
View 9 Replies View RelatedThe 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
[Code] .....
Full formula is below:
[Code] .....
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.
=SUMPRODUCT(--(Register!$I$119:$I$417=$A4),--(Register!$C$119:$C$417=B$2),--(Register!$B$119:$B$417))
fix cell E8-E19 (totals). I don't think its anything to do with the date format.
View 2 Replies View RelatedIs 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?
I am trying to sum up the rows that have multiple values in one column.
Here is my curent formula THAT works
=SUMPRODUCT(($H$46:$H$5787="EO-Deal Processing-Closing")*($K$46:$K$5787="Submitted")*($I$46:$I$5787="2-medium"))
Now I also want to add the following
($K$46:$K$5787="Assigned")
How can I get the value I need so that column "K" I get returned both "submitted" and "assigned"?
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
=SUMPRODUCT((--(DTCCSUBTRANSTYPE<>$G$2))*(--(DTCCSUBEVENTTYPE<>$I$2)*(--(DTCCSUBBACKLOAD<>$I$3)
*(--(DTCCSUBINPUTSOURCE<>$I$4)*(--(TEXT(DTCCSUBTRADEDATE,"MMM-YY")=$D$2))))))
I am attempting to count from a spreadsheet the reference number of a customer (numbers and text) based on two criteria.
1, If column G= Requested
2, Column I = Meeting
Count Row E
I thought a sumproduct was best and have started using it for the first time, I thought this should work but I keep getting a #NUM! error.
I have tried with numbers and it works but the non numeric aspect is difficult.
[Code].....
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.
The Table : Column R represents a list of services
Column L is the full price
Column N is the discounted price ( my example reflects no discount in this case)
Column C is an associated code , in some cases the code is n/a Starting at Row 13 ........
Need the formula to multiply monthly values with a maximum value in any one period? The sample file attached explains it better.
View 2 Replies View RelatedI 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.
I'm using Excel 2010.