Sumproduct Of 2 Criteria In A Single Array

Jul 16, 2007

I want to use Sumproduct function to sum up the values that belong only to Product "PXT" and "PCT". I enter it as array but my formulae doesnt work. can someone give me a hand. Here is my formulae: =SUMPRODUCT((C2:C10="PXT")*(C2:C10="PCT"))*(A2:A10)

ABCD
1ValueQtyProduct
2299.944PXT
3186.53PXT
4711.071PCA
5561.862PCT
6608.961PXT
7520.026PCT
8427.682PCA
9397.341PCA
10387.664PCT

View 2 Replies


ADVERTISEMENT

Single Cell Array Or Sumproduct Formula

Jun 16, 2009

To calculate the result for a month is easy, but I can't figure how to get a single cell formula to calculate for the year. The sample attached explains it better.

View 4 Replies View Related

Sumproduct On Array

Sep 9, 2009

There is a date field and three "attempt" fields.
If The first attempt was a fail and the second a pass this must count as a pass and not a fail. If the first attempt was a fail and second was a fail then this must count as a fail, same again for third column.

On the totals tab there should be a total against each date for the fails. As you can see iv tried doing by sumproduct but its not working, i understand why its not working but i just can't see how I can get it right.

View 6 Replies View Related

Populate Using SUMPRODUCT Or ARRAY

Apr 3, 2009

formula to take data from a column entry to a row style? I've attached a sample spreadsheet.

View 9 Replies View Related

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.

View 3 Replies View Related

Sumproduct Excluding An Array

Dec 16, 2009

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.

View 4 Replies View Related

Array In SUMPRODUCT = NOTBLANK

Nov 14, 2008

I need to finish a final bit of tweaking in the following formula..

=SUMPRODUCT(--('[MRP Data File.xls]ZMM_CHECK_MRP_AREA'!$A$1:$A$10000=A9),--('[MRP Data File.xls]ZMM_CHECK_MRP_AREA'!$D$1:$D$10000="15BG"),--('[MRP Data File.xls]ZMM_CHECK_MRP_AREA'!$I$1:$I$10000""),'[MRP Data File.xls]ZMM_CHECK_MRP_AREA'!$R$1:$R$10000)

What I'm trying to achieve is to return the value of the cell in column R if column A=A9, D="15BG", this part is fine so far but I only want to return the value if column I = NOTBLANK

At the moment the value is returning whether it is blank or not...


For info: column I will only contain "CLC", "CLR" or totally blank

View 9 Replies View Related

Filter An Array (the Longer One) Using The Shorter Array As The Criteria

Aug 26, 2009

I am trying to filter an array (the longer one) using the shorter array as the criteria. I am currently doing this using the following method

IF(LOOKUP(lookup cell, array)=lookupcell, lookupcell, "FALSE")

I then copy and paste 'values' and filter out the 'false' to get my final result.

This has worked in the past, but for some reason that I simply can't figure out, the formula isnt working! I've attached the example, and I've highlighted a number in blue (cell E522 and C103), (that should be being found in the 'LOOKUP' function) but is returning a "FALSE". I have looked over the code and simply can't figure out why Excel isn't returning the right value.

This is obviously happening for a quite a few of my numbers, as my filter result is returning an array that is about 1500 shorter than it should be. I have highlighted E522 as the 'example cell' to look at.

View 6 Replies View Related

SUMPRODUCT Array Reference Causing #N/A

May 27, 2009

Using Excel 2003, I'm summarizing info in one sheet (Review) with data in another worksheet (Work Orders). The following SUMPRODUCT calc worked fine...

View 2 Replies View Related

Conditional Sumproduct Array Formula

Nov 1, 2013

I need to sum several non-contiguous columns if certain other columns meet a certain criterion.

Details:

Need to sum columns Q,R,S,T,U,V,W,X,Y,Z,AC,AD,AI,AJ,AK,AL,AM,AN,AP,AQ,AR,AS,AT,AU,AV,AX,AY,AZ,BA,BC,BD,BE,BF,BH,BI,BK,BL,BM,BN,BQ,BR
in sheet "(1) 65539004 SAP"

IF

column D in sheet "(1) 65539004 SAP" = A2 in sheet "(4) Brand Breakout" AND column L in "(1) 65539004 SAP" = $B$1 in "(4) Brand Breakout"

The formula will be placed in C2 in "(4) Brand Breakout" and copied to other cells just FYI,

View 9 Replies View Related

Array Formual Or Possible A Sumproduct Formula

Sep 9, 2008

I think I need an array formula or possible a sumproduct formula, to make this work

=SUMIF($BU$36:$BU$66,"X",T$36:T$66)

Column BU is a column that gets an X auto added to it each day, the date field runs down my spreadsheet from row 36 to 66. I use the X as month to date signifier so each day before todays date has an X in column BU. The formula above will then take whatever is in column T and adds up only the figures enter against the date before todays date.

This method works for most of my fields but column T is an average so I can't just add up the averages.

Column T is Average Handle Time, to work out the AHT for the month I presume I need some sort of array formula that also looks at the number of multiples the AHT by calls handled each day then divide the total minutes month to date by the total calls handled. How do I fit in an array formula that only works for the days when column BU has an X in it.

The calls handled field is column P

I've tried this but it doesn't work as a formula (the sumproduct part works outside of the sum if part).
=SUMIF($BU$36:$BU$66,"X",SUMPRODUCT($P$36:$P$42,$T$36:$T$42))

View 9 Replies View Related

Whole Column Range In A Sumproduct Array

Jul 29, 2009

I've created a spreadsheet with SUMPRODUCT formulae, which is working fine for now.

However, these formulae include arrays with ranges of, for example, $G$2:$G$600. What we need to do is, instead, reference the while column as far down as it goes, forever as the range of the array. This applies to multiple occurrences.

Every formula I have found for this may work on of itself, but does not work with the SUMPRODUCT formulae I have used.

For reference, an example:

=SUMPRODUCT(--('BG NEW DB'!$D$2:$D$600=$B$2),--('BG NEW DB'!$G$2:$G$600=C$2),--('BG NEW DB'!$H$2:$H$600=$A$2),--('BG NEW DB'!$AD$2:$AD$600="Y"))

View 10 Replies View Related

Using Multiple Sum Ranges In Sumproduct() & Countif() In Array

Jul 2, 2009

My problem is :

1.In G Column I put logic for Fail and Obtained Marks.

G2=IF(COUNTIF(B2:F2,">=60")=5,SUM(B2:F2),"Fail")

2. Now in H column I want use this formula which I obtained from this forum

H2=SUMPRODUCT((G$2:G$7>G2)/COUNTIF(G$2:G$7,G$2:G$7&""))+1

To get the position of Students.

But the text value "fail" in the G2:G7 getting Position No. 1 and i've noticed the reason by using evaluate formula as well.

3. I got solution by changing "Fail" with 0 by creating column I and then column H put this formula ........

View 14 Replies View Related

Array To Match Several Results Within SUMPRODUCT Function

Aug 25, 2008

I'm using a formula (with the SUMPRODUCT function) within which I need to match the month & year from an array with 3 "true" possibilities...

Meaning that if data (month & year) from the array matches any of the 3 months & year of a specific quarter (for example July 08, August 08, September 08), the result of that section should be "True".

The data to be matched (any of the 3 months of each quarter) is drawn from a table header (showing every month of the year) by using this "monthly" formula:

TEXT(H7;"mm.yyyy") where H7 holds "01.07.2008" (but displays "Jul. 08") and returns "07.2008".

I'm trying to extend it to something like:

TEXT({H$7;I$7;J$7};"mm.yyyy") to match either July 2008 (H7), August 2008 (I7) or September 2008 (J7) for the 3rd quarter of 2008 but when I hit Enter, I get an error and H$7 is highlighted ("ctrl + shift + enter" doesn't help).

Here's an excerpt of the formula that works well for other queries on my worksheet:

=SUMPRODUCT(--(TEXT('Current update analysis'!$O$2:OFFSET('Current update analysis'!$O$2;(COUNTA('Current update analysis'!$B:$B)+20);"mm.yyyy")=TEXT({H$7;I$7;J$7};"mm.yyyy")); --('Current update analysis'!$k$2:OFFSET('Current update analysis'!$k$2;(COUNTA('Current update analysis'!$B:$B)+20)="ok"))

how I could/should structure the formula for it to work?

View 10 Replies View Related

Using Multipel Sum Ranges In Sumproduct() & Countif In Array

Jul 6, 2009

I want to get students positions in a class like 1st, 2nd, 3rd, etc. There can be two 1st position and 2 2nd positions at a time .

1.In G Column I put logic for Fail and Obtained Marks.
G2=IF(COUNTIF(B2:F2,">=60")=5,SUM(B2:F2),"Fail")
2. Then i made array formula in column H to get the position
={SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1}
Some improvement to write fail & position
={IF(COUNTIF(B3:F3,">=60")5,"Fail",SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G3))/COUNTIF(G$2:G$7,G$2:G$7&""))+1)}
And more work to get total marks & position and Fail at the same time......
={IF(COUNTIF(B2:F2,">=60")5,"Fail",SUM(B2:F2)&"-"&SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1)}
Now I wounder that is there a way to do all calculations in column H without using column G

By replacing (if there is any method) G2, G3, G4 etc. with Sum(B2:F2); Sum(B3:F3);Sum(B4:F4) etc. in below formula
={SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1}.....................

View 9 Replies View Related

Count Of Rows In Array That Meet Criteria In Column2 And Different Criteria In Column3

Dec 6, 2013

I have a table with 3 columns with an unkown number of rows (text, date, date) that is being imported daily.

I want to create a 4th column with dates starting from today and each subsequent row be one day earlier. I want to look at 30 previous days.

I then want to count the number of rows (looking at column 1-3) with the following criteria:

Countif column2<= "date in colum4" AND column3< "date in column4"

View 6 Replies View Related

Get A Single Cell To Look At An Array

Mar 10, 2009

What Iím trying to do is get a single cell to look at an array, if there is a number in that array which is between 2 limits to return that number. This is eventually going to work with limits of dates/times, and have to be updated once a day. Iíve attached a workbook with an example of the data and the way I want it formatted. {=IF(AND(A1:A25>=D32,A1:A25<E32),A1:A25,"")}

But this always returns a false. I can get it to work without using array and just having cell to cell logic but this means I would need the same amount of columns in the formatted data as the raw which is impractical.

View 5 Replies View Related

Only Include Month Of January In Sumproduct Function On Another Page Using Array

Dec 27, 2013

I only want to include the Month of January in a sumproduct function on another page using a name manager array.

I was thinking of using

