# SUMPRODUCT Combined With Other Functions

Nov 13, 2008i'm trying to perform an operation that gets the standard deviation of all open status. See my formula.

=SUMPRODUCT(($D$4:$D$1591="open")*1,($H$4:$H$1591)*1,(STDEVA($H$4:$H$1591)))

i'm trying to perform an operation that gets the standard deviation of all open status. See my formula.

=SUMPRODUCT(($D$4:$D$1591="open")*1,($H$4:$H$1591)*1,(STDEVA($H$4:$H$1591)))

ADVERTISEMENT

I have two columns of data that I need to use SUMPRODUCT on. However, in one of the columns, there might be text after the number. The text can be several different characters. I only want to use the numbers, never the text. Also, there are usually blank cells within Column B and at the bottoms of both columns because this formula is going into a template for future worksheets that all have differing numbers of rows.

Example:

14T

16

40

20

150

97L

67

13

12

For the above example, I want the result to be 9879 (14*16 + 40*0 + 20*150 + 97*67 + 13*12 + 0*0 + 0*0 = 9879).

I've tried using the LEFT function to only get the numbers before any text, but I can't make it work with the blank cells at the bottoms of the columns.

Basically I have an equation:

=SUMPRODUCT(G9:G11,H9:H11)/SUM(G9:G11)

It's just a simple percentage calculator for my purposes and works fine as is. However, I want to make it dynamic whereby from a user input the length of the array will increase or decrease, e.g. G9-G11 will become G9-G12 if there are four rows occupied with data. For all my other SUMming equations I have solved this and even came up with what I think is a perfectly valid solution for this one. The one problem is that it doesn't work when combined into a single equation.

My solution for the upper part of the fraction is this:

"A1"=SUMPRODUCT(INDIRECT("G9:G"&MIN(ROW(G9)+A8-1)),INDIRECT("H9:H"&MIN(ROW(H9)+A8-1)))

Where the user inputs the number of rows used in A8.

The solution for the lower part of the fraction is this:

"A2"=SUM(INDIRECT("G9:G"&ROW(G9)+A8-1))

[code]....

i would to combined these together

can this be done

I have a sumproduct function picking up data from a different worksheet. Because i have to much data it takes too much memory and it doesnt run that smoothly. Therefore i have hardcoded the function and tried to evaluate it using the EVAL() function from the morefunc add-in. However i don't get it to work. It continuously returns #VALUE.

This is the code i am trying to evaluate

SUMPRODUCT(--('Retrieve Depletions'!$A$4:$A$5000=$D4),--('Retrieve Depletions'!$B$4:$B$5000=$E4),--('Retrieve Depletions'!$C$4:$C$5000=$F4),--('Retrieve Depletions'!$D$4:$D$5000=$G4),--('Retrieve Depletions'!$E$4:$E$5000=$I4),--('Retrieve Depletions'!$F$4:$F$5000=$J4),'Retrieve Depletions'!H$4:H$5000)

Can someone help with this formula,

Cell $A$24 = A cell formatted as Month and Year = July06

Cell $B$1 = a date 1/7/06 linked to $A$24

Trying to use the indirect function to ref a sheet called July06 and other ranges here a example of one range =July06!$D$2:$D$247

This is what I've got

=SUMPRODUCT(--(INDIRECT(TEXT($A$24,"mmmmyy")&"!$D$2:$D$247<="&$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!$Y$2:$Y$247>= "&$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!$C$2:$C$247="&$A2)))))

I have three pages of daily data I am working with; orginal source data, manipulated data, and my output. My output data is a daily row of six columns containing 1, 0, or -1, based on my manipulated data (1,1,0-1,1,-1). I would like by row, sum the coresponding source data (5,4,0,-3,3,-4) by the positve 1s and a second column with the negative 1s. then average the column by the number of consituents (positive5+4+3 )/3=4 & negative;(-4+-3)/2=-3.5)

View 8 Replies View RelatedHow to add Sumproduct and Min/Max functions? Using Excel 2010

I am looking for the minimum, or maximum number within a range while using the Sumproduct function.

