Tracker
HOME    TRACKER    Excel

# Sumproduct, Excluding Some Values And Adjusting

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

Related Forum Messages:
Sumproduct Excluding An Array
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.

Adjusting Percentage Values On Cell Change
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?

MIN Statement But Excluding Negative Values
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 ...

Finding Maximum Value While Excluding Some Values
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.

Finding Min Cell Values Excluding Zero In Alternate Columns
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 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 ...

Sumproduct - Values Across Three Columns
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

Sumproduct Using Text And Values
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.

Count The Values Through Sumproduct In VBA
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()
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))..............

Sumproduct Multiple Values In One Column
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 - Select All Values Per Wildcard
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

Sumproduct Formula - How Do I Exclude Values
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))

SUMPRODUCT Formula Throwing Out #VALUES!
fix cell E8-E19 (totals). I don't think its anything to do with the date format.

Extract Numbers Then Sumproduct Values Against Another Column Of Data
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 ........

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…!

Sumproduct Formula: Multiply Monthly Values With A Maximum Value In Any One Period
Need the formula to multiply monthly values with a maximum value in any one period? The sample file attached explains it better.

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

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.

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

Adjusting If -Then Statement In Code
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....

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.

Help With Auto Adjusting Date Formula
This should be simple but I can't get it...any help appreciated. See sample attached

Adjusting Macro To Use On Time Method.
I have a macro on a workbook that when I hit the button it asks for the date of a register sales journal that I want to import. Then it asks for the cash in drawer amount. Then it puts in all of the data into the sales spreadsheet. Now I want to use the on time method to bypass the button and the entry of the date, and even get rid of the cash in drawer. I want to make the macro run at 9:30 every night. I want it to use the date on the computer to tell it which date to find? Here is the code I'm using! button macro

' Button2_Click Macro

Sub Button2_Click()
ImportData
End Sub

Here is the main macro
Function FileExists(sFile As String) As Boolean
Dim iLen As Integer
On Error Goto NO_FILE
FileExists = True

iLen = FileLen(sFile) ................

i do not know or can seem to find out is how i can program the team list in the table to adjust each team position auto-maticly relevant to each teams points scored

IE 1st 2nd 3rd 4th etc in a vertical league list

Graphically Adjusting Zoom Using A Scroll Bar
I am created a very simple account plan for colleagues in my team which is proving to be very poular with the exception of one thing.

I created the plan which is around 30 sheets on my laptops highest resolution setting. 1400 x 1050. Only one other colleague has this res available so of course when the rest of the team open it, it looks huge.

I need help with this as I'm still very new to excel, although learning very fast!

My preference for finding a fix for this would be to insert a scroll bar on each sheet that, when scrolled, changes the zoom of the page between a range of 50% and 100%. This may not be the best way of doing it but I'm trying to make the sheets as simple to use as possible as some of my colleagues are only just compuetr literate, let alone excel competent!

below is some code from a spreadsheet I am working on,,,,,, as you can see it adds items to a dropdown list in a combo box. This may require an update at another time,,,,, is it possible for the list to be updated from a list in the spreadsheet, therfore preventing people from accessing the code

I have the a file that is downloaded from AS400. The time column is presented in 24 hours format in column D. For example : 92114 means 9.21 am. I need a formula to adjust the date in column B based on the following criteria:

1) If the time is after 2.00 pm, adjust to the next working day taking into account public holidays in column H

2) If the time is before 2.00 pm, remain the same day

3) if the time is after 2.00 pm and it is Friday, adjust to the next working day taking into account public holidays in column H

The correctly adjusted date is in column E.

This is for a call centre and sales office. there are 3 tabs. Tab 1 represents the values that we update at the end of each week which then gives a running total over an 8 week period and then an average below. there is a seperate table for each staff member. Tab two is for one team of people and tab two is for the other team. Namely LG team and Sales Team. the tab 2 and 3 are to show league tables that both collect data from tab 1, but then create a league table showing who is top dog over the 8 week period. But this also has to self adjust to be in order.

on tab 2 and 3, the tables on the left will be hidden once complete, but are there to work out the league. the table on the right is then there to put and show the league in the right order. I HOPE THAT MAKES SENSE, COS EVEN I'M LOST NOW. I've followed previous advice on this and inputted what I think are the right formulas, but for some reason it's coming up with the wrong answers and several names are being repeated which are wrong. For example even when ben perhaps is number 2, it will show rahim as number 2 and number 3. why is this?

also, how do I get by the problem of on the odd occassion when perhaps ben and rahim have the same stats and are joint second, but it will only show ben as 2 and ben as 2 again, leaving rahim out of it. WOW. I'm glad I've got that out of my system. I was doing the washing up yesterday donig Excel formulas in my head. But the washing up got done at least.

Adjusting Page Breaks In Multiple Tabs
I have a spreadsheet with many tabs in it (over 100 I believe) and I just want a macro that will adjust the page breaks so it will print one page per tab. Somewhere along the way, the page breaks have auto-adjusted themselves to print 4 or more pages on one tab. I do not want that.

In trying to figure this out on my own, I recorded a macro on one of the tabs and it returned the following
Sub Macro1()

ActiveWindow.View = xlPageBreakPreview
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
ActiveWindow.View = xlNormalView
End Sub
How can I add to or adjust this to make it adjust the pagebreaks in all available tabs?

Adjusting Code To Use Alternate Cell Ranges
Here's a nice easy one for anybody whose used excel for more thaan a week unlike me, I'm using the following code to access a popup calendar:

Adjusting Charts To Reflect Data Properly
I've compiled data into a spreadsheet which indicates the number of homes sold over the course of 3 years (from 1/1/05 to now) along with their price per square foot, sale price, year built, bed/bath count, and other pertinent factors regarding each property.

I've extrapolated graphs from these in the past which indicated the trends in each category over time (i.e.: Average quarterly price per square foot over time, and so forth).

The data range I'm working with now, however, is from an area of my region so small, that the number of units sold over the past few years is sporadic. Whereas it's normally 100 to 1000 units that comprise my data, now it's only 15 units.

This has created gaps in my line graphs where the price per square foot will dramatically drop down to zero over the periods in time where no sale was recorded. So the line graph ends up looking like a zig-zaggy range of peaks reflecting the price per square foot of a sale, and valleys representing "\$0.00" for periods of time where nothing has sold.

To have a Price per square foot of \$0.00 is incorrect since in reality nothing was sold.

I would like to be able to eliminate the portions of the graph that have the "\$0.00" so that the line graph can "jump" from one sale to the next when the quarterly data indicates 0 units sold.

I'd like to do this without having to eliminate those areas in my spreadsheets. Is there any "catch-all" tool that might do this or is that just crazy talk?

Multiple Criteria And SUMPRODUCT (count The Number Of Rows That Have Values Greater Than 10/01/2008 In Either Of Two Fields)
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.

Moving Colored Text Within A Automatically Adjusting Table Containing More Than 3 Col
Moving Colored Text within a automatically adjusting table containing More Than 3 Colors, from Coloum to Coloum. i have managed to put this little chart together to use on our gaming wedsite but i would like to go one step further with it. may table automatically moves the colums up and down the table as i enter the infomation in to it,, "but" I the colours in the text do not stay the same as they move up and down the coloums. how to make the coloured text keep its colour in the table as it alters its position in the table

Count Excluding N/A
I have a COUNTA function that I'm using. However, I want to exclude "N/A" from my count, what is the function that I need to use to do that.

Currently I have =COUNTA(\$C\$10:\$C\$122). How do I modify this formula so it doesn't include "N/A" in my count.

Excluding Cells
I have three columns that i've called 'active period' and 'Date/time' and 'value'

The the value of the cells in the 'active period' column depend on the value and time.

I want to create a cell that; when i can enter a date, this date will be excluded from the 'active period' column

Excluding Non Mature Data
I am trying to automatically add up data, excluding that last two data points in a row. And every month add the next data point based on the date of the spreadsheet (top left). see trail below... This sting is going in circles.

Min Value Excluding 0 For Non-contiguous Cells
I can find the min value excluding zero in a range of contiguous cells, but how can I do it if the cells are A1, A3, A25, A67, etc?

Sum Of A Range Excluding Cells Containing #N/A
I want to sum a range of cells that are referencing another spreadsheet using a vlookup function, eg:

#N/A
#N/A
#N/A
51
78
73

i dont want to delete the formulas that are returning the #N/A but i want to be able to sum only the numeric values from the six cells.

SUMIF Excluding Text
I am trying to sum data in a column based on the absence of a character in a related column.