=SUMPRODUCT(INDEX(DATA,0,17),--((MONTH(INDEX(DATA,0,5)=1)*INDEX(DATA,0,17))

Where the dates are in the 6th column and include blank cells at the bottom and in the first row it says Date.

View 14 Replies View Related

Efficient Implementation Of SEARCH Outside And Or Inside An Array/SUMPRODUCT (2007)

Jan 24, 2010

I need to check each of 13,000 job titles to see if they include the string "VP".

For the time being, I want to find the most efficient way to check an individual title, before building an array or SUMPRODUCT function that will check all the titles in swoop.

So... I have tried:

=ISNUMBER(SEARCH(A1,B1))

..but it returns TRUE if A1 and B1 are both blank -- which they can be in my spreadsheet.

And I have tried:

=SIGN(SEARCH(A1,B1))

but it returns #N/A if A1 cannot be found within B1.

If I can, I want to avoid introducing an IF statement because, looking ahead, I am already concerned that this analysis will be complicated enough without multiple paths.

So.. what's the most efficient way to return a TRUE/FALSE, or 1/0, result depending on whether A1 is in B1?


EXTRA-CREDIT!

An extension of the above problem... arrays and SUMPRODUCTs welcome:

What's the most efficient way to return a TRUE/FALSE, or 1/0, result depending on whether (A1 or A2 or A3... or A99) is in B1?

View 13 Replies View Related

Large SumProduct Array -- Original Title:Loop Function

Dec 19, 2007

could provide me a loop function in VBA for these calcultions.

Cell A1*E1 + B1*F1 + C1*G1
Cell A2*E1 + B2*F1 + C2*G1
......till
CellA100*E1 + B100*F1 + C100 * G1

These are the calculations for E1-G1.
The process must be repeated till E50-G50

So the second step is:
Cell A1*E2 + B1*F2 + C1*G2
Cell A2*E2 + B2*F2 + C2*G2
......till
CellA100*E2 + B100*F2 + C100 * G2

View 12 Replies View Related

Sumifs Or Sumproduct Or Array Pull Result From Range Of Columns

Oct 3, 2013

I'm trying to pull thesum of huge number of columns in this case the range of pulling the data is B2:E7 based on two criteria (B8 and B9) The result is on B11

Sheet1

*
A
B
C

[Code].....

View 5 Replies View Related

Database Functions Vs. Array Formulas Vs. SUMPRODUCT Vs. Pivot Tables

Dec 13, 2006

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

View 4 Replies View Related

Sumproduct With Two Criteria

Feb 9, 2006

I should be able to do this by now but I keep getting an #NA error. I am
trying to use this sumproduct formula
=SUMPRODUCT(($I$7:$I$2585=921),--($L$7:$L$2585="Fb")--(AS7:AS2585)) where I
am looking up store #"921" and Attribute "fb and sum where these two criteria
meet. The column I want to sum is AS7:As285. What am I doing wrong.

View 9 Replies View Related

Sumproduct With Criteria

Jan 24, 2007

In Column A1:A20, these are included 5 types of data (red, orange, yellow, green, blue); in column B1:B20, these are included the total salary amount, and in column C1:C:20, these are included "Sole" , "Joint" or "Partnership", so if column A is included red/yellow/green (excluding orange and blue) and column C is included Sole/joint/Partenship, and total salary is between 6000 and 9000, then calculate the total salary of all the customers. How can I use sumproduct function to fix it?

View 8 Replies View Related

Array Of Values To Single Column?

Aug 16, 2012

Ok I'm writing an extraction formula. I've got my array of Trues and Falses, but now I need to go through each row and check if all the columns are TRUE, and then return the row if all the columns are true.

Right now I'm just working with a dummy set of data... my real table is some 50 columns wide and over 300 rows, but this gives you the idea of what I'm doing:

I tried putting in a Countif and using it like and array to count the different rows and return the number of times each row was returned... but countif goes nuts when you try an array for the criteria.

Formula:
=IF(IFERROR(($H$2:$J$13/B$1:D$1)>=1,TRUE),1,0)*IF(IFERROR(($H$2:$J$13/$B$2:$D$2)

View 8 Replies View Related

Formula To Multiply Single Value By Array Then Sum?

Mar 29, 2013

I am looking for an excel formula that will allow me to perform the following logic: if a cell value in (M3:BA3)="D" then multiply C29 by the corresponding value in (M29:BA29), then sum all of the products.

View 2 Replies View Related

Single Element Of An Array Is Equal To A Particular Value

Jun 20, 2008

How can you test that no single element of an array is equal to a particular value?

View 9 Replies View Related

SUMPRODUCT To Match Criteria

Jun 3, 2014

I am trying to match 3 values, and return a matching 4th from a chart. It will first look at one value and see if it falls between values (the LOAD value in cell B1..it will look to see if it falls between the "low limit" and "high limit" in columns A10 to A135 and C10 to C135, respectively. Then it will look for a match for the values in cell A2, and in cell A3. the return value will index to the corresponding value in column G10 to G135. I have written a formula to do this in cell D2....but it will only ever return the value in cell G10.

See the attached excel file : nozzle tester.xlsx

View 2 Replies View Related

Sumproduct With Multiple Criteria?

Jan 21, 2014

I am looking around any way wherein I can sumproduct the values as given in attached sheet, basically I wanted to know the total MRP value of Sale and Stock

View 3 Replies View Related

Sumproduct Meeting Two Criteria?

Jun 20, 2014

I'm working on a spreadsheet at work and I'm trying to sum only the values in a column that meet criteria in the column next to it. For example, in the attached spreadsheet, I would like to have a totals line at the bottom of the spreadsheet for all three Facilities and the total next to them. I know I could do a pivot table but I know this approach would be more aesthetic and easier for my supervisor.

View 3 Replies View Related







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