|
Count if function
Hi
I have the following formula in one of the cell =COUNT(IF((TRIM($C$4:$C$96)="P")*ISBLANK($M$4:$M$9 6),$A$4:$A$96)) But I have autofilter command and I want the rows to be counted based on the output of the filter results. But this formula always gives the output os the rows. Please help |
Count if function
If you are going to use a filter then you don't need a formula like this,
filter on blanks in M and P in C then just count the visible cells in A =SUBTOTAL(2,A4:A96) If you still want a formula and all your data starts in row 4 with headers in 3 you can use =SUMPRODUCT(--(ISBLANK($M$4:$M$96)),--(TRIM($C$4:$C$96)="P"),--(ISNUMBER($A$4:$A$96)),SUBTOTAL(3,OFFSET($A$4,ROW( $A$4:$A$96)-MIN(ROW($A$4:$A$96)),,))) although I would probably change remove isblank and use range="" =SUMPRODUCT(--($M$4:$M$96=""),--(TRIM($C$4:$C$96)="P"),--(ISNUMBER($A$4:$A$96)),SUBTOTAL(3,OFFSET($A$4,ROW( $A$4:$A$96)-MIN(ROW($A$4:$A$96)),,))) This formula can be entered normally as opposed to your COUNT formula -- Regards, Peo Sjoblom "ub" wrote in message ... Hi I have the following formula in one of the cell =COUNT(IF((TRIM($C$4:$C$96)="P")*ISBLANK($M$4:$M$9 6),$A$4:$A$96)) But I have autofilter command and I want the rows to be counted based on the output of the filter results. But this formula always gives the output os the rows. Please help |
Count if function
Hi, when I use Subtotal(2,a4:a96), It get error messgae # value.
Please advise "Peo Sjoblom" wrote: If you are going to use a filter then you don't need a formula like this, filter on blanks in M and P in C then just count the visible cells in A =SUBTOTAL(2,A4:A96) If you still want a formula and all your data starts in row 4 with headers in 3 you can use =SUMPRODUCT(--(ISBLANK($M$4:$M$96)),--(TRIM($C$4:$C$96)="P"),--(ISNUMBER($A$4:$A$96)),SUBTOTAL(3,OFFSET($A$4,ROW( $A$4:$A$96)-MIN(ROW($A$4:$A$96)),,))) although I would probably change remove isblank and use range="" =SUMPRODUCT(--($M$4:$M$96=""),--(TRIM($C$4:$C$96)="P"),--(ISNUMBER($A$4:$A$96)),SUBTOTAL(3,OFFSET($A$4,ROW( $A$4:$A$96)-MIN(ROW($A$4:$A$96)),,))) This formula can be entered normally as opposed to your COUNT formula -- Regards, Peo Sjoblom "ub" wrote in message ... Hi I have the following formula in one of the cell =COUNT(IF((TRIM($C$4:$C$96)="P")*ISBLANK($M$4:$M$9 6),$A$4:$A$96)) But I have autofilter command and I want the rows to be counted based on the output of the filter results. But this formula always gives the output os the rows. Please help |
Count if function
I don't see how that formula can return a value error unless you calculate
it with something else like =SUBTOTAL(2,A4:A96)*x and what you calculate it with is a text value also make sure you don't have any errors in your ranges although count does not get affected by errors like a sum would -- Regards, Peo Sjoblom "ub" wrote in message ... Hi, when I use Subtotal(2,a4:a96), It get error messgae # value. Please advise "Peo Sjoblom" wrote: If you are going to use a filter then you don't need a formula like this, filter on blanks in M and P in C then just count the visible cells in A =SUBTOTAL(2,A4:A96) If you still want a formula and all your data starts in row 4 with headers in 3 you can use =SUMPRODUCT(--(ISBLANK($M$4:$M$96)),--(TRIM($C$4:$C$96)="P"),--(ISNUMBER($A$4:$A$96)),SUBTOTAL(3,OFFSET($A$4,ROW( $A$4:$A$96)-MIN(ROW($A$4:$A$96)),,))) although I would probably change remove isblank and use range="" =SUMPRODUCT(--($M$4:$M$96=""),--(TRIM($C$4:$C$96)="P"),--(ISNUMBER($A$4:$A$96)),SUBTOTAL(3,OFFSET($A$4,ROW( $A$4:$A$96)-MIN(ROW($A$4:$A$96)),,))) This formula can be entered normally as opposed to your COUNT formula -- Regards, Peo Sjoblom "ub" wrote in message ... Hi I have the following formula in one of the cell =COUNT(IF((TRIM($C$4:$C$96)="P")*ISBLANK($M$4:$M$9 6),$A$4:$A$96)) But I have autofilter command and I want the rows to be counted based on the output of the filter results. But this formula always gives the output os the rows. Please help |
Count if function
Hi
I am using the formula : =SUMPRODUCT(--(ISBLANK($M$4:$M$96)),--(TRIM($C$4:$C$96)="P"),SUBTOTAL(2,A4:A96)) Please advise , what misytake I am doing. Thanks "Peo Sjoblom" wrote: I don't see how that formula can return a value error unless you calculate it with something else like =SUBTOTAL(2,A4:A96)*x and what you calculate it with is a text value also make sure you don't have any errors in your ranges although count does not get affected by errors like a sum would -- Regards, Peo Sjoblom "ub" wrote in message ... Hi, when I use Subtotal(2,a4:a96), It get error messgae # value. Please advise "Peo Sjoblom" wrote: If you are going to use a filter then you don't need a formula like this, filter on blanks in M and P in C then just count the visible cells in A =SUBTOTAL(2,A4:A96) If you still want a formula and all your data starts in row 4 with headers in 3 you can use =SUMPRODUCT(--(ISBLANK($M$4:$M$96)),--(TRIM($C$4:$C$96)="P"),--(ISNUMBER($A$4:$A$96)),SUBTOTAL(3,OFFSET($A$4,ROW( $A$4:$A$96)-MIN(ROW($A$4:$A$96)),,))) although I would probably change remove isblank and use range="" =SUMPRODUCT(--($M$4:$M$96=""),--(TRIM($C$4:$C$96)="P"),--(ISNUMBER($A$4:$A$96)),SUBTOTAL(3,OFFSET($A$4,ROW( $A$4:$A$96)-MIN(ROW($A$4:$A$96)),,))) This formula can be entered normally as opposed to your COUNT formula -- Regards, Peo Sjoblom "ub" wrote in message ... Hi I have the following formula in one of the cell =COUNT(IF((TRIM($C$4:$C$96)="P")*ISBLANK($M$4:$M$9 6),$A$4:$A$96)) But I have autofilter command and I want the rows to be counted based on the output of the filter results. But this formula always gives the output os the rows. Please help |
Count if function
You should use the formula I posted
=SUMPRODUCT(--(ISBLANK($M$4:$M$96)),--(TRIM($C$4:$C$96)="P"),--(ISNUMBER($A$4:$A$96)),SUBTOTAL(3,OFFSET($A$4,ROW( $A$4:$A$96)-MIN(ROW($A$4:$A$96)),,))) or use custom filtering and then simply =SUBTOTAL(2,$A$4:$A$96) nothing else -- Regards, Peo Sjoblom "ub" wrote in message ... Hi I am using the formula : =SUMPRODUCT(--(ISBLANK($M$4:$M$96)),--(TRIM($C$4:$C$96)="P"),SUBTOTAL(2,A4:A96)) Please advise , what misytake I am doing. Thanks "Peo Sjoblom" wrote: I don't see how that formula can return a value error unless you calculate it with something else like =SUBTOTAL(2,A4:A96)*x and what you calculate it with is a text value also make sure you don't have any errors in your ranges although count does not get affected by errors like a sum would -- Regards, Peo Sjoblom "ub" wrote in message ... Hi, when I use Subtotal(2,a4:a96), It get error messgae # value. Please advise "Peo Sjoblom" wrote: If you are going to use a filter then you don't need a formula like this, filter on blanks in M and P in C then just count the visible cells in A =SUBTOTAL(2,A4:A96) If you still want a formula and all your data starts in row 4 with headers in 3 you can use =SUMPRODUCT(--(ISBLANK($M$4:$M$96)),--(TRIM($C$4:$C$96)="P"),--(ISNUMBER($A$4:$A$96)),SUBTOTAL(3,OFFSET($A$4,ROW( $A$4:$A$96)-MIN(ROW($A$4:$A$96)),,))) although I would probably change remove isblank and use range="" =SUMPRODUCT(--($M$4:$M$96=""),--(TRIM($C$4:$C$96)="P"),--(ISNUMBER($A$4:$A$96)),SUBTOTAL(3,OFFSET($A$4,ROW( $A$4:$A$96)-MIN(ROW($A$4:$A$96)),,))) This formula can be entered normally as opposed to your COUNT formula -- Regards, Peo Sjoblom "ub" wrote in message ... Hi I have the following formula in one of the cell =COUNT(IF((TRIM($C$4:$C$96)="P")*ISBLANK($M$4:$M$9 6),$A$4:$A$96)) But I have autofilter command and I want the rows to be counted based on the output of the filter results. But this formula always gives the output os the rows. Please help |
Count if function
using this formula , I get # value error.
Thanks for all your help. "Peo Sjoblom" wrote: You should use the formula I posted =SUMPRODUCT(--(ISBLANK($M$4:$M$96)),--(TRIM($C$4:$C$96)="P"),--(ISNUMBER($A$4:$A$96)),SUBTOTAL(3,OFFSET($A$4,ROW( $A$4:$A$96)-MIN(ROW($A$4:$A$96)),,))) or use custom filtering and then simply =SUBTOTAL(2,$A$4:$A$96) nothing else -- Regards, Peo Sjoblom "ub" wrote in message ... Hi I am using the formula : =SUMPRODUCT(--(ISBLANK($M$4:$M$96)),--(TRIM($C$4:$C$96)="P"),SUBTOTAL(2,A4:A96)) Please advise , what misytake I am doing. Thanks "Peo Sjoblom" wrote: I don't see how that formula can return a value error unless you calculate it with something else like =SUBTOTAL(2,A4:A96)*x and what you calculate it with is a text value also make sure you don't have any errors in your ranges although count does not get affected by errors like a sum would -- Regards, Peo Sjoblom "ub" wrote in message ... Hi, when I use Subtotal(2,a4:a96), It get error messgae # value. Please advise "Peo Sjoblom" wrote: If you are going to use a filter then you don't need a formula like this, filter on blanks in M and P in C then just count the visible cells in A =SUBTOTAL(2,A4:A96) If you still want a formula and all your data starts in row 4 with headers in 3 you can use =SUMPRODUCT(--(ISBLANK($M$4:$M$96)),--(TRIM($C$4:$C$96)="P"),--(ISNUMBER($A$4:$A$96)),SUBTOTAL(3,OFFSET($A$4,ROW( $A$4:$A$96)-MIN(ROW($A$4:$A$96)),,))) although I would probably change remove isblank and use range="" =SUMPRODUCT(--($M$4:$M$96=""),--(TRIM($C$4:$C$96)="P"),--(ISNUMBER($A$4:$A$96)),SUBTOTAL(3,OFFSET($A$4,ROW( $A$4:$A$96)-MIN(ROW($A$4:$A$96)),,))) This formula can be entered normally as opposed to your COUNT formula -- Regards, Peo Sjoblom "ub" wrote in message ... Hi I have the following formula in one of the cell =COUNT(IF((TRIM($C$4:$C$96)="P")*ISBLANK($M$4:$M$9 6),$A$4:$A$96)) But I have autofilter command and I want the rows to be counted based on the output of the filter results. But this formula always gives the output os the rows. Please help |
Count if function
It works trust me, so you have either #VALUE! errors (probably in C4:C96)
somewhere within your data or you simply didn't copy and pasted the formula as posted -- Regards, Peo Sjoblom "ub" wrote in message ... using this formula , I get # value error. Thanks for all your help. "Peo Sjoblom" wrote: You should use the formula I posted =SUMPRODUCT(--(ISBLANK($M$4:$M$96)),--(TRIM($C$4:$C$96)="P"),--(ISNUMBER($A$4:$A$96)),SUBTOTAL(3,OFFSET($A$4,ROW( $A$4:$A$96)-MIN(ROW($A$4:$A$96)),,))) or use custom filtering and then simply =SUBTOTAL(2,$A$4:$A$96) nothing else -- Regards, Peo Sjoblom "ub" wrote in message ... Hi I am using the formula : =SUMPRODUCT(--(ISBLANK($M$4:$M$96)),--(TRIM($C$4:$C$96)="P"),SUBTOTAL(2,A4:A96)) Please advise , what misytake I am doing. Thanks "Peo Sjoblom" wrote: I don't see how that formula can return a value error unless you calculate it with something else like =SUBTOTAL(2,A4:A96)*x and what you calculate it with is a text value also make sure you don't have any errors in your ranges although count does not get affected by errors like a sum would -- Regards, Peo Sjoblom "ub" wrote in message ... Hi, when I use Subtotal(2,a4:a96), It get error messgae # value. Please advise "Peo Sjoblom" wrote: If you are going to use a filter then you don't need a formula like this, filter on blanks in M and P in C then just count the visible cells in A =SUBTOTAL(2,A4:A96) If you still want a formula and all your data starts in row 4 with headers in 3 you can use =SUMPRODUCT(--(ISBLANK($M$4:$M$96)),--(TRIM($C$4:$C$96)="P"),--(ISNUMBER($A$4:$A$96)),SUBTOTAL(3,OFFSET($A$4,ROW( $A$4:$A$96)-MIN(ROW($A$4:$A$96)),,))) although I would probably change remove isblank and use range="" =SUMPRODUCT(--($M$4:$M$96=""),--(TRIM($C$4:$C$96)="P"),--(ISNUMBER($A$4:$A$96)),SUBTOTAL(3,OFFSET($A$4,ROW( $A$4:$A$96)-MIN(ROW($A$4:$A$96)),,))) This formula can be entered normally as opposed to your COUNT formula -- Regards, Peo Sjoblom "ub" wrote in message ... Hi I have the following formula in one of the cell =COUNT(IF((TRIM($C$4:$C$96)="P")*ISBLANK($M$4:$M$9 6),$A$4:$A$96)) But I have autofilter command and I want the rows to be counted based on the output of the filter results. But this formula always gives the output os the rows. Please help |
Count if function
OK, now I know, where the error could be.
My column 'C' has only values till c40, rest all are blank. So please advise what should I do in this case. Thanks "Peo Sjoblom" wrote: It works trust me, so you have either #VALUE! errors (probably in C4:C96) somewhere within your data or you simply didn't copy and pasted the formula as posted -- Regards, Peo Sjoblom "ub" wrote in message ... using this formula , I get # value error. Thanks for all your help. "Peo Sjoblom" wrote: You should use the formula I posted =SUMPRODUCT(--(ISBLANK($M$4:$M$96)),--(TRIM($C$4:$C$96)="P"),--(ISNUMBER($A$4:$A$96)),SUBTOTAL(3,OFFSET($A$4,ROW( $A$4:$A$96)-MIN(ROW($A$4:$A$96)),,))) or use custom filtering and then simply =SUBTOTAL(2,$A$4:$A$96) nothing else -- Regards, Peo Sjoblom "ub" wrote in message ... Hi I am using the formula : =SUMPRODUCT(--(ISBLANK($M$4:$M$96)),--(TRIM($C$4:$C$96)="P"),SUBTOTAL(2,A4:A96)) Please advise , what misytake I am doing. Thanks "Peo Sjoblom" wrote: I don't see how that formula can return a value error unless you calculate it with something else like =SUBTOTAL(2,A4:A96)*x and what you calculate it with is a text value also make sure you don't have any errors in your ranges although count does not get affected by errors like a sum would -- Regards, Peo Sjoblom "ub" wrote in message ... Hi, when I use Subtotal(2,a4:a96), It get error messgae # value. Please advise "Peo Sjoblom" wrote: If you are going to use a filter then you don't need a formula like this, filter on blanks in M and P in C then just count the visible cells in A =SUBTOTAL(2,A4:A96) If you still want a formula and all your data starts in row 4 with headers in 3 you can use =SUMPRODUCT(--(ISBLANK($M$4:$M$96)),--(TRIM($C$4:$C$96)="P"),--(ISNUMBER($A$4:$A$96)),SUBTOTAL(3,OFFSET($A$4,ROW( $A$4:$A$96)-MIN(ROW($A$4:$A$96)),,))) although I would probably change remove isblank and use range="" =SUMPRODUCT(--($M$4:$M$96=""),--(TRIM($C$4:$C$96)="P"),--(ISNUMBER($A$4:$A$96)),SUBTOTAL(3,OFFSET($A$4,ROW( $A$4:$A$96)-MIN(ROW($A$4:$A$96)),,))) This formula can be entered normally as opposed to your COUNT formula -- Regards, Peo Sjoblom "ub" wrote in message ... Hi I have the following formula in one of the cell =COUNT(IF((TRIM($C$4:$C$96)="P")*ISBLANK($M$4:$M$9 6),$A$4:$A$96)) But I have autofilter command and I want the rows to be counted based on the output of the filter results. But this formula always gives the output os the rows. Please help |
| All times are GMT +1. The time now is 01:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com