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.
View 5 RepliesI'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"
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))
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).
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.
a1=good
a2 = 100
b1 = bad
b2 = 50
c1 = good
c2 = 100
would like to sum a2+b2+c2 only if a1,b1,c1 = good.
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"))
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.
=SUMPRODUCT(N(INDIRECT(""&Sheets&"!"&ADDRESS(MATCH($A$12,$A:$A,0),MATCH($G$3,3:3,0)),FALSE)))
I'm getting #REF! and when I step through it, it fails when it reaches the Indirect statement.
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))
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.
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?
=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
Is there anyone know how to Use sumproduct formula in macro?
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.
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.
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)
I'm looking for a formula for use in powerpivot, that can convert the following formula (I do not wanna use any VBA, only generate a caluculated column in powerpivot, based on a formula)
[Code] .....
The formula checks for 3 different values, and returns a status code for each. If no match is found it returns "no match".
The formula looks for the criteria (1234 or 1857201 or 1857206) in column2 (item) and writes a status code in column3 (output). At the same time it also checks for simular order numbers (column1). So the same status code is present for all order-number rows. (If a match in column2 / item is found)
See the excample in regular excel here: [URL] ....
I have a sumproduct formula based on some criteria, but I don't know how add another criteria wherein I need to exclude in the count if the date in column F is 1/1/2009
Attached excel file for reference. LE26dec.xlsx
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] .....
Still finding the SUMPRODUCT function a bit challenging to fully grasp.
This formula works great to sum the miles (Q16:Q38) the team (A16:A38) drove in a particular month (B16:B38)
=SUMPRODUCT(--ISNUMBER(MATCH(A16:A38,INDIRECT(E3),0)),--(MONTH(B16:B38)=E7),Q16:Q38)
How would I expand it to check yet another column say (S16:S38) for the term " Charge Call" ?
So I can see the miles the team drove in March for all the charge calls.
Where can I go to study and learn more about SUMPRODUCT. Seems like just what I need to build interactive type dashboard reports.
Is there a simple way to find the min and max value for my formula below
HTML =SUMPRODUCT((Data!$J$2:$J$10000="E700")*(Data!$M$2:$M$10000>'Rental Rates'!$A3)*(Data!$M$2:$M$10000<'Rental Rates'!$B3)*(Data!$M$2:$M$10000))
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:
=SUMPRODUCT(--(Z413:Z432="G");--(AB413:AB432<-3);--(AC413:AC432>0,4)*(AD413:AD432>5))
and this one, which sums the values of the same
=SUMPRODUCT(--(Z413:Z432="G");--(AB413:AB432<-3);--(AC413:AC432>0,4);--(AD413:AD432>5);(AD413:AD432))
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.
I'm not sure what I'm doing wrong with this formula in Excel 2003. I figured out how to do it in Excel 2007 using the SUMIFS statement, but alas Excel 2003 won't take a SUMIFS statement.
I have data in a column, J of Worksheet B that I need to add based on 2 conditions. The first conditions are in cells AJ6 to 8 on Worksheet A. The second condition must be matched from Column A of Worksheet B to the same condition in Column F on Worksheet A.
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))
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.
View 2 Replies View Relatedfix cell E8-E19 (totals). I don't think its anything to do with the date format.
View 2 Replies View RelatedI have the following formula that sometimes returns an #N/A value. Is there a way I can stop that from happening?
View 9 Replies View RelatedI have 2 columns of data being populated by vlookups
Column H is both numbers and text. Column I is Text and blanks. I need to be able to find only numeric values in column H greater than 0 and compare those occurrences with the corresponding cells in column I and if column I has a text entry (not a blank space) than to count that and at the end give me a total number of times these 2 criteria are met. As an example.
If column H has a text entry then don't count it.
If column H has a number less than zero then don't count it.
If column H has a number greater than zero but column I is blank then don't count it.
If column H has a number greater than 0 and column I has a text entry then count it.
I've tried using many variations of a sumproduct formula and none of them work.
This formula counts all instances where column I has a text entry without checking column H for a number greater than 0.
=SUMPRODUCT(--(H2:H110>0),--(I2:I110<>" "))
Or it's possible that the formula is counting the text entries in column H as a number greater than 0 but I'v tried excluding text using this..
=SUMPRODUCT(--(H2:H110>0&<>"*"),--(I2:I110<>" "))
but this causes an error in the formula somehow that I can't figure out. I even tried this
=SUMPRODUCT(--(H2:H110>0&"*"),--(I2:I110<>" "))
and I get a formula that counts only the times text appears in column H and column I together which is not what I want either.
I'm self-taught on Excel so I know there's a lot I'm not understanding about creating formulas like this but I need to have this working by Friday and I just want it to work.
Is it possible to use the Sumproduct formula to search for a particular word
and grab the amount in the next column, but 1 row down? For example, I would
search for region 1, but want to grab the figure in the total loans column
next to Consumer.
Region Loan Type Total Loans
--------- ------------ --------------
Region 1 Commercial 150000
Consumer 75000
Region 2 Commercial 90000
Consumer 145000
With the formula I have as:
=SUMPRODUCT(--(GLCodes=$B7),MasterNetAmt_01)
I want to dis-include the CC="06" in the summation. I tried using:
=SUMPRODUCT(--(GLCodes=$B7),(cc="06"),MasterNetAmt_01)
But it gives a wrong calculation
I use "--" because i have character in the cell