Subtotal And Countif
First of all thanks to the three gentlemen who have pointed me in the right
direction using the Subtotal function in a previous post. I am half way there! Using subtotal works for what I need to accomplish. What I am trying to do is filter a list of entries and make calculations. I am currently using countif if a cell contains a "Y" as a value for the calculation. I need to filter a list with multiple same name entires and am having trouble filtering the list because I cannot use the subtotal and countif together. Ideally I would like to filter a column for a name that has mulitply entries and have Excel calulate all of the cells that are tied to that name for which contain the letter "Y". I thought I was on the right track with the CountA(A:1,A5,"Y") function and then using Subtotal(3,A:1,A:5,"Y") but that will not work. CountA(A:1,A5,"Y") works fine by itself but not with the subtotal function. I cannot use the CountA(A:1,A5,"Y") function for my calculations due to these same filtered fields having to contain other characters such as "N" and "NA". CountA will count every cell that is not blank. Thanks in advance for any help that anyone may be able to provide. |
Hi
try: =SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX($A$1:$A$10,1,1),ROW($A$1: $A$10)- ROW(INDEX($A$1:$A$10,1,1)),0))=1),--($A$1:$A$10="Y")) -- Regards Frank Kabel Frankfurt, Germany "Sheryl" schrieb im Newsbeitrag ... First of all thanks to the three gentlemen who have pointed me in the right direction using the Subtotal function in a previous post. I am half way there! Using subtotal works for what I need to accomplish. What I am trying to do is filter a list of entries and make calculations. I am currently using countif if a cell contains a "Y" as a value for the calculation. I need to filter a list with multiple same name entires and am having trouble filtering the list because I cannot use the subtotal and countif together. Ideally I would like to filter a column for a name that has mulitply entries and have Excel calulate all of the cells that are tied to that name for which contain the letter "Y". I thought I was on the right track with the CountA(A:1,A5,"Y") function and then using Subtotal(3,A:1,A:5,"Y") but that will not work. CountA(A:1,A5,"Y") works fine by itself but not with the subtotal function. I cannot use the CountA(A:1,A5,"Y") function for my calculations due to these same filtered fields having to contain other characters such as "N" and "NA". CountA will count every cell that is not blank. Thanks in advance for any help that anyone may be able to provide. |
Why can't you first filter on the names and then filter on the Y, then just
count the list? You might also be able to add a column with formulas and filter on first the other 2 then on the formulas. Finally there is a complex solution that's using subtotal and sumproduct to do countif on a filtered list but given the complexity maybe you could post back with more information first to see if we could use other alternatives Here's an example of a formulka that will count Y in column B of a filtered list =SUMPRODUCT(--($B$2:$B$20="Y"),(SUBTOTAL(3,OFFSET($B$2,ROW($B$2: $B$20)-MIN(ROW($B$2:$B$20)),,)))) Regards, Peo Sjoblom "Sheryl" wrote: First of all thanks to the three gentlemen who have pointed me in the right direction using the Subtotal function in a previous post. I am half way there! Using subtotal works for what I need to accomplish. What I am trying to do is filter a list of entries and make calculations. I am currently using countif if a cell contains a "Y" as a value for the calculation. I need to filter a list with multiple same name entires and am having trouble filtering the list because I cannot use the subtotal and countif together. Ideally I would like to filter a column for a name that has mulitply entries and have Excel calulate all of the cells that are tied to that name for which contain the letter "Y". I thought I was on the right track with the CountA(A:1,A5,"Y") function and then using Subtotal(3,A:1,A:5,"Y") but that will not work. CountA(A:1,A5,"Y") works fine by itself but not with the subtotal function. I cannot use the CountA(A:1,A5,"Y") function for my calculations due to these same filtered fields having to contain other characters such as "N" and "NA". CountA will count every cell that is not blank. Thanks in advance for any help that anyone may be able to provide. |
Thanks Frank! That worked perfectly! - Sheryl
"Frank Kabel" wrote: Hi try: =SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX($A$1:$A$10,1,1),ROW($A$1: $A$10)- ROW(INDEX($A$1:$A$10,1,1)),0))=1),--($A$1:$A$10="Y")) -- Regards Frank Kabel Frankfurt, Germany "Sheryl" schrieb im Newsbeitrag ... First of all thanks to the three gentlemen who have pointed me in the right direction using the Subtotal function in a previous post. I am half way there! Using subtotal works for what I need to accomplish. What I am trying to do is filter a list of entries and make calculations. I am currently using countif if a cell contains a "Y" as a value for the calculation. I need to filter a list with multiple same name entires and am having trouble filtering the list because I cannot use the subtotal and countif together. Ideally I would like to filter a column for a name that has mulitply entries and have Excel calulate all of the cells that are tied to that name for which contain the letter "Y". I thought I was on the right track with the CountA(A:1,A5,"Y") function and then using Subtotal(3,A:1,A:5,"Y") but that will not work. CountA(A:1,A5,"Y") works fine by itself but not with the subtotal function. I cannot use the CountA(A:1,A5,"Y") function for my calculations due to these same filtered fields having to contain other characters such as "N" and "NA". CountA will count every cell that is not blank. Thanks in advance for any help that anyone may be able to provide. |
Thanks Peo, will try this as well! - Sheryl "Peo Sjoblom" wrote: Why can't you first filter on the names and then filter on the Y, then just count the list? You might also be able to add a column with formulas and filter on first the other 2 then on the formulas. Finally there is a complex solution that's using subtotal and sumproduct to do countif on a filtered list but given the complexity maybe you could post back with more information first to see if we could use other alternatives Here's an example of a formulka that will count Y in column B of a filtered list =SUMPRODUCT(--($B$2:$B$20="Y"),(SUBTOTAL(3,OFFSET($B$2,ROW($B$2: $B$20)-MIN(ROW($B$2:$B$20)),,)))) Regards, Peo Sjoblom "Sheryl" wrote: First of all thanks to the three gentlemen who have pointed me in the right direction using the Subtotal function in a previous post. I am half way there! Using subtotal works for what I need to accomplish. What I am trying to do is filter a list of entries and make calculations. I am currently using countif if a cell contains a "Y" as a value for the calculation. I need to filter a list with multiple same name entires and am having trouble filtering the list because I cannot use the subtotal and countif together. Ideally I would like to filter a column for a name that has mulitply entries and have Excel calulate all of the cells that are tied to that name for which contain the letter "Y". I thought I was on the right track with the CountA(A:1,A5,"Y") function and then using Subtotal(3,A:1,A:5,"Y") but that will not work. CountA(A:1,A5,"Y") works fine by itself but not with the subtotal function. I cannot use the CountA(A:1,A5,"Y") function for my calculations due to these same filtered fields having to contain other characters such as "N" and "NA". CountA will count every cell that is not blank. Thanks in advance for any help that anyone may be able to provide. |
All times are GMT +1. The time now is 02:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com