SUMPRODUCT Formula With Criterion Condition???

May 8, 2009

see the attached spreadsheet. Basically I want the formula in the yellow cell to carry out a sum product of two columns, but only for rows where the value in the first (criterion) column is 10.

Change Sumproduct Formula To Incorporate Condition

Mar 3, 2014

I have have sumproduct formula that works well, however I need a section of it to use a condition.

"=SUMPRODUCT(--(('Input Sheet'!\$A\$10:\$A\$20009=3)+('Input Sheet'!\$B\$10:\$B\$20009=1)+('Input Sheet'!\$C\$10:\$C\$20009=1)=3),--('Input Sheet'!\$L\$10:\$L\$20009>=G21),--('Input Sheet'!\$L\$10:\$L\$20009<=J21))"

The first section ('Input Sheet'!\$A\$10:\$A\$20009=3) looks at column of data and checks if any of the data says 3, however I need the formula to look at the column of data and determine if the figure is =>3 and also =<5.

Enable/disable A Criterion WITHIN An Array Formula (2007)

Jun 13, 2009

I have collected some data from survey respondents, and I have the following array formula:

=MIN(

IF(\$C\$1:\$C\$400="happy",

IF(\$G\$1:\$G\$400="tall",

IF(\$H\$1:\$H\$400="american",

\$F\$1:\$F\$400

))))

What I'd like to do is make the 3rd criterion -- \$H\$1:\$H\$400="american" -- dependent on whether the string value in cell A1 ("Evaluate Americans only?") is "yes" or "no".

Because, my actual situation is considerably more complex (see below), I'm looking to insert a test within the MIN array formula to check the value of A1, and calculate the answer accordingly.

I'm aware that I could theoretically create two array arguments (one with & one without the American test), and place them inside an IF function that tests the value of A1. However that isn't practical for my real-world situation...

I occasionally see array formulas with all sorts of symbols that I don't really understand in the array context (such as * and | and . ), and I'm wondering if one of those magic symbols might be the doorway to my solution.

Sumproduct Given A Condition?

Apr 7, 2014

I am trying to arrive at the results in the yellow cells without needing the blue cells inbetween.

A sumproduct would be best but the 2nd row of blue cells has an if statement in it.

SUMPRODUCT Using A Condition

Jun 20, 2014

I would like to create a formula in D7 that calculates the weighted average growth for products (column A) manufactured by Bob (column B). In J5 I've inserted the expected result using a SUMPRODUCT function.

How to create a formula in D7 that will calculate from the data in B6:D6 the same result as in J5. i.e. How do I make the SUMPRODUCT function only consider data in columns C and D when Bob is the manufacturer?

Sumproduct With More Than One Condition

Oct 23, 2007

Here is my dilemma I have 3 columns with data ranges two of which need to match certain criteria and if the criteria is met then I want the 3rd columns data to be sumed.

For instance: I want all only want to add column c for the ones in Column a that have blue and column b that have lost.

ABC
bluelost1
greenfound2
purplelost3
bluelost4
greenfound2
redlost3
orangefound4

More Condition With Sumproduct?

Mar 3, 2009

I want to make some general tab from source in other sheet. I want to count unique product based more condition and also sum the amount for people and product.
It is better to understund in my atach.

IF ELSE Condition With SUMPRODUCT?

Aug 7, 2013

I have a SUMPRODUCT formula that checks several columns of employee data for various conditions and sums up a salary value, which works just fine. But now I have to put a cap on the salary amount when summing up each employee, which I thought I could use an IF statement for, but alas just returns a #VALUE! error. Essentially what I tried was

Code:
=SUMPRODUCT( --( Range1=A1), --(Range2=B1), IF( SalaryRng>200000, 150000, SalaryRng) )

I thought since I was returning numeric values from the IF statement there shouldn't be a problem. Obviously not. So my question is whether an IF statement can be used this way, and if not, what method should I use? SUM/IF array formula?

Sumproduct With Condition Formating

Nov 5, 2008

I have two columns from which criteria must be satisifed. column A denotes gender (m or f) and column B denotes a number of points. I want to sum those males who achieve >4 in one cell and those females who achieve >4 in another cell.

