Subtotal formula
echec et math
Posted messages
102
Status
Membre
-
Vaucluse Posted messages 27336 Registration date Status Contributeur Last intervention -
Vaucluse Posted messages 27336 Registration date Status Contributeur Last intervention -
Hello,
When using the "subtotal" formula to determine the number of cells that meet a given criterion while simultaneously using the "auto filter" function, the result is determined based on all rows, including hidden rows. How can we obtain the result without considering the hidden rows?
For example:
a1=1 / b1=0
a2=0 / b2=0
a3=1 / b3=5
a4=2 / b4=8
a5=1 / b5=0
If the selection criterion is: count the number of positive cells in column b
and the auto filter “=1” is activated in column a
the result is: 2
the formula counts cell b4 even though row 4 is not visible on the screen.
How can we count only the cells that are visible on the screen?
Thank you for your help.
ps: excel 2003
When using the "subtotal" formula to determine the number of cells that meet a given criterion while simultaneously using the "auto filter" function, the result is determined based on all rows, including hidden rows. How can we obtain the result without considering the hidden rows?
For example:
a1=1 / b1=0
a2=0 / b2=0
a3=1 / b3=5
a4=2 / b4=8
a5=1 / b5=0
If the selection criterion is: count the number of positive cells in column b
and the auto filter “=1” is activated in column a
the result is: 2
the formula counts cell b4 even though row 4 is not visible on the screen.
How can we count only the cells that are visible on the screen?
Thank you for your help.
ps: excel 2003
Configuration: Windows XP Internet Explorer 6.0
7 réponses
Thank you,
You are both right, but in fact, I miscommunicated. In my example, I am not trying to calculate the sum of the cells in column B (code 9), nor the number of cells in column B (code 3), but rather the number of POSITIVE CELLS ONLY in column B while taking into account the filter "=1" for the cells in column A.
I think I need to use the COUNTIF function, but in this case, all cells will be counted including the hidden cells.
In the example:
ex:
a1=1 / b1=0
a2=0 / b2=0
a3=1 / b3=5
a4=2 / b4=8
a5=1 / b5=0
using the filter "=1" for column A, here is what it looks like:
a1=1 / b1=0
a3=1 / b3=5
a5=1 / b5=0
If I use the formula COUNTIF(B1:B5,">0"), the result is 2 because it counts the hidden cell b4.
How can I avoid counting the hidden cells?
Thank you.
You are both right, but in fact, I miscommunicated. In my example, I am not trying to calculate the sum of the cells in column B (code 9), nor the number of cells in column B (code 3), but rather the number of POSITIVE CELLS ONLY in column B while taking into account the filter "=1" for the cells in column A.
I think I need to use the COUNTIF function, but in this case, all cells will be counted including the hidden cells.
In the example:
ex:
a1=1 / b1=0
a2=0 / b2=0
a3=1 / b3=5
a4=2 / b4=8
a5=1 / b5=0
using the filter "=1" for column A, here is what it looks like:
a1=1 / b1=0
a3=1 / b3=5
a5=1 / b5=0
If I use the formula COUNTIF(B1:B5,">0"), the result is 2 because it counts the hidden cell b4.
How can I avoid counting the hidden cells?
Thank you.
If you had Excel 2007, you could have used the function COUNTIFS().
In your example, I suggest using column C:C (or any other):
- In C6, enter the filter criterion each time (here, it's 1).
- In C1, enter the formula =IF(AND(A:A=$C$6,B:B>0),1,0) and copy it down to C5.
- In C6, sum up =SUM(C1:C5).
It's not very elegant, but it works!
--
Retirement is nice! Especially in the Antilles... :-)
Raymond
In your example, I suggest using column C:C (or any other):
- In C6, enter the filter criterion each time (here, it's 1).
- In C1, enter the formula =IF(AND(A:A=$C$6,B:B>0),1,0) and copy it down to C5.
- In C6, sum up =SUM(C1:C5).
It's not very elegant, but it works!
--
Retirement is nice! Especially in the Antilles... :-)
Raymond
Hello everyone
And why not simply count the number of 1s first and subtract the number of B<0
=COUNTIF(A2:A6,1)-COUNTIF(B2:B6,"<0")-COUNTIF(B2:B6,0)
There is also the solution:
=SUMPRODUCT((A2:A6=1)*(B2:B6>0)).
In my opinion, this is the most straightforward
Safe travels to you both
Good sunshine to Raymond
CRDLMNT
--
The truth is not possessed, it is only sought
And why not simply count the number of 1s first and subtract the number of B<0
=COUNTIF(A2:A6,1)-COUNTIF(B2:B6,"<0")-COUNTIF(B2:B6,0)
There is also the solution:
=SUMPRODUCT((A2:A6=1)*(B2:B6>0)).
In my opinion, this is the most straightforward
Safe travels to you both
Good sunshine to Raymond
CRDLMNT
--
The truth is not possessed, it is only sought
Thank you for your responses,
In fact, all these solutions are valid in my example, but in practice, my base table is much more complex.
I sometimes use several filters on different columns with different criteria (which sometimes change).
In the end, I am left with one (or several) columns to analyze, and it's in this column that I want to determine the number of positive values visible on the screen.
One of the solutions could be to add a column, show "1" in each cell corresponding to the positive values of my reference column (the one I want to analyze) and use the SUBTOTAL function (code 9) to sum the "1s".
But on the one hand, this solution is not very elegant, to use Raymond's expression, and furthermore, I do not always analyze the same column, which forces me to do the same manipulation for all the columns I want to analyze, not elegant at all!!!
There may not be a solution with Excel 2003, but if you have other ideas, thank you.
In fact, all these solutions are valid in my example, but in practice, my base table is much more complex.
I sometimes use several filters on different columns with different criteria (which sometimes change).
In the end, I am left with one (or several) columns to analyze, and it's in this column that I want to determine the number of positive values visible on the screen.
One of the solutions could be to add a column, show "1" in each cell corresponding to the positive values of my reference column (the one I want to analyze) and use the SUBTOTAL function (code 9) to sum the "1s".
But on the one hand, this solution is not very elegant, to use Raymond's expression, and furthermore, I do not always analyze the same column, which forces me to do the same manipulation for all the columns I want to analyze, not elegant at all!!!
There may not be a solution with Excel 2003, but if you have other ideas, thank you.
Hello
Before saying that there is no solution:
We can always have tons of ideas, but if you don't provide us with the right data, it seems like a waste of time...!
If you want to do better, upload your file on cjoint.com, we will be happy to see what's going on. In the meantime, let's enjoy the sun a little...
CRDLMNT
--
The truth is not possessed, it is only sought.
Before saying that there is no solution:
We can always have tons of ideas, but if you don't provide us with the right data, it seems like a waste of time...!
If you want to do better, upload your file on cjoint.com, we will be happy to see what's going on. In the meantime, let's enjoy the sun a little...
CRDLMNT
--
The truth is not possessed, it is only sought.
Okay, I'm trying to send the file, but not in its entirety, it’s 20MB!!!
https://www.cjoint.com/?fisSTLXsIF
I activated the automatic filter with different criteria for columns F-O-S-V.
But I’m doing stats and these criteria are not fixed, nor is the selection of the columns.
I would like to know the number of visible positive values on the screen for columns I-J-K-L-M.
And if possible, at the location of the question marks for each column.
Thank you.
https://www.cjoint.com/?fisSTLXsIF
I activated the automatic filter with different criteria for columns F-O-S-V.
But I’m doing stats and these criteria are not fixed, nor is the selection of the columns.
I would like to know the number of visible positive values on the screen for columns I-J-K-L-M.
And if possible, at the location of the question marks for each column.
Thank you.
Hello
I took a look, I propose:
Column AC from A5 to .....:(or elsewhere _further_if you wish.
=IF(I5>0,1,0)
Column AD
=IF(J5>0,1,0)
And so on...
To be pulled down the height of the field
You can hide the columns
Cell I2:
=SUBTOTAL(9,I:I)
To be pulled across columns J, K, L, M
I tested it, it works for me, but of course it requires using 5 reference columns
CRDLMNT
--
The truth is not possessed, it is only sought.
I took a look, I propose:
Column AC from A5 to .....:(or elsewhere _further_if you wish.
=IF(I5>0,1,0)
Column AD
=IF(J5>0,1,0)
And so on...
To be pulled down the height of the field
You can hide the columns
Cell I2:
=SUBTOTAL(9,I:I)
To be pulled across columns J, K, L, M
I tested it, it works for me, but of course it requires using 5 reference columns
CRDLMNT
--
The truth is not possessed, it is only sought.
RE
For the sake of form, below is your file with my proposal, organized up to line 10000.
CRDLMNT
http://www.cijoint.fr/cjlink.php?file=cj200805/cijx03ASdo.xls
--
The truth is not possessed, it is only sought.
For the sake of form, below is your file with my proposal, organized up to line 10000.
CRDLMNT
http://www.cijoint.fr/cjlink.php?file=cj200805/cijx03ASdo.xls
--
The truth is not possessed, it is only sought.
But thank you for your response: I had completely lost sight of this function and its options ...
--
Retirement is great! Especially in the Caribbean ... :-)
Raymond
Same result with 9 and 109, despite what the help says about the function. Besides, I've mentioned this tip several times.
Good sun
PS: the code 9 or 109 concerns hidden lines, but apparently not filtering, according to my tests
--
Truth is not possessed; it is only sought.