Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Referencing 3 columns for a result (2002 SP-2)

    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!

  2. #2
    Uranium Lounger
    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 SP-2)

    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

  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing 3 columns for a result (2002 SP-2)

    Thanks for the quick reply! I have never worked with an array fml before so the tip to use Ctrl-Shift-Enter 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?

  4. #4
    3 Star Lounger
    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 SP-2)

    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 multple-value AND. It effects vector (matrix) multiplication. The evaluation of a conditional yield a truth-valued 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

  5. #5
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing 3 columns for a result (2002 SP-2)

    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.

  6. #6
    Uranium Lounger
    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 SP-2)

    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

  7. #7
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing 3 columns for a result (2002 SP-2)

    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!

  8. #8
    Uranium Lounger
    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 SP-2)

    Your first try was close. Try this:

    <pre>=SUM(($W7:$W123="y")*($P7:$P123=2)*$Y7:$Y12 3)
    </pre>

    Legare Coleman

  9. #9
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing 3 columns for a result (2002 SP-2)

    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?

  10. #10
    Uranium Lounger
    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 SP-2)

    $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

  11. #11
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing 3 columns for a result (2002 SP-2)

    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.

  12. #12
    Uranium Lounger
    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 SP-2)

    Could you upload a workbook that shows the problem? Delete or alter all sensitive data.
    Legare Coleman

  13. #13
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing 3 columns for a result (2002 SP-2)

    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 (formula-calculated) 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.

  14. #14
    Uranium Lounger
    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 SP-2)

    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

  15. #15
    Uranium Lounger
    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 SP-2)

    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •