| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count cells which contain a formula that returns TRUE
The question in princilpe refers to any cell that contain formula.I\ve
noticed that if I use COUNTIF on cells that contains formula returning TRUE it doesn't count.It count only if I simply type TRUE in the cells |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to count cells which contain a formula that returns TRUE
In A1 thru A7 enter:
=1=1 In A8 thru A10 enter: =1=2 We see in A1 thru A10: TRUE TRUE TRUE TRUE TRUE TRUE TRUE FALSE FALSE FALSE in another cell enter: =COUNTIF(A1:A10,TRUE) which correctly displays 7 -- Gary''s Student - gsnu200860 "Art" wrote: The question in princilpe refers to any cell that contain formula.I\ve noticed that if I use COUNTIF on cells that contains formula returning TRUE it doesn't count.It count only if I simply type TRUE in the cells |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to count cells which contain a formula that returns TRUE
MAke sure your formulas which return TRUE returns the boolean TRUE and not a
text (within double quotes) like To count on formulas like this one you need to use COUNTIF(a:a,"true") =IF(A1=0,"true","false") To count on formulas which return boolean you can try COUNTIF(a:a,TRUE) =IF(A1=0,TRUE,FALSE) If this post helps click Yes --------------- Jacob Skaria "Art" wrote: The question in princilpe refers to any cell that contain formula.I\ve noticed that if I use COUNTIF on cells that contains formula returning TRUE it doesn't count.It count only if I simply type TRUE in the cells |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to count cells which contain a formula that returns TRUE
Are you sure that your formula is returning the boolean value TRUE and not
the text string "TRUE" ? -- David Biddulph "Art" wrote in message ... The question in princilpe refers to any cell that contain formula.I\ve noticed that if I use COUNTIF on cells that contains formula returning TRUE it doesn't count.It count only if I simply type TRUE in the cells |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to count cells which contain a formula that returns TRUE
It works in your example, but not in my particular case :(
I'm using: =IF(AND(C181=1,G181=0,J181=1), "TRUE", "") this returns TRUE or nothing. I just want simply count the cells returning TRUE. Grrrr Thanks, "Gary''s Student" wrote: In A1 thru A7 enter: =1=1 In A8 thru A10 enter: =1=2 We see in A1 thru A10: TRUE TRUE TRUE TRUE TRUE TRUE TRUE FALSE FALSE FALSE in another cell enter: =COUNTIF(A1:A10,TRUE) which correctly displays 7 -- Gary''s Student - gsnu200860 "Art" wrote: The question in princilpe refers to any cell that contain formula.I\ve noticed that if I use COUNTIF on cells that contains formula returning TRUE it doesn't count.It count only if I simply type TRUE in the cells |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to count cells which contain a formula that returns TRUE
On Wed, 12 Aug 2009 03:26:01 -0700, Art wrote:
The question in princilpe refers to any cell that contain formula.I\ve noticed that if I use COUNTIF on cells that contains formula returning TRUE it doesn't count.It count only if I simply type TRUE in the cells Post your formula. There is likely some error in how you are trying to do this, as I cannot reproduce your problem here. --ron |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to count cells which contain a formula that returns TRUE
I've tried both formulas:
=IF(A1:A10,TRUE) and =IF(A1:A10,"true") Both give 0 in my case. Thanks, "Jacob Skaria" wrote: MAke sure your formulas which return TRUE returns the boolean TRUE and not a text (within double quotes) like To count on formulas like this one you need to use COUNTIF(a:a,"true") =IF(A1=0,"true","false") To count on formulas which return boolean you can try COUNTIF(a:a,TRUE) =IF(A1=0,TRUE,FALSE) If this post helps click Yes --------------- Jacob Skaria "Art" wrote: The question in princilpe refers to any cell that contain formula.I\ve noticed that if I use COUNTIF on cells that contains formula returning TRUE it doesn't count.It count only if I simply type TRUE in the cells |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to count cells which contain a formula that returns TRUE
=IF(AND(C181=1,G181=0,J181=1), "TRUE", "")
this returns TRUE or nothing. I just want simply count the cells returning TRUE. I've tried both formulas: =COUNTIF (A1:A10,TRUE) or =COUNTIF(A1:A10,"TRUE") Both give 0 in my case. Thanks, "Ron Rosenfeld" wrote: On Wed, 12 Aug 2009 03:26:01 -0700, Art wrote: The question in princilpe refers to any cell that contain formula.I\ve noticed that if I use COUNTIF on cells that contains formula returning TRUE it doesn't count.It count only if I simply type TRUE in the cells Post your formula. There is likely some error in how you are trying to do this, as I cannot reproduce your problem here. --ron |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to count cells which contain a formula that returns TRUE
Your formula is producing the text string "TRUE", not the logical value
TRYE. Get rid of the quote marks. -- David Biddulph "Art" wrote in message ... It works in your example, but not in my particular case :( I'm using: =IF(AND(C181=1,G181=0,J181=1), "TRUE", "") this returns TRUE or nothing. I just want simply count the cells returning TRUE. Grrrr Thanks, "Gary''s Student" wrote: In A1 thru A7 enter: =1=1 In A8 thru A10 enter: =1=2 We see in A1 thru A10: TRUE TRUE TRUE TRUE TRUE TRUE TRUE FALSE FALSE FALSE in another cell enter: =COUNTIF(A1:A10,TRUE) which correctly displays 7 -- Gary''s Student - gsnu200860 "Art" wrote: The question in princilpe refers to any cell that contain formula.I\ve noticed that if I use COUNTIF on cells that contains formula returning TRUE it doesn't count.It count only if I simply type TRUE in the cells |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to count cells which contain a formula that returns TRUE
Are you sure that your COUNTIF formulae work, Jacob? For me, with Excel
2003, it seems to treat both as looking for the boolean, not the text string. -- David Biddulph "Jacob Skaria" wrote in message ... MAke sure your formulas which return TRUE returns the boolean TRUE and not a text (within double quotes) like To count on formulas like this one you need to use COUNTIF(a:a,"true") =IF(A1=0,"true","false") To count on formulas which return boolean you can try COUNTIF(a:a,TRUE) =IF(A1=0,TRUE,FALSE) If this post helps click Yes --------------- Jacob Skaria "Art" wrote: The question in princilpe refers to any cell that contain formula.I\ve noticed that if I use COUNTIF on cells that contains formula returning TRUE it doesn't count.It count only if I simply type TRUE in the cells |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to count cells which contain a formula that returns TRUE
Oops...you are right we cannot and will have to use
=SUMPRODUCT(--(A1:A10="TRUE")) If this post helps click Yes --------------- Jacob Skaria "David Biddulph" wrote: Are you sure that your COUNTIF formulae work, Jacob? For me, with Excel 2003, it seems to treat both as looking for the boolean, not the text string. -- David Biddulph "Jacob Skaria" wrote in message ... MAke sure your formulas which return TRUE returns the boolean TRUE and not a text (within double quotes) like To count on formulas like this one you need to use COUNTIF(a:a,"true") =IF(A1=0,"true","false") To count on formulas which return boolean you can try COUNTIF(a:a,TRUE) =IF(A1=0,TRUE,FALSE) If this post helps click Yes --------------- Jacob Skaria "Art" wrote: The question in princilpe refers to any cell that contain formula.I\ve noticed that if I use COUNTIF on cells that contains formula returning TRUE it doesn't count.It count only if I simply type TRUE in the cells |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to count cells which contain a formula that returns TRUE
I've got it working,but ...
The formula returns text string,so I changed it to e.g Yes =IF(AND(C181=1,G181=0,J181=1), "Yes", "") And now using =COUNTIF (A1:A10,Yes) it works! But as I mentioned before with the formula: =IF(AND(C181=1,G181=0,J181=1), "TRUE", "") and formulas: =COUNTIF (A1:A10,TRUE) or =COUNTIF(A1:A10,"TRUE") Both give 0 in my case. Thank you for your quick replies. Art "Art" wrote: The question in princilpe refers to any cell that contain formula.I\ve noticed that if I use COUNTIF on cells that contains formula returning TRUE it doesn't count.It count only if I simply type TRUE in the cells |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to count cells which contain a formula that returns TRUE
Try the below array formula which should count both text "TRUE" and boolean
TRUE =SUM(IF(IF(F1:F20<"",F1:F20&"A")="TRUEA",1,0)) If this post helps click Yes --------------- Jacob Skaria "Art" wrote: I've got it working,but ... The formula returns text string,so I changed it to e.g Yes =IF(AND(C181=1,G181=0,J181=1), "Yes", "") And now using =COUNTIF (A1:A10,Yes) it works! But as I mentioned before with the formula: =IF(AND(C181=1,G181=0,J181=1), "TRUE", "") and formulas: =COUNTIF (A1:A10,TRUE) or =COUNTIF(A1:A10,"TRUE") Both give 0 in my case. Thank you for your quick replies. Art "Art" wrote: The question in princilpe refers to any cell that contain formula.I\ve noticed that if I use COUNTIF on cells that contains formula returning TRUE it doesn't count.It count only if I simply type TRUE in the cells |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to count cells which contain a formula that returns TRUE
it seems to treat both as looking for the boolean, not the text string.
To count TEXT "true": =COUNTIF(A1:A10,"*true") -- Biff Microsoft Excel MVP "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Are you sure that your COUNTIF formulae work, Jacob? For me, with Excel 2003, it seems to treat both as looking for the boolean, not the text string. -- David Biddulph "Jacob Skaria" wrote in message ... MAke sure your formulas which return TRUE returns the boolean TRUE and not a text (within double quotes) like To count on formulas like this one you need to use COUNTIF(a:a,"true") =IF(A1=0,"true","false") To count on formulas which return boolean you can try COUNTIF(a:a,TRUE) =IF(A1=0,TRUE,FALSE) If this post helps click Yes --------------- Jacob Skaria "Art" wrote: The question in princilpe refers to any cell that contain formula.I\ve noticed that if I use COUNTIF on cells that contains formula returning TRUE it doesn't count.It count only if I simply type TRUE in the cells |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to count cells which contain a formula that returns TRUE
On Wed, 12 Aug 2009 04:16:02 -0700, Art wrote:
=IF(AND(C181=1,G181=0,J181=1), "TRUE", "") this returns TRUE or nothing. I just want simply count the cells returning TRUE. I've tried both formulas: =COUNTIF (A1:A10,TRUE) or =COUNTIF(A1:A10,"TRUE") Both give 0 in my case. Thanks, This is a fascinating finding. One simple way around it is to change your IF formula to return the Boolean: =IF(AND(C181=1,G181=0,J181=1), TRUE, "") COUNTIF will then work as designed. I think what may be happening is that COUNTIF is changing what should be the text string "TRUE" into the Boolean, because both of these formulas seem to work, so long as you change your IF formula to return a BOOLEAN TRUE: =COUNTIF(A1:A10,"TRUE") =COUNTIF(A1:A10,TRUE) --ron |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to count cells which contain a formula that returns TRUE
On Wed, 12 Aug 2009 04:16:02 -0700, Art wrote:
=IF(AND(C181=1,G181=0,J181=1), "TRUE", "") this returns TRUE or nothing. I just want simply count the cells returning TRUE. I've tried both formulas: =COUNTIF (A1:A10,TRUE) or =COUNTIF(A1:A10,"TRUE") Both give 0 in my case. Thanks, And, of course, you could also change the COUNTIF formula: =COUNTIF(A1:A10,"*TRUE") --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to not count cells which contain a formula that returns " "? | Excel Worksheet Functions | |||
| =IF(test,true,false) only ever returns "true"? | Excel Worksheet Functions | |||
| An if statment tat returns a true blank | Excel Worksheet Functions | |||
| Creating a count when the information from two cells is true. | Excel Worksheet Functions | |||
| Code returns TRUE instead of value | Excel Discussion (Misc queries) | |||
Linear Mode
