Results 1 to 15 of 22

20050129, 23:37 #1
 Join Date
 Feb 2003
 Posts
 95
 Thanks
 0
 Thanked 0 Times in 0 Posts
Referencing 3 columns for a result (2002 SP2)
I have an Excel worksheet with data in multiple columns. In column P, data has one of four values (1, 2, 3, or 4). Column Q has one of two values (L or S); Finally, column W has one of two values (y or n) (for yes or no). I want to Count the # of times there was a "y" in Col W when there was,say, 2 in column P and "L" in column Q. While its easy to use the following fml  IF(AND(P7=2,Q7="L",W7="y"),1,"")  as an example, this only processes data in one row and cannot be used for counting, whereas I want to COUNT across multiple rows in the worksheet where the specified conditions (2 in column P, "L" in col Q, "y" in col W) were true in any given row. Once I figure that out, I wil modify it to Count other combinations also (e.g. 1/L/y, 1/S/y, 4/S/n, etc.)
Please help. I am completely stumped!

20050130, 00:28 #2
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Referencing 3 columns for a result (2002 SP2)
The following array formula should work for up to 100 rows of data. If you have more than 100 rows, then change the three occurances of 100 to whatever is appropriate. Again, this is an array formula, so hold down Ctrl and Shift when you press Enter to confirm the formula.
<pre>=SUM((W1:W100="y")*(P1:P100=2)*(Q1:Q100="L" ))
</pre>
Legare Coleman

20050130, 16:06 #3
 Join Date
 Feb 2003
 Posts
 95
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Referencing 3 columns for a result (2002 SP2)
Thanks for the quick reply! I have never worked with an array fml before so the tip to use CtrlShiftEnter was extremely useful. Out of curiosity, I have a few more Qs:
 if I later insert a new row and insert new data, will the array fml automatically extend its range to include this new row or will I be required to manually update the fml itself?
 the asterisk sign (*) in the fml  is it just a separator or does it imply multiplication as in a standard formula?
 the current array fml uses 3 conditions; what is the maximum # of conditions one can use in an array fml and are they all required to be enclosed in parentheses?

