SUMPRODUCT-formula With Changing Conditions

Feb 8, 2009

I have a few varieties of SumProduct-formulae that are very helpful when dealing with numbers and/or letters in set conditions.

Like this one, which counts rows where conditions are met in four columns:
and this one, which sums the values of the same

How does one make these two do their respective jobs when one or some of the columns are conditions for the next column - with their changing values, and thereby changing conditions?

Instead of "bigger than 0,4" as the condition for the AC part, it should be something like "bigger than it's AD counterpart" (AD value on the same row).

I have a few combinations of these "internal" measurements to do (the AB in addition being < than a certain percentage of the AC, and so on) but first of all I need a solution to the placement of the > and < signs.

SUMPRODUCT Formula - Multiple Conditions?

Dec 6, 2009

Can a sumproduct formula accomodate multiple criteria?

The following is a sumproduct formula, for just one condition.

SUMPRODUCT(--(A1:A100="Red Sox"),--(B1:B100""))

3 Conditions - SUMIF Or SUMPRODUCT Or...?

Jan 6, 2009

I have 4 columns, A B C and D. They are a mix of formats.

A= Name (text)
B= Date
C= Code (text)
D= Number

I need a formula that gives me the total from column D, provided conditions for columns A, B and C are met. The date condition is month only, so I'm using MONTH(1) for January etc.

I can find a formula if there are only three columns total (using SUMPRODUCT), but not 4.

Sumproduct To Count From 2 Conditions...

Jan 7, 2009

I'd like to use a sumproduct function to count 2 conditions. I want to add the number of times the number 0 is entered in Column D when a 1 is entered in the same row within Column C next to it.

I'm using the formula below yet its wrong.... it gives the answer of 7 rather than 1 (see data in attached file).

=SUMPRODUCT((C3:C124=1)*(D3:D124=0))

Sumproduct With Multiple Conditions

Apr 10, 2008

My sumproduct has multiple conditions - is there a limit to the number of multiple conditions one sumproduct formula can have? I didn't think there was????

The formula looks like this, and should return results - at the moment, it returns #N/A. Does it have anything to do with the fact that I'm using named ranges?

=SUMPRODUCT(--(Data!W:W>=Cumulative!A12)*(Data!D:D=Super)*(Data!E:E=Region)*(Data!Q:Q=EWC)*(Data!J:J="H")*(Data!L:L="Tonnes"),Data!K2:K65536)

Sumproduct Where Two Conditions In The Same Column

Jan 16, 2009

I am trying to get sumproduct to work on a table where two conditions in the same column must be true (ie two employees must both have worked on the same project) in order to calculate a result. Trouble is my formula doesn't produce anything but a big fat zero.

Column A contains the list of projects. Column B has the list of employees. And column C has each employee's cost. So:

=sumproduct((column A = project1) * (column B = Joe) * (column B = Mark) * (column C))

=total cost of Project1 when both Joe AND Mark work on it.

Unfortunately, when I structure sumproduct this way, it returns zero.

SUMPRODUCT - Count Multiple Conditions

Feb 16, 2006

Ive started using the sumproduct function to count multiple conditions which is useful

howveer if i want to count those records in one column that meet a condition and those records in another column that meet anyone of a number of conditions how can i do that?

the only way i can think is like the below

=sumproduct(--((columnA=apple)*((ColumnB<>Red)*(columnB<>Yellow))))

Rather than having to eliminate red and yellow i would like to say is green or blue.

Sumproduct - Average With Multiple Conditions?

May 7, 2012

I need to average with multiple conditions. Is there a way for sumproduct to do such a thing? How to average with conditions?

Changing Colours On Conditions

Mar 5, 2008

i am creating a spreadsheet to account for work being done on a team. At the moment the sheet adds up the average time that the whole team is taking to do a task,and it also adds up the time each person is taking.

