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

Topic

New Reply

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!

Reply | Quote

Replies

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.

=SUM((W1:W100="y")*(P1:P100=2)*(Q1:Q100="L"))

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote

Your first try was close. Try this:

=SUM(($W7:$W123="y")*($P7:$P123=2)*$Y7:$Y123)

Reply | Quote

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?

Reply | Quote

$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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

After thinking about it for a bit longer, I made a guess at what your formula is doing. Try changing my formula to:

=SUM(($W7:$W123="y")*($P7:$P123=2)*IF(ISTEXT($Y7:$Y123),0,$Y7:$Y123))

Again, this is an array formula, hold down Ctrl + Shift when entering the formula.

Reply | Quote

Thanks Legare, I changed your fml and now it works! Amazing how small tweaks can make you stop tearing your hair!
Out of curiosity, is the order of precedence in an array fml important? I noticed that you put W7:W123=”y” ahead of P7:P123=2 in the fml.

Reply | Quote

=SUMPRODUCT(–($W7:$W123=”y”),–($P7:$P123=2),$Y7:$Y123)

would ignore any text values in Y7:Y123 and is slightly faster.

Reply | Quote

The order of precedence is important in all statements with more than one operator. In this case, the order of the multiplications does not make any difference. 1*1*0 will get the same result as 1*0*1 or 0*1*1. I put them in that order because that is the order you specified them in in your post, and since the order was not important, I just did them in the order you typed them.

Reply | Quote

Thanks, Aladin and, especially, Legare for all your help. Keep up the good work!

Reply | Quote

Hoping I’m not too late here, but I have a similar problem (wanting to count and sum rows in a table that match criteria based on the contents of a number of columns). I know I’m a bit slow on array formulae, so I looked for an alternative. I found DCOUNT. This function references a table (for the counting), and also a Criteria Table. This Criteria Table can check on multiple criteria (I think it’s the same as the Criteria Tables in Advanced FILTER (Data / Filter / Advanced Filter )). So, for example, using Conditional Criteria I could check on the number of characters in cells in a column, whether a column cell contained a certain character, etc, etc. Moreover I can do OR’s and ANDs. Thus I can select the rows to count. Using DSUM I can total a column in a similar way. I’m hoping these techniques will solve my problems. Perhaps it could also provide relevant alternatives to using SUMPRODUCT and Array Formulae.

regards

Geoffrey Howell

Reply | Quote

No, Geoffrey, you are not too late. I am frankly surprised you were able to dig out this post as its a few weeks old! I have no personal experience of using the DCOUNT or other functions you mentioned. While my (relatively simple) needs were met with the previous replies, I will keep your suggestion in mind and it just might come in useful sometime in the future. Thanks for your post.

Reply | Quote

> I am frankly surprised you were able to dig out this post as its a few weeks old

If you put key word(s) into our Search function (at the top of every page in the Lounge), you will be able to find very specific results among >456,000 posts – stretching back to when this version of the Lounge went live in December 2000.

“Search” is our most under-utilised resource.

HTH

Reply | Quote