=SUMPRODUCT(--ISNUMBER(FIND("R",CMSB3003.xls!$A$12:$A$20000)),--ISNUMBER(FIND("Reg",CMSB3003.xls!$AF$12:$AF$20000)),

--(CMSB3003.xls!$B$12:$B$20000>=$B$2),--(CMSB3003.xls!$B$12:$B$20000

Looking to nest or combine the following 2 functions because I'm looking up the same cell content, except within 2 different tables.

=IF(B12="","",SUMPRODUCT(--($B$12:$B$17=X18),--($S$12:$S$17))) and =IF(B22="","",SUMPRODUCT(--($B$22:$B$32=X18),--($S$22:$S$32)))

Right now, these functions works well from a single table but need to combine them. Other function examples are welecomed too.

All I am doing is counting text values in a table.

The table has 3 main columns(which are relevant to this thread anyway).

Shift - Area - Status

The example I have attached shows examples of DCOUNTA, SUMPRODUCT and a Pivot Table.

I have read many threads stating that the best one to use is Pivot Table followed by DCOUNTA followed by SUMPRODUCT.

The most effective for me seems to be SUMPRODUCT (although this does slow excel down dramatically when you use a lot of these formulas). As do Array Formulas

The Pivot Table does not update on its own, therefore constantly needs to be refreshed. (I could use code to do this)

The DCOUNTA seems to be the least effective at doing what I want (unless I am doing something wrong)

In the attached example can the DCOUNTA be used more efficiently as I don't like the fact that I am duplicating rows to apply the criteria for a different shift. e.g

Area 1 - Late Shift - Banned

Area 1 - Early Shift - Banned

I want my table to be as follows (as the SUMPRODUCT shows)

AREA - Early Shift - Late Shift - Night Shift - Area Total

Area 1

Area 2

Area 3

Area 4

Area 5

Shift Total

I have a workbook with 17 sheets, is there a easy way to get all 17 sheets combined into one sheet?

View 9 Replies View RelatedI have a formula that uses the max function/formula (I am not sure of the terminology) to summarise the maximum value contained within a cumulative list of monthly expenditure in cells F12 to F60 (i.e MAX(Cashflow!F12:F60)) of a very old sheet I am working on, I was wondering if it was possible to have a cell that displays the profiled expenditure which is displayed in column E adjacent to the cell containing the maximum expenditure.

I have tried using the vlookup formula however combing formulas!

Sample Excel with Frequency.xls.Attached a sample sheet with some data. What I'm trying to do is combine the frequency function and add another variable. Not sure how to do this. I'm using Excel 2003, so I don't think I can use "countifs".

View 2 Replies View RelatedI am having a list consisting of two columns. Column A describes a "product" and the currency directly below. In column B i have the quantity of the product and below the value. Now i have products in USD and EUR and would like to only sum up the quantity of all products which are in USD. A simple example is attached and i calculated the target valua manually. The values i would like to sum are market in red.

View 1 Replies View RelatedI have hundreds of numbers in column A and I want to combined these numbers in call B1 with “ , ”

e.g.

column "A"

451

45

61

48

194

Call "B1"

451,45,61,48,194

I am using attached sheet for this thing but I can’t use any other new sheet, is there any another way to do this. I need formula. So I can use any sheets.

I have a Yes/No list that I use in two cells for data validation:

Cell A1 has the Yes/No list for data validation.

Cell A10 has the Yes/No list for data validation.

I want A10 to become No if the user selects "No" in the A1 pull-down list.

I am trying to combine 2 dates into the 1 cell separated by the word "TO:" So I have: =B2&" TO: "&C2

Which results in: 40211 TO: 40240

What I need is: 02/02/10 TO: 03/03/10

I have about 5 nested if statements in 5 different cells. The problem is I need to combine all 5 nested if statements into 1 cell. Can someone show me how this is done. I don't want to try vlookup since the ifs are already written. I named all of the formulas as one, two, three, four, five to help me keep track of them. Need to figure out what I have to do to combine all of the if statements into 1 cell.

View 4 Replies View RelatedI am trying to write a formula using IF, OR & AND combined together, but I can't see to get my formula in the correct order. I have tried moving several sections around but I still keep getting an error.

IF(OR((A2="1A",A2="2A",A3="3A",AND(IF(OR(B2="Q4 2013",B2="Q1 2014"),"Y","N")

The goal is to count up the number of rows in a database that have both properties:

1.) empty cell in Column B

2.) "XYZ" found somewhere in column D

I am attempting to create a vlookup formula that will count or sum a series of data so that I can pull it over into a summary sheet on a report. The vlookup formula that I am using is

=IF(ISNA(VLOOKUP($A34,'Jan 14'!$N$2:$AF$36,12,FALSE)),0,VLOOKUP($A34,'Jan 14'!$N$2:$AF$36,12,FALSE))

It seems to be working I just cant figure out how to incorporate the count or sum function into it where I need it.

Test Sample File.xlsx

I have attached a sample of the file I am working with.

I need to make a Combined Name List from several tables by formula or Macro code.

Attached file defined detail and what is exact wanted.

Unit&ValuesList.xlsâ€Ž

How to correctly sort data when it contains both text and numbers.

This is what is currently happening

LA100LA102LA109LA11LA117LA118LA12LA120LA121LA199LA21LA216LA220LA221/SLA227LA229LA23LA230LAK102LAK107LAK11LAK117LAK120

BUT I NEED IT BE IN ORDER LIKE THIS -

LA11LA12LA21LA23LA100LA102LA109LA117LA118LA120LA121LA199LA216LA220LA221/SLA227LA229LA230LAK11LAK102LAK107LAK117LAK120

I know how to use the large function when looking for a specific criteria as the example below illustrates:

Code:

=IFERROR(LARGE(IF('2012'!$D$1:$D$7="Green",'2012'!$C$1:$C$7),1),"-")

What I'm trying to do is take this one step further and not only find the nth largest numbers in a set of data based on a particular criteria, but also sum those numbers because they repeat in a table: for example a sample table is below:

Account Accout # Store # channel $ sales

A 1000 10001 green $100

B 1001 10011 green $230

A 1000 10002 green $120

C 1002 10021 brown $145

A 1000 10003 green $100

D 1003 10031 red $20

B 1001 10012 green $50

So what I'm basically attempting to do is bring in the nth largest accounts within the "green" channel. Now if these were the top 5 stores I was looking for, the formula from above would suffice. However since this deals with accounts and the account # repeat I need to bring in the total sum of those repeating accounts instead of just one of the unique stores. So if I was seeking the largest account (NOT store) within the "green" channel the correct values this formula would be:

Account A $320

I would imagine we would need to combine a sumif with a large function or maybe involve sumproduct somehow.

My excel sheet contains inventory of products and quantities, each is in a form of combined number followed by text without a delimeter. For example 2AWS means Two (2) of AWS.

Each cell in my excel database may contain pair of products, each is defined in the above form but the pair of products are separated with a comma.

I need to calculate the total number of each product and split in two different cells.

For example:

Cell A1: 3GFG, 2AWS

Cell A2: 2NG, 1AWS

Cell A3: 1NG, 4GFG

Desired Output

==============

GFG7 (i.e. Cell A5: GFG , Cell B5:7 etc.)

AWS3

NG3

I receive a monthly spreadsheet from our trucking company which tracks the package and weight of that package. However I need to pull the weight out and run a report on total weight. However it is a mix of pounds and kilograms. Please see my example. What I want is a final column of all weight converted to kilograms. Note as well the spacing in the data as sometimes the "lbs" is right next to the number and other times there is a space between them.

Raw Data

package 1

10.2kgs

package 2

5 kgs

[Code] ........

I'm trying to run a match function combined with If and Iserror. I need this because I want to see if a given item number in one cell in one sheet in excel is ALSO located in a given range in another excel sheet. If that number is found, I want the result to say "yes". If the item number is NOT also found in the given range in the other workbook, I want it to say "no". This is what I have so far:

=IF(ISERROR(MATCH($C9,'Case Usage Summary'!$A$2:$A$45138,0))=FALSE,"Yes","No")

The problem so far is that this is actually giving me the wrong results. For instance, the above function attempts to located cell C9, denoted as item number 964, in another sheet called Case Usage Summary in the range between A2 and A45138. If it could find 964 in that range in the Case Usage Summary sheet, it should give an answer of "yes", if it couldn't then it should give an answer of "no". The problem is that it gave me an answer of "no", but I just looked through the list of item numbers in the case usage summary sheet and there IS in fact a item number 964 entered in the range. What is going on here? I tried this for some of the other cells, and it is just giving me "no" for all of them. What is going on?

My spreadsheet looks something like this:

ID

Responsibility

Criteria One

Criteria Two

Criteria Three

Numbers

Functional Column

ID1

Marc

lorem

lorem

lorem

1

x

[Code] .......

I want to build a little report on another worksheet that goes over the table and counts cells (i.e. data records) based on the entries in the last column (i.e., if the corresponding data-record has an "x" in that column, then it should be counted).

As every data record also carries a numeric value in the "Numbers" column, I wanted to do this with COUNT formula combined with an IF-formula.

The formula to count the corresponding data records that match the criteria then looks something like this:

=COUNT(IF($F$1:$F$6="x";$E$1:$E$6))

However, this always counts all cells in the "Numbers" column. I want the formula only to return the range of "Numbers" cells, that belong to the matched data records.

Is there an easier or cleaner way to do this?

I have a Workbook(wb1) that pulls information based off of IF(and( and vlookups from multiple other workbooks(wb2 thru wb12). Is there a way the member typing in wb2 - wb12 able to change the Tab name one each sheet? Since the formulas i use use the tab name on the sheets for referencing. I'm hoping to do the example with out any user input.

Example ....

I need to create a macro wheather it be for each row or perhaps a dynamic macro that based on the active cell it can then offset based on my Expense type(Column B) into the respective row.

i had placed a sample of what i need to come up with highlighted in red. I need to copy the data in C7, C8, C9, etc... into D7:N7, D8:N8, etc... I have over 950 rows this code will need to be copied to. Columns do not move.

So if choose "fixed then i need for the macro to copy and paste data in each of those cells for Febuary to December. Should i choose "variable" then it places a "0" for Febuary to December and should I choose "NA" then nothing happens and i dont need code for that. It needs to be running constantly that way i dont need to run the macro so it needs to be in a module i beleive.

My problem is that i have three years of data and i have over 950 rows for expenses. THis is just a morsol. I cant place code in the cells as the expenses go there and it will just over write the code and is useless to me. That is why i need a macro that based on what i choose it then copies January's expense appropriately and for now it doesnt need to be dynamic but i prefer that it be based on the active cell if this is possible as i used over 950 option buttons and it worked but as the file gets bigger it crashes my computer.. 'Not enough Resources" .. gigs of ram and a 3.6 processor..

Copyrights 2005-15 www.BigResource.com, All rights reserved