• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Countif in Array formula

Y

YasirH

New Member
Hi,


I just started using array formulas and I'm having a problem with a slightly complicated one.


The simplified version of the formula is:

[pre]
Code: 
{=SUM(IF(S1!$B2:$B1000="20");S1'!$I2:$I1000*COUNTIF(S1!$J2:$AG1000;"<10"))}
What the formula is basically supposed to do is to look at a column, find the the "20"s and for each of the rows, take the value from column I of that row and multiply it by the number of times a certain criteria is met in columns J to AG of that row.


I tried building the array up from a single row formula (works btw) and the simplified version is:

[pre][code]=IF(S1!$B2="20");S1!$I2*COUNTIF(S1!$J2:$AG2;"<10"))
I think it's the countif range that's the issue. 


If you're interested in reading the actual formula and helping me out there:


Array formula

{=SUM(IF('Disbursing Projects'!$B2:$B1000=VLOOKUP($A12;FinanceTypes;2;FALSE);
'Disbursing Projects'!$I2:$I1000*COUNTIFS('Disbursing Projects'!$J2:$AG1000;"
<="&RIGHT(B$10;LEN(B$10)-FIND("-";B$10));'Disbursing Projects'!$J2:$AG1000;">"&LEFT(B$10;FIND("-";B$10)-1))))}
[/pre]
Single row formula

=IF('Disbursing Projects'!$B2=VLOOKUP($A12;FinanceTypes;2;FALSE);'Disbursing Projects'!$I2
*COUNTIFS('Disbursing Projects'!$J2:$AG2;"<="&RIGHT(B$10;LEN(B$10)-FIND("-";B$10));
'Disbursing Projects'!$J2:$AG2;">"&LEFT(B$10;FIND("-";B$10)-1)))[/code][/pre]
Thanks!!!
 
Columns I to AG look like this:

[pre]
Code: 
Balance          P1	P2	P3	P4	P5	P6
551,112 	 1,972 	 2,155 	 2,338 	 2,521 	 2,704 	 2,887
949,129 	 668 	 851 	 1,034 	 1,217 	 1,400 	 1,583
2,701,536 	 183 	 366 	 549 	 732 	 915 	 1,098
89,394 	 1,946 	 2,129 	 2,312 	 2,495 	 2,678 	 2,861
56,013 	 2,201 	 2,384 	 2,567 	 2,750 	 2,933 	 3,116
452,952 	 1,307 	 1,490 	 1,673 	 1,856 	 2,039 	 2,222
274,766 	 2,074 	 2,257 	 2,440 	 2,623 	 2,806 	 2,989
72,833 	 2,406 	 2,589 	 2,772 	 2,955 	 3,138 	 3,321
469,800 	 515 	 698 	 881 	 1,064 	 1,247 	 1,430
123,316 	 2,048 	 2,231 	 2,414 	 2,597 	 2,780 	 2,963
2,560 	 	 2,712 	 2,895 	 3,078 	 3,261 	 3,444 	 3,627
[/pre]

P1 and onwards are calculated values based on an aging formula which calculates principal repayment dates
 
Hi Yasir ,


I am not clear on what you want to do.


You have data in column B ; is this text or numeric , since in your formula , you are checking for "20" and not 20 ?


Secondly , when you say it's an array formula , are you looking for an overall sum of all those values which satisfy the criterion , over multiple rows ? For example , if rows 3 , 6 and 10 meet the criterion , do you want I3 to be multiplied by the number of cells in the range J3 through AG3 which meet the criterion of less than 10 , I6 to be multiplied by the number of cells in the range J6 through AG6 which meet the criterion of less than 10 , I10 to be multiplied by the number of cells in the range J10 through AG10 which meet the criterion of less than 10 , and these 3 values to be summed ?


Narayan
 
Hi Yasir ,


If you want just one result , then the following formula will do the job :


=SUM(((Data_Range<10)*(Data_Range>0.0001))*((I2:I1000)*(B2:B1000=20)))


Data_Range is a named range referring to J2:AG1000.


The above formula is to be entered as an array formula , using CTRL SHIFT ENTER.


The inclusion of Data_Range>0.0001 is to exclude blank cells.


Narayan
 
Hi Narayan,


Thank you very much! It works but I have no idea how it does. Could you link me to anything that will take me through it.


Thanks again

Yasir
 
Hi Yasir ,


An array formula works on arrays i.e. on more than one cell at a time.


What is it that you want to do ? You want to look at those cells in the list B2:B1000 which have the value 20 in them ; when you use the construct (B2:B1000=20) , what you get is an array of 0s and 1s ; 0 ( which is the same as FALSE ) where the cell does not equal 20 , and 1 ( which is the same as TRUE ) where the cell equals 20.


Once you know a cell in the list B2:B1000 equals 20 , what do you want to do ? You would like to look at the range J2:AG1000 , and see how many of these cells are less than 10. That is done by this construct : ((Data_Range<10)*(Data_Range>0.0001)) As I have mentioned earlier , the second part of this (Data_Range>0.0001) is just to exclude blanks , since blank cells are taken as 0 , and counted as less than 10. Since we are entering the whole formula as an array formula , the above will give us a matrix with 0s where the cell value is greater than or equal to 10 , and 1s where the cell value is less than 10 ( and of course not blank ).


Having got these two arrays , we now do the multiplication with the values in the list I2:I1000 ; this will result in a matrix , with 0s and the value in the corresponding row in the range I2:I1000 e.g. if say I7 is having a value 13 , and B7 has 20 , then the resulting row might be something like this {0,0,13,13,0,0,0,13,0,0,13,0,0,13,0,0,0,0,0,0,13,13,0,0} ; there will be 24 values , since the number of columns in the range J:AG is 24. The number of 13s in this result ( 7 ) is the result you would have got using COUNTIF(J7:AG7,"<10"). Essentially , by SUMMING the row , we have got the result of I7*COUNTIF(J7:AG7,"<10").


The final matrix will have 999 rows ( 2:1000 ) with the same structure. SUMMING the whole matrix gives us the final result.


Narayan
 
You must log in or register to reply here.
Back
Top