That is to say I need to be able to exclude rows that contain an "m" (there may be multiple characters in the criteria cell)

Dates Excluding Sundays
I have a worksheet which calculates dates a product will be manufactured based on how many hours the job will run.

The first column has the starting date of the job which I input. The second column contains the date the job will finish based on a third column which contains the hours the job will run. The calculated ending date is then put into the starting date of the 2nd job. It continues to calculate down to the last job.

This calculation is based on a 24 hour day, 7 days a week. I would like to take out Sunday's in the calculation.

Averaging But Excluding Zeros
I have a list of numbers that I want to average. Problem is, some of the numbers contain zeros and i want to exclude these entries.

So, if I had a list of 6 values and 3 were zero, I'd want to sum the list and divide it by 3 (not 6).

Min Formula Excluding Any 0's Or Empty Cells
I have tried a number of different ones from my search of this forum but I'm getting an error. here is a sample of my spreadsheet.

I want it to give me the lowest number, I don't want it to look at the 0's or if the cell is blank.
Here is my formula and my error:
=SMALL(N196:N203,COUNTIF(\$N\$196:\$N\$203,0)+1)

my error is #num!

I've also tried others including using the MIN function but either 0 is entered or the error above.

3402414

Create List Excluding Zeros
I have a column with values, say column a. In column a are several rows with values equal to zero. I would now like to create a new list that omits the values that are zero, e.g.:

original list:
23
0
54
76
0
0

new list:
23
54
76

Excluding Weekends Without Analysis Toolpak
I currently have a formula like this:
e87-7+choose(weekday(e87-7),1,0,0,0,0,0,2

this formula is used because I was adding or subtracting a certain number of days from one date to figure out when a certain event would occur next. When the date fell on a weekend, I wanted to make sure that it rolled over to the next closest weekday.

HOWEVER, what I also want this formula to do is exclude weekends in the counting of the days. So basically, I want to exclude weekends completely from my formula.

example, Printing always occurs 4 days after the p.r. meeting. the p.r. meeting is in e87. so e87 + 4 = the printing date. but I dont want those 4 days to include weekends in the count AND I dont want the final date to fall on a weekend either.

Weighted Average Excluding High/Low
I'm trying to calculate a weighted average excluding the top and bottom value, and am having trouble creating a formula that can be dragged across.

my values are in E2:L17, and the weights are in M2:M17. Basically I want a weighted average of each column, using M2:M17 as the weights.

I'm sure someone's done something like this before, care to assist?

Example:

Data: 1, 2, 3, 4, 5
Weights: 3, 4, 5, 6, 7

Weighted avg ex hi/lo: (2x4+3x4+4x6)/3 = 44/3

How To Calculate Leadtime Excluding Sunday
I have a start date in cell A1(5/15/2009) and a end date in cell B1(5/25/2009).

I would like to calculate the leadtime between the 2 dates in cell C1 excluding the Sundays in between.

I have out a formula(in cell c1) as B1-A1 but it is yielding 14 days whereas it should be12 days excluding Sundays.

Averaging Selected Cells Excluding Zero
I wanted to average selected cells excluding the value zero. Also, I wanted to make the value of cell is zero and not to display the error #DIV/0! if the denominator is zero.

A1 = Denominator

Selected cell:
A3, A5, A6, A8

Copy And Paste Excluding Zeros
How can I copy a range and paste it excluding the lines with a zero value. The sample attached explains it better.

Excluding Specific Items When Dragging Down
Im using the following formula to update our prices in our stock list.

=VLOOKUP(C1,'Most Recent Prices 2009'!A:G,6,0)

Column C = Unique part number.

Problem:
I have a tab of 100 or so prices that I don't want to be updated. How can I exclude these pieces of equipment when I drag down the above formula. Assume that these pieces of equipment are in a tab called 'keep' and their unique part numbers are in Column A.

Average Over Worksheets Excluding Zeros
im trying to average over a number of work sheets to determine the avergage talk time of employees over a day, ive tried:

Excluding Zeros When Calculating An Average
I have a one column of numbers...let's say consisting of six rows
the following numbers 12,0,14,25,0,9
if I average this it comes out to 10

how can I average the same rows but exclude the zeros in the outcome(is there a formula to do that?)

I would like the answer to be 15.....I still need the zero's in the rows however