combination of AutoFilter and array formula?
Hi. Someone just called me about a formula that one of the managers thinks
he needs. I can do what they want in three rows, but am not seeing how to do it in one row, and have it change with the AutoFilter. They have something like the following, across rows and columns: Schedule Value Row1 1 X Row2 2 C Row3 3 NULL Row4 1 NULL Row5 2 X Row6 3 NULL They want to count the instances of X for each schedule, where AutoFilter is turned on, and they pick schedule 1, 2 , or 3, from the drop down. I can give them an array formula based upon another cell, say A12, that will do it: =SUM(--(B2:B9="X")*--(A2:A9=A12)) But in that example, you have to type the 1, 2, or 3 in cell A12... that is not automatically picked up from the filtered selection. I tried combining the array formula above with a subtotal(9,), but I didn't get that to enter with the array. Perhaps I just had a syntax problem. Suggestions? Thanks. Mark |
combination of AutoFilter and array formula?
you could try:
=SUMPRODUCT(--(B2:B10="x"),SUBTOTAL(3,OFFSET(B2,ROW(B2:B10)-ROW(B2),0))) normally entered "mark" wrote: Hi. Someone just called me about a formula that one of the managers thinks he needs. I can do what they want in three rows, but am not seeing how to do it in one row, and have it change with the AutoFilter. They have something like the following, across rows and columns: Schedule Value Row1 1 X Row2 2 C Row3 3 NULL Row4 1 NULL Row5 2 X Row6 3 NULL They want to count the instances of X for each schedule, where AutoFilter is turned on, and they pick schedule 1, 2 , or 3, from the drop down. I can give them an array formula based upon another cell, say A12, that will do it: =SUM(--(B2:B9="X")*--(A2:A9=A12)) But in that example, you have to type the 1, 2, or 3 in cell A12... that is not automatically picked up from the filtered selection. I tried combining the array formula above with a subtotal(9,), but I didn't get that to enter with the array. Perhaps I just had a syntax problem. Suggestions? Thanks. Mark |
combination of AutoFilter and array formula?
|
combination of AutoFilter and array formula?
Thank you both. I will try your suggestion on Monday when I get back to it,
if not later today. I recognize your names, and am sure from that that your advice is good. Thank you. Mark "Don Guillett" wrote: Less cluttered than my offering. -- Don Guillett SalesAid Software "JMB" wrote in message ... you could try: =SUMPRODUCT(--(B2:B10="x"),SUBTOTAL(3,OFFSET(B2,ROW(B2:B10)-ROW(B2),0))) normally entered "mark" wrote: Hi. Someone just called me about a formula that one of the managers thinks he needs. I can do what they want in three rows, but am not seeing how to do it in one row, and have it change with the AutoFilter. They have something like the following, across rows and columns: Schedule Value Row1 1 X Row2 2 C Row3 3 NULL Row4 1 NULL Row5 2 X Row6 3 NULL They want to count the instances of X for each schedule, where AutoFilter is turned on, and they pick schedule 1, 2 , or 3, from the drop down. I can give them an array formula based upon another cell, say A12, that will do it: =SUM(--(B2:B9="X")*--(A2:A9=A12)) But in that example, you have to type the 1, 2, or 3 in cell A12... that is not automatically picked up from the filtered selection. I tried combining the array formula above with a subtotal(9,), but I didn't get that to enter with the array. Perhaps I just had a syntax problem. Suggestions? Thanks. Mark |
combination of AutoFilter and array formula?
Both should give you the same desired result
-- Don Guillett SalesAid Software "mark" wrote in message ... Thank you both. I will try your suggestion on Monday when I get back to it, if not later today. I recognize your names, and am sure from that that your advice is good. Thank you. Mark "Don Guillett" wrote: Less cluttered than my offering. -- Don Guillett SalesAid Software "JMB" wrote in message ... you could try: =SUMPRODUCT(--(B2:B10="x"),SUBTOTAL(3,OFFSET(B2,ROW(B2:B10)-ROW(B2),0))) normally entered "mark" wrote: Hi. Someone just called me about a formula that one of the managers thinks he needs. I can do what they want in three rows, but am not seeing how to do it in one row, and have it change with the AutoFilter. They have something like the following, across rows and columns: Schedule Value Row1 1 X Row2 2 C Row3 3 NULL Row4 1 NULL Row5 2 X Row6 3 NULL They want to count the instances of X for each schedule, where AutoFilter is turned on, and they pick schedule 1, 2 , or 3, from the drop down. I can give them an array formula based upon another cell, say A12, that will do it: =SUM(--(B2:B9="X")*--(A2:A9=A12)) But in that example, you have to type the 1, 2, or 3 in cell A12... that is not automatically picked up from the filtered selection. I tried combining the array formula above with a subtotal(9,), but I didn't get that to enter with the array. Perhaps I just had a syntax problem. Suggestions? Thanks. Mark |
All times are GMT +1. The time now is 10:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com