#1   Report Post  
Old December 9th 04, 03:11 PM
Sheryl
 
Posts: n/a
Default 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.

  #2   Report Post  
Old December 9th 04, 04:41 PM
Frank Kabel
 
Posts: n/a
Default

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.


  #3   Report Post  
Old December 9th 04, 04:45 PM
Peo Sjoblom
 
Posts: n/a
Default

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.

  #4   Report Post  
Old December 9th 04, 07:16 PM
Sheryl
 
Posts: n/a
Default

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.



  #5   Report Post  
Old December 9th 04, 07:18 PM
Sheryl
 
Posts: n/a
Default


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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 01:01 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017