# Expand Array Formula Criteria

Dec 8, 2006
Need to expand the crteria on this array?

{=SUM(IF(LYIND1=1,IF(PROFILE!$G$405:$G$435="EOM",(PROFILE!$AU$405:$AU$435),SUM(IF(LYIND1=2,IF(PROFILE!$G$406:$G$436="EOM",(PROFILE!$AU$406:$AU$436)))))))-SUM($K$16:N16)}

I need to add

{=SUM(IF(LYIND2=1,IF(PROFILE!$G$406:$G$436="EOM",(PROFILE!$AU$406:$AU$436),SUM(IF(LYIND2=2,IF(PROFILE!$G$407:$G$437="EOM",(PROFILE!$AU$407:$AU$437)))))))-SUM($K$16:N16)}

View 3 Replies
ADVERTISEMENT
Oct 2, 2009

I;'m trying to create an ARRAy formula to SUM with; the formula has to match 3 different criteria's. I'm trying this with no success, here is the last one I tried.

=sum(ifs(DATA!G:G,Sheet1!A27,DATA!B:B,if(Sheet1!B1,DATA!I:I,">=0")

View 18 Replies
View Related
Dec 6, 2012

How could I add ">0" to the below formula array?

{=IFERROR(AVERAGE(IF(Reports!$A$1:$A$4000=A5,Reports!$H$1:$H$4000/86400)),"-")}

Reports!$A$1:$A4000 = column A lists multiple agent's names

A5 = a specific agent's name

Reports!$H$1:$H$4000 = column H contains a number which has been converted from time to a number

86400 = number of seconds in a day (the cell where I am placing the formula converts the number back to a time format)

The average I'm looking for will give me an average of all times which are greater than zero.

View 3 Replies
View Related
May 19, 2014

I am looking for a formula that averages the numbers in an array if they match the row and column text-based criteria. Based on another thread, I found and edited the following formula. However, it is giving me incorrect numbers.

[=AVERAGE(IF(($A$3:$A$275=$P6)*($B$2:$M$2=Q$4),$B$3:$M$275))]

I have attached a sample workbook that includes the broken formula.

Average Formula Error.xlsx

View 4 Replies
View Related
Jul 9, 2014

I need an array formula that can take average based on multiple criteria present row wise and column wise.

Check the attached sheet for detail. I need array formulae in yellow space. I have given one result that I need.

Average.xlsx

View 13 Replies
View Related
Jan 7, 2014

I am trying to do a sum of data that contains three requirements - The country has to match either the USA or Canada and then it needs to pull for only a certain month (i.e. Jan, Feb, Mar) and then for that month only pull either Airfare, Hotel, Per Diem, or Car Rental. Basically looking for a way to Sum only the specific category for a specific time frame.

View 9 Replies
View Related
Jul 20, 2006

I am creating a summary page to analyse my golf scores. I have come to a dead end when looking at the Par statistics. Does anyone fancy a challenge and try to fill in some of the blanks on the summary tab. I am trying to use array formulas based on multiple criteria. for instance under the Best Par 3 section, it would look at all the Par 3's for whichever golfer is selected and then return the lowest number.

View 4 Replies
View Related
May 6, 2014

I have a set of data (assume 2 columns, one with a long name and the second with a time). The names contain variables that must be used as criteria (a single entry may contain "Blue" and "On"/"Off") and times vary, based on when the Name turns "On" or "Off" [in minutes: 25 (On), 47 (Off), 89 (On), 100 (Off) and 137 (On)]. I need to create another table that automatically inserts values for all times, to include the missing times (0 min thru 24 and 26 thru 46, etc.). EX: If the first entry is "Blue-On" at "25 minutes", the cells from 0 minutes to 24 minutes are each "0" and become a "1" at 25 minutes - the following cells are "1" until 47 minutes (where it is turned off). (1 and 0 represent "On" and "Off", respectively)

How can I created a formula to insert the correct numbers into the correct places? I'm willing to have multiple cells with formulas and simply hide the columns that are doing the calculations.

View 1 Replies
View Related
Jul 18, 2012

I am trying to run an array formula to match two dates on two seperate sheets as close together based on another cell. Below is what I currently have on some made up values. I am entering it as an array and ideally I would like it to run down the cells changing the reference A2 depending on which cell it is then to then enter this into a macro.

The formula I am currently using is:

=INDEX(Trees!B$2:B$75,MATCH(MIN(INDEX(ABS(IF(A2=Trees!A$2:A$75,(Trees!B$2:B$75-B2))),0)),INDEX(ABS(IF(A2=Trees!A$2:A$75,(Trees!B$2:B$75-B2))),0),0))

Sheet 1 (Named: Main)

Number

Date

Formula

1

15/06/2012

[Code] ....

Sheet 2 (Named: Trees)

Number

Date

1

05/06/2012

[Code] .....

View 2 Replies
View Related
Dec 19, 2013

I can manage an array formula that returns a value based on a criteria. Simple. But I want to add in an additional couple of criteria. Now I'm stuck....

My sheet looks at a manually entered postcode, finds out what region this is in, and returns a list of postcode I have defined as being in that region. (So the postcode WF1 3JY would return a region of Yorkshire, and list postcodes of WF, BD, L, etc)

I also have a list of engineers, with a column for their home postcode.I want to be able to list all the engineers from my list whose home postcode matches any of the values on the already created list from the postcode and region entered. So far I have this, which finds me all the engineers for just one postcode area.

View 4 Replies
View Related
Feb 26, 2014

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.

View 2 Replies
View Related
Mar 31, 2009

I have a cell set up with a formula to copy the content from another cell. However, the cell won't expand to fit the text. If you delete the formula and just type in the cell, it does expand. See attachment.

View 5 Replies
View Related
Jan 25, 2014

The attached workbook has a fragment of a table . The column heads are letters representing job nos. The first row is of numbers which will always be multiples of 1000. Subsequent rows are the factors of each number by each number.

My problem is how to structure the columns of the second table so that if a number has a factor of 1 there is one copy of that column head. if the number has a factor of 2 then there are 2 copies of the head, and so on.

The table can vary in size but the first row of each column will always be an integral number of 1000's (e.g. never 1250).

I have not been able to attach the workbook as "ADDFILES" is not visible in the download screen, I hope the above description will be enough.

View 3 Replies
View Related
May 17, 2007

I believe it should be quite a simple thing...and probably has something to do with the OFFSET function...but I cannot seem to put my fingers to it.

All details are mentioned in the attached ZIP file...so I won't repeat myself here.

View 10 Replies
View Related
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
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
Jul 6, 2014

=IF(E14<=0,0,IF(N9="yes",MAX(E15*C15,30),30))

I am currently using the above formula and need to make an addition to it.

If D8 is greater than 9000 and less than 9999 then the entry will be 35 rather than 30. Any other entry in D8 would leave it at 30

View 5 Replies
View Related
Apr 7, 2009

Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.

Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?

Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.

And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.

I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.

View 6 Replies
View Related
Nov 6, 2013

Is there anyway to recreate this formula w/o it being an array ?

{=IF(C3="","",IFERROR(INDEX('Master List'!$B$1:$B$2000,MATCH(TRUE,ISNUMBER(SEARCH('Master List'!$A$1:$A$2000,C3)),0)),"ADD TO MASTER"))}

View 5 Replies
View Related
May 18, 2007

I just wanted to concatenate a few cells using an array formula like this:

{=SUM(IF(B2:J2="";0;B2:J2))}

Well, this does not work. I have no idea why it fails. Any solution to this without scripting?

View 9 Replies
View Related
Jun 30, 2006

i need to replicate what i did using array formulas with VBA macro (array variable). to make things clear and simple i created an example for illustration only. look at it & u will find what i did & what i need to do ,much of it

in writing so that i accurately describe my problem. attached is my example

View 4 Replies
View Related
Oct 17, 2012

I've been trying to sum up invoices for specific contract lines while also taking into account if the invoice was deleted. There is a deleted column in which a "Y" is entered into the corresponding row meaning this invoice should no longer be accounted for.

I've been able to sum up the invoices per contract item just fine but I can't seem to get excel to account for the deleted column. I've attached a sample workbook which has dynamic named ranges already defined, the current array formula I'm using to sum up invoices and a pivot table that shows what the correct invoice amounts should be.

View 4 Replies
View Related
Nov 12, 2006

I need to transpose into 2x2 range to use as criteria for advanced filter

I tried

arrCrit = Array("Accnt_Nmbr", "313000", "DT", "RV")

Set rngCrit = wsData.Range("IU1:IV2")

rngCrit = WorksheetFunction.Transpose(arrCrit)

Where Accnt_Nmbr and DT are Column Headers in the data to to filtered

However when I run the output includes all DT's (Document Types)

How can I expand so both criteria are used in the filter?

View 9 Replies
View Related
May 20, 2014

I would like to count the number of values in column B IF the value in Column A is equal to a value from a range of cells.

I would like the formula to be in cell E7. And the range of cells containing the values is in column D.

Here is my workbook Book1.xlsx

View 1 Replies
View Related
Nov 1, 2009

I have an array of data that i need to find multiple data points in: ...

View 11 Replies
View Related
Apr 29, 2009

I am trying to use an SUM array to count occurances of a given criteria.

Here is an example of one that works for me.

View 3 Replies
View Related
Sep 13, 2012

=COUNTIF(A$2:T$1001,V2 & W2 & X2 & Y2)

I want to copy and paste this formula down column Z.

I want to count or add 1, only when a row of 20 cells (A to T) contains all 4 criteria in V W X Y.

View 9 Replies
View Related
May 23, 2013

Filter below:

Code:

Sheets("Sheet1").Range("$A$3:$AO$64999").AutoFilter Field:=1,

Criteria1:=Array("0" _, "1", "2", "3", ""), Operator:=xlFilterValues

Now I would like to change to exclude these values, tried some tricks, like:

Criteria1:=Array("0" _, "1", "2", "3", ""), Operator:=xlFilterValues

but not working.

View 2 Replies
View Related
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
View Related
Jun 22, 2014

with a multiple criteria index match array!

I have attached an example where I need to bring back a result matching 4 specific criteria, but I cannot seem to get it to work at all!

I have attached an example dataset with the formula that I was trying to get right (and failing miserably!!)

View 6 Replies
View Related