so for instance: KL take 100 minutes to do the task. but the average time across the team is 90 minutes per task.
i have my list of staff with their times in column E. so what i want is if their time is higher than the average then the box turns red, if it is lower then green etc.
(the average figure is got by summing everyones totals in box B22.

Changing Cell Colour On Certain Conditions

Sep 15, 2009

I am an Excel newbie and would like your help to develop the attached spread sheet. I would like the colour of cells A4 to H4 to change colour when the date in column B is plus 10 days and the same to happen to A5 to H5 so on and so on

I would also like the cells A4 to P4 to change colour when a quote goes to become an order an we enter data into J4 to P4 again the same thing to happen in subsequent lines as data is entered in the relevant line.

Basically I want a quote of 10 days or more to show as a different colour so that an employee can chase the quote. Once the quote become a firm order I want the cells to become a different colour again.

Changing Range Of Sumproduct Function Depending On Length Of Data Series

Feb 20, 2007

I have a small problem using the �sumproduct function� which I am using it to calculate the area under curves using the following equation (=SUMPRODUCT((B10:B109-B9:B108)*(D10:D109+D9:D108))*0.5). However I have hundreds of curves to calculate the area under and the length of the data series for each curve is different, I am currently changing the length of the data series by hand. I have attached an example worksheet of 2 sets of data (the formula I am using at the moment is highlighted in blue � row 203). Is there are function or formula that I can use inside this equation to change the range of the formula depending on the length of the data series?

Changing Colors In Specific Cell When Conditions Met In Another Cell

Apr 22, 2014

I need to change the color of a specific cell when conditions are met in a different cell and it seems like it may be beyond the scope of simply applying conditional formatting. What vba code would look like in proper syntax to make the following statement true. IF cell value Q4 is >=cell value S4 AND cell value R4 is <100 Then cell Q4 turns red and IF cell value Q4 is >= cell value S4 AND cell value R4 =100 then cell Q4 turns green. it may also be useful to know that the "cell values" in this example will be dates and not straight values. this may be possible to do using conditional formatting, but i cannot find a formula that works.

Add 2 Conditions To Formula Or Modify Existing Formula?

Dec 22, 2012

I'm trying to add either a new condition to a formula or modify existing formula, so blank cells can't be taken as a number like 0 Here is the formula =IF(N14>0,1,0)

***This is what I do with formulas with similar problems =IF(COUNT(Y14,A14)=2,Y14/A14*100,"")

unfortunately the above condition its already a condition by itself so I don't know what to do whether to modify condition or add a new condition which I don't know how to do.

IF Formula In SUMPRODUCT

Dec 9, 2009

I'm trying to write a SUMPRODUCT formula (cell H2 in the attachment) that gives different outputs according to the value of another cell (H1), but the output values are incorrect. I suppose it's because the "else" value given in the IF formula won't be recognized as a formula but as a text.

SUMPRODUCT / AND Formula

Jul 11, 2007

I'm currently using a series of SUMPRODUCT formulas to populate a summary page from a large data source. This works fine in the most part, however one part of the summary page calls for a number of different criteria to be counted within the same data range. I'm currently using a work around by having three seperate SUMPRODUCT formulas on hidden rows, and then totaling their answers to give me the result I want, however I wondered if there was a cleaner and better way to do this? An exampel of the formula I am currently working with is below:

=SUMPRODUCT(('Filtered Data'!\$A\$2:\$A\$10000="Stock-Out")*('Filtered Data'!\$I\$2:\$I\$10000="Open")*('Filtered Data'!\$Q\$2:\$Q\$10000="A"))

The final part of the formula is what is causing me the problem - I would like it to look for and count occurances of "A" , "B" , & "C"

Sumproduct Formula - #Value! Returned

Jan 29, 2009

I've got a workbook where sheet "Raw Data" is used to enter audit findings. Subsequent sheet "Analysis" contains formulas to extract quantities and nature of audit findings so that they can be shown on quarterly reports.

When I set up the workbook the formulas on the "Analysis" sheet worked fine. The department has filled the columns with data and now all the formulas are returning #Value! There's something fishy going on here.....

=SUMPRODUCT(--('Raw Data'!\$H\$7:\$H\$1000=A4),--('Raw Data'!\$K\$7:\$K\$1000=C\$1))

=SUMPRODUCT Formula Failure

Feb 24, 2009

No doubt due to a fundamental lack of understanding my SUMPRODUCT formulae (please see attached workbook) is not working.

Can someone be kind enough to point out the error of my ways? I've tried every possible combination (except the right one).

Sumproduct In Vba (formula Array)

May 6, 2009

I have a function that has a formula sumproduct and here it is:

Set RSheet = Worksheets("Rawdata")
Set RSheet = Worksheets("Rawdata")

RSheet_lastRow = RSheet.Cells(Rows.Count, "A").End(xlUp).Row
RSheet.Range("AJ2:AJ" & RSheet_lastRow).Formulaarray = "=SUMPRODUCT
((V2>=ProjectedStarts!\$K\$1:\$K\$45)*(V2<=ProjectedStarts!\$L\$1:\$L\$45),ProjectedStarts!\$M\$1:\$M\$45)"

The problem is that when the formula is sent to each cell V2 (bolded) does not change to V3, V4, V5.

Sumproduct Or Sumif Formula

Aug 24, 2009

a1=good
a2 = 100

b2 = 50

c1 = good
c2 = 100

would like to sum a2+b2+c2 only if a1,b1,c1 = good.

Simplifying A Sumproduct Formula

Feb 8, 2010

I'm using Excel 2003. I have been using SUMPRODUCT to look at too compare columns and data that is contained in both. Column L11:L52 contains the numbers 1 to 4 and Column K11:K52 contains the word YES in some cells. I just want to look and see if text is contained in a row in column L is it contained in that row in column K. This is the formula I'm using but it seems over complicated and I'm sure there is a way of just looking if the cell contains information without worrying what it is.

=SUMPRODUCT(--(Data!\$L\$11:\$L\$52=1),--(Data!\$K\$11:\$K\$52="YES"))+SUMPRODUCT(--(Data!\$L\$11:\$L\$52=2),--(Data!\$K\$11:\$K\$52="YES"))+SUMPRODUCT(--(Data!\$L\$11:\$L\$52=3),--(Data!\$K\$11:\$K\$52="YES"))+SUMPRODUCT(--(Data!\$L\$11:\$L\$52=4),--(Data!\$K\$11:\$K\$52="YES"))

SUMPRODUCT With Indirect Formula

Jul 15, 2013

I am trying to sum the same data across several tabs but I am using information in the column and row headers to find it. For some reason this formula wont work and I can't figure out what I'm doing wrong.

I'm getting #REF! and when I step through it, it fails when it reaches the Indirect statement.

Sumproduct Formula With Dates

Apr 12, 2007

I have a workbook which contains multiple lines of data. In column H it contains a date. I want to count how many of these dates occur in January. I also want to be able to add other criteria as well. Example, column H = Janaury and column D=Open. In cell B2 I have the number 1 which represents �January�. Below is my formula but it returns an error.

=SUMPRODUCT((MONTH('Raw Data'!H:H)=B2))

SUMPRODUCT(OFFSET( Formula

Jun 20, 2007

I would like to have a sumproduct formula to sum up in a paticular table in my worksheet. But its in a weird table format. Look in cell A1 that is my criteria it should sum cells G24 thru G28 and it should return the total of 1.8000. The rows could varies from time to time in those table formats. Does anyone knows how to accomplishment this.

******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutG27=
ABCDEFG1114Y1.8000 2 114C 3 Material #DescriptionMan/Machine ScrapMaterial 4 Operation # Std/Rate lbs5 100510 6 101010 0.56207 8 9 10 114D 11 Material #DescriptionMan/Machine ScrapMaterial 12 Operation # Std/Rate lbs13 14 S-SO2 0.400015 100510 0.895016 101010 0.200017 18 19 20 114Y 21 Material #DescriptionMan/Machine ScrapMaterial 22 Operation # Std/Rate lbs23 24 100910 25 670114X 1.000026 670114C 0.400027 670114D 0.4000Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

SUMPRODUCT Formula- Getting #VALUE! Result

Nov 21, 2007

I'm trying to use the sumproduct formula to query 3 columns: I want it to look in column E for either a "P" "C" or an "A", multiply the corresponding numbers in column D and F, and put the sumproduct total in cell C5, D5 and E5 for each formula below. The problem is that I keep getting #VALUE! result. It's because some cells in the range have #N/A in them, but I can't control that.

Here is my formula:

=SUMPRODUCT(--(E9:E70="P"),D9:D70,F9:F70)

=SUMPRODUCT(--(E9:E70="A"),D9:D70,F9:F70)

=SUMPRODUCT(--(E9:E70="C"),D9:D70,F9:F70)

How can I use this formula if for example cells D15-17 have no values or are #N/A?

How Does This Formula Work- Sumproduct

Oct 18, 2008

=SUMPRODUCT(--(\$C\$1:\$C\$17="S"),--(\$C\$2:\$C\$18"S"))

how does this formula work?

It counts the number of instances in a list but not sure how

does the ranges have to be differant for this to work? ie C1:C17 C2:C18

Use Sumproduct Formula In Macro

Oct 18, 2008

Is there anyone know how to Use sumproduct formula in macro?

Countif/sumproduct Formula

Oct 22, 2008

I have a sheet with repeated dates for several months and I need to break out data by week and then by certain criteria. I can do 1 or the other but combining the COUNTIF formula and the SUMPRODUCT formula has proven to be beyond me.

I have this now:

=SUMPRODUCT(--(D2:D31719"7/19/2008"+0),--(G2:G31719>"5"))

but it returns a value of 0 which is incorrect.

What I need to do is have the formula return a sum of all of the fields in colG that are >5 within a date range. Once I find that # I have to divide it by another field and multiply by 100 to get the percent.

Sumproduct Formula Returning #VALUE

Jan 19, 2010

I have the following SUMPRODUCT FORMULA, Ive tested all the data in the columns. It's returning a #VALUE. Can someone explain?

=SUMPRODUCT((A1:A10="Ford")*(B1:B10="June")*(C1:C10))

I'm using Excel 2003, if that is a factor.

Sumproduct Formula Returns #Value

May 20, 2006

Some reason my sumproduct formula returns #Value!

=SUMPRODUCT((Data_2005!\$A\$2:\$A\$170=A\$2)*(Data_2005!\$F\$2:\$F\$170=\$A5)*(Data_2005!\$S\$2:\$S\$170))

wb is attached (stripped down for upload)

Using If AND OR Conditions In The Formula?

Jun 19, 2014

I am having trouble with formulas below, when i try to use AND, OR, i having trouble .

=IF(B1=SG,IF(A1="I",0,IF(A1="A",0,IF(A1="P",0,IF(A1="D",0,IF(A1="W",0,IF(A1="O",0,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
=IF(B1=LQT,IF(A1="I",150,IF(A1="A",150,IF(A1="P",150,IF(A1="D",44,IF(A1="W",44,IF(A1="O",44,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
=IF(B1=MLT,IF(A1="I",151,IF(A1="A",151,IF(A1="P",151,IF(A1="D",0,IF(A1="W",0,IF(A1="O",0,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
=IF(B1=IBW,IF(A1="I",151,IF(A1="A",151,IF(A1="P",151,IF(A1="D",10+(C2*15%),IF(A1="W",0,IF(A1="O",0,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
=IF(B1=JL,IF(A1="I",151,IF(A1="A",151,IF(A1="P",151,IF(A1="D",0,IF(A1="W",0,IF(A1="O",0,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))