20050130, 20:46 #4
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Referencing 3 columns for a result (2002 SP2)
Q1. Answer: No. However, the following setup would take care of that.
Assuming that P is numeric and the data starts at row 7...
A1:
=MATCH(9.99999999999999E+307,P7:P65536)
The formula would become, using Sumproduct instead of Sum:
=SUMPRODUCT(($W$7:INDEX($W$7:$W$65536,$A$1)=A2),($P$7:INDEX($P$7:$P$65536,$A$1)=B2),($Q$7:INDEX($Q$7:$Q$65536,$A$1)=C2))
where A2 houses a value like "y" (without double quotes, B2 2, and C2 "L".
This formula needs just enter instead of control+shift+enter.
Q2. The Sum formula operates on arrays as the SumProduct formula. The star [ i.e., * ] might be thought of as a multplevalue AND. It effects vector (matrix) multiplication. The evaluation of a conditional yield a truthvalued vector, that is, something like {TRUE,FALSE,FALSE,TRUE,...}. This set is first coerced into a numerical vector, that is, something like {1,0,0,1,...}. In Excel, 1 and 0 are numerical equivalents of TRUE and FALSE, respectively. After coercion, vectors are multilied and summed. The same thing happens when we use SumProduct. Double negation takes care of coercion. The comma in SumProduct stands for multiplication.
Q3. Answer: 30. The conditionals indeed must be put between parens.Microsoft MVP  Excel

20050130, 21:35 #5
 Join Date
 Feb 2003
 Posts
 95
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Referencing 3 columns for a result (2002 SP2)
Thanks, Aladin. However, the SUMPRODUCT thing went right over my head  it seems too complicated for my present needs though I will keep it in mind.

20050130, 22:22 #6
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Referencing 3 columns for a result (2002 SP2)
1 No, the formula will not automatically adjust for added rows. However, because of the way this particular formula works, you can specify a row that is greater than the maximum you will ever have (i.e. you could specify row 1000 even though you only have 100 rows in the current sheet). That would not work in all formulas like this, but it will in this case.
2 Yes, the * in the formula indicates multiplication. When you use Excel boolean expressions, they return a 1 for true and a 0 for false. Therefore, if you multiply three comparison expressions together, you will get 1 if all are true and 0 if any are false. If you add those up, you get a count of the number of times all three expressions were true.
3 The maximum number of conditions is restricted by the maximum size of any formula.Legare Coleman

20050130, 23:18 #7
 Join Date
 Feb 2003
 Posts
 95
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Referencing 3 columns for a result (2002 SP2)
A new wrinkle has appeared. I decided to drop col Q from consideration and instead added col Y which has percentage (%) values. Now I want the formula to add the % values in column Y when the conditions in columns P and W are met. Put another way, if W7="y" and P7=2, then add % value in Y7, and then repeat this for the entire data set up to row 123 (my data currently extends from row 7 to row 123). Thus, I wrote:
{=SUM(($W7:$W123="y")*($P7:$P123=2)*($Y7:$Y123>0)) }
but this gives the same value as {=SUM(($W7:$W123="y")*($P7:$P123=2)}. Then I tried various other methods  such as replacing SUM by SUMIF or putting in an IF condition, like
=IF(($W7:$W123="y")*($P7:$P123=2),SUM($Y7:$Y123))
etc.  but I either got an error message from Excel saying the fml was incorrect or I got #VALUE in the cell or I got a value of 0 or I got the same result as {=SUM(($W7:$W123="y")*($P7:$P123=2)}. Bottom line: inspite of various permutations, I can't get Excel to add the % values in column Y when the 2 specified conditions in columns W and P are met. Please help as this really has me stumped!

20050131, 01:16 #8
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Referencing 3 columns for a result (2002 SP2)
Your first try was close. Try this:
<pre>=SUM(($W7:$W123="y")*($P7:$P123=2)*$Y7:$Y12 3)
</pre>
Legare Coleman

20050131, 13:49 #9
 Join Date
 Feb 2003
 Posts
 95
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Referencing 3 columns for a result (2002 SP2)
Legare, I tried your fml both as is and then within braces {} but both times Excel gave me a #VALUE error. Any workaround? Also, I notice that you did not place the argument $Y7:$Y1230 inside parenthesis  was that an oversight or deliberate?

20050131, 15:24 #10
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Referencing 3 columns for a result (2002 SP2)
$y7:$y123 was not in parenthesis because there was no comparison there to cause possible operation precidence problems. The parenthesis around the others are to insure that the comparison is done before the multily.
The error you are getting indicates that one or more of the values in $y7:$y123 is not a numeric value. Is one or more of your percents entered as text rather than a numeric value?Legare Coleman

20050131, 16:42 #11
 Join Date
 Feb 2003
 Posts
 95
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Referencing 3 columns for a result (2002 SP2)
Legare, they are all numeric values; none is a text entry. I don't know if this is important but they are all derived from a formula within the individual cells.

20050131, 17:41 #12
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Referencing 3 columns for a result (2002 SP2)
Could you upload a workbook that shows the problem? Delete or alter all sensitive data.
Legare Coleman

20050131, 19:02 #13
 Join Date
 Feb 2003
 Posts
 95
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Referencing 3 columns for a result (2002 SP2)
Legare, while I was preparing a copy for upload and clearing out some rows, I noticed that once I cleared the lowest rows (117 to 123) that do not have any data (but do have certain formulas in individual cells), your condition returned a value, as if like magic! In other words, if your condition references cells that have certain formulas but no value as yet (which will come at a future date), it returns a #VALUE error, but if I limit the reference just to the cells with (formulacalculated) values, it does work.
I guess to make your condition work, I must limit the reference to cells with values but then it would become tedious to manually alter the condition each week (data is added each week to the spreadsheet; each row refers to 1 week of data) to refer to new cells with data. Is there a workaround? I tried as follows:
{=IF(ISERROR(your formula),"",your formula)}
but this just returned a blank (""). Any other workaround? What about Aladdin's solution using SUMPRODUCT  it went right over my head but maybe you can explain it in simple English and maybe it will work? Or any other trick that you know of.

20050131, 19:23 #14
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Referencing 3 columns for a result (2002 SP2)
Again, could you upload a workbook that shows the problem (without cleaning up rows 117 to 123). It is most likely that those formulas can be changed to allow my formula to work properly, but I can't tell without seeing what they are doing. Aladdin's formula would probably give the same result as mine since his formula is just a different way of doing the same thing.
Legare Coleman

20050131, 19:42 #15
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Referencing 3 columns for a result (2002 SP2)
After thinking about it for a bit longer, I made a guess at what your formula is doing. Try changing my formula to:
<pre>=SUM(($W7:$W123="y")*($P7:$P123=2)*IF(ISTEXT( $Y7:$Y123),0,$Y7:$Y123))
</pre>
Again, this is an array formula, hold down Ctrl + Shift when entering the formula.Legare Coleman