# Sumproduct, Excluding Some Values And Adjusting

Oct 21, 2009
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 Replies
ADVERTISEMENT
Dec 12, 2011

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

=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?

View 5 Replies
View Related
Dec 16, 2009

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.

View 4 Replies
View Related
Jan 8, 2009

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?

View 11 Replies
View Related
Jun 28, 2014

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?

View 3 Replies
View Related
Feb 2, 2006

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.

View 14 Replies
View Related
Feb 2, 2009

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 Related
Sep 23, 2013

Below 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]....

View 1 Replies
View Related
Apr 14, 2014

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.

View 2 Replies
View Related
Nov 4, 2005

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.

View 9 Replies
View Related
Jul 10, 2013

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] ........

View 5 Replies
View Related
Jun 29, 2012

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.

View 9 Replies
View Related
Apr 27, 2009

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 ...

View 9 Replies
View Related
May 27, 2014

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] ....

View 2 Replies
View Related
Jun 15, 2009

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

View 7 Replies
View Related
Apr 10, 2014

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.

View 9 Replies
View Related
Jan 21, 2004

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 Related
Aug 5, 2006

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))..............

View 7 Replies
View Related
Jan 19, 2014

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] .....

View 14 Replies
View Related
Apr 14, 2009

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))

View 3 Replies
View Related
Jun 9, 2009

fix cell E8-E19 (totals). I don't think its anything to do with the date format.

View 2 Replies
View Related
Mar 1, 2013

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?

View 9 Replies
View Related
Jan 24, 2008

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"?

View 9 Replies
View Related
Jul 30, 2008

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

View 9 Replies
View Related
Jan 22, 2014

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))))))

View 6 Replies
View Related
Feb 5, 2014

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].....

View 14 Replies
View Related
Apr 11, 2014

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.

View 1 Replies
View Related
Feb 22, 2010

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 ........

View 9 Replies
View Related
Jun 16, 2009

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 Related
Jul 31, 2014

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.

I'm using Excel 2010.

View 9 Replies
View Related
Jun 19, 2014

Using Excel 2010, I am trying to do a Sumproduct formula with two criteria, one of which needs to ignore text values.

Here is the set up:

Column AColumn BColumn C

(Side)(Qty)(Price)

Buy5,51215.67

Sell119,428null

Buy24,20945.77

Sell20,05412.25

...

I'm trying to find the sumproduct of Qty * Price if the side equals "Buy" (or "Sell") but ignoring the "null" value in column C. The formula I have is =SUMPRODUCT(--($A$2:$A$20="Buy")*IF(ISNUMBER($C$2:$C$20),--($B$2:$B$20*$C$2:$C$20)))

The result in the cell is 0, but if I open the Insert Function dialog box, I see the correct value being returned.

View 5 Replies
View Related
Apr 18, 2008

I am trying to figure out a formula for the boss, but I just cannot wrap my mind around what I need to do. I’m sending out an S.O.S.! If you can figure this out, please respond.

=MIN(b4*12-25%,b4*12-500) + MIN(c4*12-25%,c4*12-500) fills in the cell with the correct answer, but not quite the answer that I need. Let me illustrate:

The first part of the formula is to take b4 times 12 minus 25% --if this amount is less than or equal to 500 then use the amount. If it is more than 500 then use 500.

(b4*12-25%)

234.25 * 12 = 2811 – 25% = 2811

2811 - 25% = 702.75 500 Since this amount is more than 500, 500 should be used instead. ~~this is actually the amount that I want to show in the cell, the amount saved~~

2811 - 500 = 2311 ~~this is the answer that is showing up in the cell, which is not quite what I need. ~~

I want to find the answer to this question: b4 times 12 equals ?, subtract 25% or 500 from that, insert this answer into cell.

One of my biggest problems with this is that I can’t even figure out how to say what it is that I need. The formula is doing exactly what I'm telling it to do and I can't even figure out what it is that isn't working. This formula is confusing my brain…!

View 9 Replies
View Related
Aug 2, 2014

I am using Office/Excel 2007 and Windows 8

I want to determine the number of values SUMIF/SUMPRODUCT functions used/checked to arrive at the answer, i.e. 95 for row 2 and 116 for row 3. A function that can give a 3 and 2 respectively.

Example:

Results for Row #2 gives: 95 with 3 values picked up under "Rate"

Results for Row #3 gives: 116 with 2 values though three places were checked up under "Rate"

How can I count the number of these values referencing at the "Rate" like in the functions used.

I managed to use SUMIF/SUMPRODUCT functions to calculate totals based on "Rate" in row one.

I tried COUNTIF function, but allows only field/heading.

A

B

C

D

E

F

G

[Code] ..........

results for each formula used

95 formula: =SUMIF(B$1:G$1,B$1,B2:G2)

95 formula: =SUMPRODUCT((B$1:G$1=B$1)*(B2:G2))

3 formula: =COUNTIF(B1:G2,B1)

View 9 Replies
View Related
Dec 10, 2012

How Auto Resize (height) cell to fit text? (macros, formulas, setting)? One can make the height of the cell is reset according to the amount of text?

ej..jpg

View 4 Replies
View Related
Jan 30, 2014

I'm writing a code to adjust the height of the row based on the wraped lines. I want it to be "16.5 * (number of lines)".

First I counted the number of lines in each cell. I wrote at D1 the formula to calculate the lines at A1:

=LEN(A1)-LEN(SUBSTITUTE(A1;CHAR(10);""))+1

Then I wrote this vba code to adjust the height:

Sub AdjustHeight()

Dim x As Double

x = 1

For Each Cell In Range("A1: A11")

Cell.RowHeight = 16.5 * CELL(D"x")

x = x + 1

Next Cell

End Sub

How do I do that multiplication? How can I tell it to multiply 16,5 with the value of the cell D"x"? Is this the best way?

View 2 Replies
View Related
Dec 8, 2009

I am trying to make some additions to this code. Basically what it does is copy some data from another sheet. Now there are two types of copying here.

1. A ‘call value’ from another sheet is copied to this sheet.

2. The formula from the previous cell in the column is copied. (eg. Formula in C3 is copied to C4, formula in D3 is copied to D4, and so on)

So the macro works fine for all cases EXCEPT the initial entry, which will be in Row3, that’s where the formulae begin. So I need some kind of an If-Then statement.. to say if A3 is empty then do the following, If not then do something else....

View 12 Replies
View Related
Jun 11, 2007

How would you adjust the network days formula so that Wednesday is considered a non-working day?

View 9 Replies
View Related
Oct 22, 2008

I have a table upon which I wish to perform vlookups, however I need to be able to adjust the range that the vlookup searches to include any data that may be added in the future without having to go into the sheets and change the vlookup.

Example

Name DOB

Tom 01/01/81

Dick 02/02/82

Harry 03/03/83

if someone then adds

Fred 04/04/84

I would like the vlookup to automatically accommodate the extra entry without needing any changes.

I've seen this done on data validation with the following formula:

=OFFSET($a$1,0,0,MATCH(REPT("z",255),$A:$A))

It only needs to do new rows, not columns.

View 9 Replies
View Related
Jun 29, 2006

I am using the =today() function to places todays date in a cell...how can I get the next to cells to automatically display dates that are 2 months and 4 months later without having to change them manually every day. FWIW, this is for an certification expiration chart.

View 4 Replies
View Related
Jan 23, 2009

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.

View 2 Replies
View Related