Variable Condition In Sumproduct

Nov 25, 2008

I have the following formula

=SUMPRODUCT(INDIRECT(\$B3&"I3:I1000")*(INDIRECT(\$B3&"K3:K1000")>0)*(INDIRECT(\$B3&"u3:u1000")=\$F\$1)*(INDIRECT(\$B3&"p3:p1000")=\$D\$1)*(INDIRECT(\$B3&"r3:r1000")=\$H\$1))

Cells F1, D1 and H1 contain dropdown lists matching values in the referenced columns, PLUS they all contain the option All, which effectively means that the condition should return true.

I need something like if(\$F\$1='All',1,(INDIRECT(\$B3&"u3:u1000")=\$F\$1))), but this doesn't work

Double Condition (where Sumproduct Won't Work)

Jun 30, 2008

my table is something like below:

my program sheet looks something like this:

I need a double condition formula to check the date, if matches the date Column (A), and matches product Column (b), then returns Column (C) which is the doc#.
I Could easily do it if it is a sum product formula, but in this case, it is alpha numberic.

date: 14 Jun (dd / mm)
product -- Doc#
apple ---- ac21 (so what formula do I put here?)
Orange --- abc2 (formula?)

date --- product --- Doc#
12 Jun -- apple ----- abc1
12 Jun -- Orange --- ab12
14 JUn -- Apple ----- ab21
14 Jun -- Orange ---- abc2

Sumproduct Returns A Zero In False Condition, Instead Of A Blank

Jan 8, 2007

I've written a sumproduct formula, which does what I want it to do; but it returns a zero in false condition, instead of a blank. I've tried several things, but don't seem to be progressing very far. Here's the formula that I ended up with.

Adding Condition To SUMPRODUCT Causes Result To Go Up? (2007)

Jan 22, 2010

Adding condition to SUMPRODUCT causes result to go up? (2007). I have the following formula:

Excel 2003 :: How To Use List Or OR Condition In SUMPRODUCT

Oct 30, 2009

How to include a boolean OR in my SUMPRODUCT formulas.

Software: WinXP SP2 and Excel 2003 SP3

I'll try to type in some data in legible format:

......Col B.....Col C.....Col D.....Col E.......Col F
1...NW OH....WMA.....110%....NW OH...(result)
2...NW OH....SIS.......130%
3...SO FL......PRB........92%
4...NW OH....TRO......104%

I want to sum the contents of Column D when Column B = a given text value located in Cell E1 AND Column C = text value #1 OR text value #2 OR text value #3, and plunk the result in Cell F1.

So for example if I have NW OH in Cell E1 and my values to match in Col C are WMA, SIS, or PRB, I need a result of 240%.

I am using dynamic ranges for the contents of Columns B, C, and D: CALC_AFFIL, CALC_TITLE, and CALC_PERC respectively.

I have tried:
=SUMPRODUCT(--(CALC_AFFIL=\$E1),--(CALC_TITLE="WMA")+--(CALC_TITLE="PRB")+--(CALC_TITLE="SIS"),CALC_PERC)
=SUMPRODUCT(--(CALC_AFFIL=\$E1),--(CALC_TITLE="WMA")+--(CALC_TITLE="PRB")+--(CALC_TITLE="SIS"),--CALC_PERC)

Also tried both the above formula w/o the "--" in front of any array.
=SUMPRODUCT(--(CALC_AFFIL=\$E1),--(CALC_TITLE={"WMA","PRB","SIS"}),CALC_PERC)

Again, the above was tried with and w/o the combinations of "--" in front of arrays.
=SUMPRODUCT(--(CALC_AFFIL=\$E1),--ISNUMBER(MATCH(CALC_TITLE={"WMA","PRB","SIS"},0)),CALC_PERC)

Once more with and w/o "--" combinations.

Adding Another Condition To Sumproduct (countif) Statement

Oct 6, 2011

I am using the formula below to count the occurences of relevant text strings (names) in a cell that can contain many separate strings. It works great. "References!\$F\$2:\$F\$34" contains my list of names and the formula returns how many occurences there are in each cell.

What i'd like to do know is work in a date criteria. Each name has an associated relevant date attached to it as does each cell that I am looking for these names in.

So...in english what Id like to do is alter my formula so that when it finds a text string that exists in "References!\$F\$2:\$F\$34", it then compares the associated date in "References!\$G\$2:\$G\$34" with the date associated with N3 which happens to be O3.

SUMPRODUCT(COUNTIF(N3,"*"&References!\$F\$2:\$F\$34&"*"))

Rank Criterion

Apr 9, 2007

I deal with Income statements of varying length. What I'm trying to do is figure out a way to rank only the expenses.

The account names change from sheet to sheet, and the number of accounts always changes.

However, Expenses always START after a cell in the "A" column marked "OPERATING EXPENSES" and always ENDS after a cell in the "A" column marked "TOTAL OPERATING EXPENSES". However, I don't want to rank "TOTAL OPERATING EXPENSES" because it's the sum of all the indivicual expenses.

Count If For Multiple Criterion?

Jan 6, 2014

I am working on a spreadsheet for a retail store. Column A has style numbers; Column B has an indication of A+ if that style number is a high selling item; Column C is called June and represents the number of units per style we will need in the month of June. There are some styles in June that we won't need at all and so the cell is blank for those style numbers in the June column.

I want to count all style numbers we will be ordering in June that are also indicated as an A+ item in column B.

So, I want to write a formula that says count populated cells in column C if column A has an A+.

SumIf But Using A Range As A Criterion.

Jun 18, 2009

I have a large spreadsheet, to which I add 4 weeks worth of data, from a database printout, (cut n paste) to the bottom, this could be any number of rows. However there is a column with week numbers in for each row. I can happily use =SUMIF('FORM R60'!\$H\$1:\$H\$1248,\$A1,'FORM R60'!\$AH\$1:\$AH\$1248)

This gives me the info I need on a TOTALS sheet, across various columns and rows, where the formula sits, adjusted for whichever column of data I require totals. This info is then read by another spreadsheet.

However, rather than manually change the formula (to suit a range) to add certain numbers of weeks, as I do now, I would like to include in the formula a condition say where the week numbers are >5 and <10, preferably if the condition could refer to cells.

INDEX MATCH Two Criterion, AND/OR

May 11, 2007

This INDEX formula looks at two data sets for a match in two cells on the same row

If there is a match betwen BOTH B&D columns on the same row, the code will take data from column F of the second data set and put it in the matching row of the first data set

How do I set this code to do an OR operation rather than an AND operation on the match check?

I.e. a match in either B OR D rather than B AND D?

=INDEX(custom!\$F\$2:\$F\$99,MATCH(1,(custom!\$B\$2:\$B\$99=B1)*(custom!\$D\$2:\$D\$99=D1),0))

Multiple Criterion Autofilter In Vba

Aug 13, 2007

How can we set two criterias in autofilter property in vba? For example if there are two columns - departments and grade ... i want that all those rows that have department as Finance and Grade as Grade A should be filtered automatically in VBA Coding.

Removing Auto-filter Criterion

Feb 29, 2008

Is it possible to remove autofilter criteria found in the drop down menus automatically created by autofilter?

Function Of Addition With An Only Conditional Criterion

Mar 23, 2009

I behind developed to a time a function of Addition with an only conditional criterion.
I would like to extend at least for three criteria, this function I function accurately as the function SUMPRODUCT alone that done in VBA.

Function VlookupAllSum(name As String, IntervalSearches As Range, IntervalReturn As Range) As Variant ' as integer para valores at� 32.767
Dim Valor, Nome
Dim lin, col As Integer
Dim Total
Application.Volatile
lin = 1
col = lin
For Each Nome In IntervalSearches
If Nome = name Then
Valor = IntervalReturn(lin, col)................

Sum Identical Rows Meeting Criterion

Aug 10, 2007

In each row of my data set, columns A thru E contain different three-digit numbers, in ascending order (e.g. 012 312 344 798 880). No number is repeated more than once in a single row, and no two rows contain the same set of five numbers. Column F contains a value (these are what I'd like to sum).

The variables I want to create a formula with are each three-digit numbers, call them X and Y. I want to find the pairs of rows where one row contains X in one of the first five columns, and the other row contains Y and the same other four values as were contained in the four non-X cells in the X-row (note that X and Y may or may not be in the same column, since each row is in ascending order). If X=344 and Y=955, an example of a match would be one row with 012 312 344 798 880 and another row with 012 312 798 880 955. For each X and Y combo there could be many such matching pairs of rows (with different sets of the other four values). I'd like to sum all the values in the X rows of such pairs (and then I'd do the same with a different formula for the Y rows).............

Deleting Rows NOT EQUAL To Multiple Criterion

Apr 28, 2008

I have a spreadsheet with several thousand lines on it and I only want certain rows from it. Unfortunately I cannot use a sort as I have to keep groups of data in the same order (the line count in between the data that I need left behind isn't always the same so I can't go that route either). I was hoping there was some code to delete all rows other than those where the cell in column A contains either "NAME"; "Data1" or "Data 2".

I tried the code below but that didn't work.

Return Text Results Based On 2 Criterion

Oct 6, 2008

I am attempting to create a formula that will, return text results based on 2 criterion.

This is what my sheet looks like

Name Job Full Time / Partime
Bob Admin Full Time
Barry Accounts Part Time
Chris Claims Full Time
Emma Claims Full Time

The formula I have for this is {=INDEX(A1:A4,MATCH(1,(((B1:B4)="Claims")*((C1:C4)="Full Time")),0))}

Which is fine and returns the name Chris, however as you can see from above, Emma also meets the criteria given however, I cannot get Emma to appear also. Is it possible to display more than one entry in a cell/s based on this formula, or am I missing something.

I would use a pivot table to do this, however I need the sheet to be completely automated.

Multilookup Based On Multiple Criterion Without Using Arrays

Mar 6, 2009

Multilookup based on Multiple criterion without using Arrays
Dear Forum,

I am well aware of the oft-mentioned Array Solution which gives us more than one record based on mulitple criterion using the INDEX function..

the formula layout being as mentioned below:

[ ={INDEX(A2:A100,SMALL(IF((B2:B100=<Condtn 1>)*(C2:C100=<Condtn 2>),ROW(D2:D100)-ROW(\$D\$2)+1),ROW(\$D\$2:\$D2))} ]

I like this formula a lot and have used it more often, however it has made my file exceedingly slow..

Considering that it takes every small change in the file to compute around 5 mins or more than this the whole purpose seems to be defeated, I have read a lot of posts on Array Vs Non-Array of late by DonkeyOte.

Filter And Clean Cells Based On Another Criterion Column

Apr 26, 2014

I need to filter and clean the criterion based on another column cells.

If the R column has the value 1, then it is filtered and cleaned only the cells of column J

My spreadsheet needs to stay current with the strange layout.

[Code] ....

Attached File : LayOutStranger.xlsx‎

VB To Copy Cell Value In Sheet1 To Cells In Sheet2 If Criterion Met

Feb 27, 2014

This following is just an example:

In sheet1 I have 4 columns; Name, Age, *** and Job.

Sheet2 has three columns; Name, Age and Job.

What I need is VB that will check the *** column from Sheet1 and when it is Male, the Name, Age and Job fields from that row are copied into the cells in Sheet2.

Create Checked And Unchecked Checkboxes Based On Three Criterion

Mar 10, 2014

How to create checked and unchecked checkboxes based on three criterion(in sheet 1)?for clear explanation..it will be like this...firstly,the "SELECT TAKEN only"checkbox will checked/unchecked row d20:r20.secondly,the "SELECT RETURN only" checkbox will checked/unchecked row d21:r21 and so on.lastly,"SELECT ALL" will checked/unchecked all the rows.

Checkbox1.xlsm

Add Addition If Condition To Existing Formula: Long Formula

Aug 12, 2007

This task joins a string together based on a number of characters per cell in the range.

I want to isolate one range, Col N, and add an IF condition to it.

There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference....