# Array Formula Using Two Columns And Countif

May 11, 2006
Having trouble getting Excel to compute a value based on two columns on another sheet. What I need it to do is see if column A has the value I specify, then look in column B and see if that date figure is greater than 180 days, and then count it. So I might have 45 that meet criteria A, but then need to find items in that subset that meet criteria B...

View 9 Replies
ADVERTISEMENT
May 5, 2008

Lets say I have 30 rows of numbers in two columns. I know I can multiply each number together in each row with an array formula ={A1:A30*B1:B30}

Question:

Given the same ranges, except no numbers but just the text 'x'. some rows have 1 x some have 2 some have none.

Can I use 'Countif(A1:B1,"x")' in an array formula and if so how would that be structured?

View 9 Replies
View Related
Mar 11, 2014

In the first row of my table are the targets for each period of the year and in the second row is the actual performance. The targets for the year will be populated throughout but only periods so far will have a figure for the actual performance.

Thus I have an array formula below:

{=INDIRECT(ADDRESS(2,CELL("col",INDEX(B3:N3,MATCH(2,1/(B3:N3"")))),4))}

Which tells me what the last period's target or performance was (depending on which row number I use).

But when I try to sum it, to find the total for the year to date:

{=SUM(B3:ADDRESS(2,CELL("col",INDEX(B3:N3,MATCH(2,1/(B3:N3"")))),4))}

I get an error. How can I do this?

View 1 Replies
View Related
Feb 22, 2010

In the attached file on the Final Lists tab I have an Array formula in each column.

My problem is that I need to extend this to cover approx 500 columns. Is there any way that I can cut'n'paste or drag the formula across all the 500 columns, have my references increase with each new column, but also retain my $ references (ie my formula format)?

As it is, I am facing having to either type or correct each and every one of the 500 columns. As you can see, I am a little bit of a newbie when it comes to the intricacies of excel.

View 2 Replies
View Related
Feb 12, 2009

I want to use following array formula: {=SUM(SQRT((0.004)^2+(B$2:B10-B$1:B9)^2))}

on columns B till U. In the case of column B, the end of the column is at row 10. However, the lenght of the columns varies, so row 10 won't be the end of the column for column C. So for column C the above formula doesn't work anymore.

The beginning of all columns is the same, hence the '$'. Is there a way to change 'B10' and 'B9' in this formula into 'end of column' and 'end of column minus one'? Cause I would like to use the same formula for all columns.

View 2 Replies
View Related
Feb 14, 2014

In the attached example file, I have two tabs: options and sets. The options tab is to be populated from a form. The sets tab is to draw data from the options tab to create teaching sets. I have managed to create an array formula that does this for me, but what I would like it to do is sort my resulting list alphabetically. For reasons that I won't go into, I need the data on the sets tab to remain in three columns: First Name, Surname and Form.

This is the array formula I am using at the moment: AliGW - Example.xlsx

View 14 Replies
View Related
May 30, 2014

I have two lists in different columns, which are defined ranges. I want to extract them to a unique list with an array formula and at the same time sort albafetically, without duplicates, like this:

List1

List2

Final List

Audi

Ford

[Code] ....

So far, I have this formula which is working in what concerns removing duplicates and exctract a unique list. But it is not ordering alfabeticaly.

{=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($D$1:D1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($D$1:D1, List2), 0))), "")}

View 2 Replies
View Related
May 2, 2014

I want to use the countif function for a certain array. The range is set by another cell which is made up out of a percentile of an entire row.

The problem is is that excel doesn't see the value it displays so i continuously get a value of 0 in the countif cell. If i fill in the range by hand, which is exactly the same range as the outcome of the percentile the countif cell does give the correct value.

So in short the problem is i guess that the countif cell does not recognize the value because this value is made up by a formula.

View 8 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
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
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
May 9, 2014

I'm having a difficult time returning COUNTIF values for a positive match between 2 columns THAT meet a certain condition. Basically I want to iterate through column A sheet 1 (ONLY for values where column B is paid) and return a count for every instance there is a match of value A sheet 1 in column A sheet 2. In other words, I'm looking to find the number of ids marked as paid from Column A sheet 1 that exist in Column A sheet 2. I don't wish to return the actual ids, just the total count.

I've tried the following but I know there's an error in iterating through Column A the way I have it:

=COUNTIFS(Sheet1!$ColA:$ColA,Sheet2!$ColA:$ColA,Sheet1!$ColB:$ColB,"paid")

Sheet1:

ColA

ColB

123

paid

[Code] .....

Sheet2:

ColA

ColB

23

NY

[Code] .....

View 7 Replies
View Related
Jan 27, 2009

I've been asked to extend the counting capabilities of my interface to pick up the profession of each person when they answer whatever is selected in the 'Interface' worksheet (cell B2) put them in my interface.

I've been trying various incarnations of SUMPRODUCT along with what DonkeyOte helped me with before, but currently no joy. Hope that makes sense - have a look at previous post for further details. I've attached an example to look at.

View 2 Replies
View Related
Jan 3, 2009

I have 16 columns with 10 rows with different single digits in them. I want to count the number of times the number 2 appears in columns A, C, E, G, I, K, M, O (in other words every other column in this case).

I know how to write the formula by using countif to find the results but it is rather long. The fomrula would look like this:

View 5 Replies
View Related
Oct 7, 2008

I have two columns and have to count all "DATA PAPA" rows

How i can do it?

View 10 Replies
View Related
Mar 30, 2009

I am using this formula

=COUNTIF($C$1:$C$4000,B1)

How can I add a third column D to the formula to check if the value in 'D'

is the same

eg.

A B C D

1-Jan 1-Jan 4523

2-Jan 4523

3-Jan 4523

4-Jan 4501

View 9 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
Dec 5, 2013

I have coded data for age range in one column and for answering yes or no to a question in a different column. I need a formula that will count the number of yes answers for each coded age range.

a= 14-15 years old, b=16-17, ect.

Column One Column Two

age(coded) Yes or No

a Y

a N

b N

c Y

a Y

b N

I need to be able to count the number of yeses for each coded age. If the formula works it should give me the following output given the sample data above.

14-15 year olds output with the data above would be 2

16-17 year olds output would be 0.

How would I write this formula? I tried countif and sumproduct (though admittedly sumproduct I don't know at all and just tried to find a similar situation and tried it with my spreadsheet).

View 5 Replies
View Related
Jan 8, 2009

I have a spreadsheet where I need to count the # of times the value 1 is in a row, but the columns that I need to check is every other column.

View 3 Replies
View Related
Mar 19, 2009

I am trying to tally answers for a survey. Column A specifies one of 3 locations (Boulder, Larimer, Westminster) and column C specifies a grade for services between locations (Not Uniform, Slightly Uniform, Very Uniform). I was able to tally each separately using 'COUNTIF' and the conditions, but now I would like a total for each of the grades by location, i.e. a count of people answering both Boulder and Uniform, etc.

View 5 Replies
View Related
Jan 6, 2009

I need to have cell with the total rows of Column E that equals TODAY() AND Column F = "Repair". I have tried various COUNTIFs with not much luck.

View 5 Replies
View Related
Aug 10, 2009

I have three seperate Formulas on a summary page that will count dates depending on whether they will be approaching in the next month, whether todays date falls within them and if they are expired or are less than todays date.

Basically the form was developed to monitor a maintanence schedule and give a summary to the person in charge of cleaning so they can forecast there work load. I have managed to get the formula working for "whether todays date falls within them" ("# of Computers than can be cleaned today" as it appears on the summary form) but I cannot seem to get the other formulas figured out.. Well at least the formula that checks to see which computers are past due.

As far as the formula that checks to see which computers will need to be cleaned within the next month, I don't know how to make that work at all considering the setup of my Date input page.

View 10 Replies
View Related
Feb 25, 2010

counting cells if blank. I have two columns E and J . For example if E5 and J5 are both blank I want that to count as 1.

View 13 Replies
View Related
Jun 6, 2009

When using the following:

=COUNTIF(F6:F200,"G")

I get what I want.

What I can't figure out is, I want to get the count of columns G, J, N, and R which has the criterior that I'm looking for, added together for a total.

And in reality, the rows do not go down that far, but they might, so I put 200 in there to be safe.

View 9 Replies
View Related
Feb 15, 2009

I have two columns A and B. Column A contains 'Machine Nr' and column B contain 'Week Nr'. In cell C1 i want to write a function so that it counts the number of weeks in Column B where week number is 4 AND the machine nr is 1 in column nr A.

Column A Column B

Machine Nr Week Nr

1 4

1 4

2 4

2 4

3 4

3 4

View 10 Replies
View Related
Dec 26, 2012

I would like to use the sumproduct or countif formula for a set of data. I have looked around the forum and have not found what I need so

Column A______Column B

West___________1

West___________2

West___________1

West___________2

South__________ 1

South__________2

South__________1

South__________2

I want to count column B for all "West" (column A) and I don't want duplicates. So it would count two unique characters for West and two unique characters for South. I want "west" and "south" separated.

View 3 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
Apr 22, 2014

I have a formula (below) where I want it to count the cells that contain a date that is less than or equal to today, but only when the adjacent cell is blank. The latter part of the formula works, but I'm struggling to tell it to only do the count when cells in column I are blank;

=IF('QS12'!I10:I3120="",COUNTIF('QS12'!H10:H3120,">="&TODAY()))

View 9 Replies
View Related
Jan 15, 2010

I want to be able to look at two columns and set a criteria to count. I want to look at column A and if its blank then look at column B and if it has a value of more than 0 then count.

A B

1 1.00

2 Yes 4.78

3

4 5.00

5 Yes 4.89

6 11.99

So this example would count 3

View 3 Replies
View Related
Dec 19, 2012

I have a spreadsheet that contains a list of dates: What I need is a formula that will count the number of cells that hava a date more than 6 months old. I also would like the field automated so I don't have to change the date manually every day.

=COUNTIF(S5:S593,"<2012/06/18") This formula will give the correct read out, but I must change the date manually.

My attempts at adding a =today() command in place of the date result in 0 being the result (not correct)

This is what I have tried: =COUNTIF(S5:S593,"<"=TODAY()-"183")

View 8 Replies
View Related