|
COUNT(IF((,,,)) function
hi all,
i am trying to duplicate the example in microsoft help about the count(if((,,,)) function in my spreadsheet but it gives me false results. below is the syntax i am using: {=COUNT(IF((G2:G8000="12:00")*('Y2:Y8000<"180000") ,Y2:Y8000))} i am wondering if in my first argument G2:G800="12:00" would be processed as i am trying to filter out my data with respect to time? i tried substituting text within that column and the syntax gave me the needed results. {=COUNT(IF((G2:G8000="test")*('Y2:Y8000<"180000"), Y2:Y8000))} = 3 (i put 3 entries of different values less than 180000) so, bottom line, will using "time" as part of the syntax will work in this particular scenario? thanks in advance... vjmc |
COUNT(IF((,,,)) function
What does "12:00" represent? 12:00 AM or 12:00 PM ? If it's PM try this:
=SUMPRODUCT(--(G2:G8000=TIME(12,0,0)),--(Y2:Y8000<180000)) Note that if a cell in Y2:Y8000 is empty it will evaluate as being <180000 and could lead to incorrect results. You can account for that by testing to make sure the cells do contain numbers: =SUMPRODUCT(--(G2:G8000=TIME(12,0,0)),--(ISNUMBER(Y2:Y8000)),--(Y2:Y8000<180000)) You can also shorten it a little and replace TIME(12,0,0) with 0.5. 12:00 PM is equivalent to decimal 0.5. =SUMPRODUCT(--(G2:G8000=0.5),--(ISNUMBER(Y2:Y8000)),--(Y2:Y8000<180000)) -- Biff Microsoft Excel MVP "vjmc" wrote in message ... hi all, i am trying to duplicate the example in microsoft help about the count(if((,,,)) function in my spreadsheet but it gives me false results. below is the syntax i am using: {=COUNT(IF((G2:G8000="12:00")*('Y2:Y8000<"180000") ,Y2:Y8000))} i am wondering if in my first argument G2:G800="12:00" would be processed as i am trying to filter out my data with respect to time? i tried substituting text within that column and the syntax gave me the needed results. {=COUNT(IF((G2:G8000="test")*('Y2:Y8000<"180000"), Y2:Y8000))} = 3 (i put 3 entries of different values less than 180000) so, bottom line, will using "time" as part of the syntax will work in this particular scenario? thanks in advance... vjmc |
COUNT(IF((,,,)) function
hi biff,
thanks for the feedback. what i am doing is, i am trying to filter out a system generated statistics file in 30min intervals for 24hours. based on your syntax given, this is an array right? vlad "T. Valko" wrote: What does "12:00" represent? 12:00 AM or 12:00 PM ? If it's PM try this: =SUMPRODUCT(--(G2:G8000=TIME(12,0,0)),--(Y2:Y8000<180000)) Note that if a cell in Y2:Y8000 is empty it will evaluate as being <180000 and could lead to incorrect results. You can account for that by testing to make sure the cells do contain numbers: =SUMPRODUCT(--(G2:G8000=TIME(12,0,0)),--(ISNUMBER(Y2:Y8000)),--(Y2:Y8000<180000)) You can also shorten it a little and replace TIME(12,0,0) with 0.5. 12:00 PM is equivalent to decimal 0.5. =SUMPRODUCT(--(G2:G8000=0.5),--(ISNUMBER(Y2:Y8000)),--(Y2:Y8000<180000)) -- Biff Microsoft Excel MVP "vjmc" wrote in message ... hi all, i am trying to duplicate the example in microsoft help about the count(if((,,,)) function in my spreadsheet but it gives me false results. below is the syntax i am using: {=COUNT(IF((G2:G8000="12:00")*('Y2:Y8000<"180000") ,Y2:Y8000))} i am wondering if in my first argument G2:G800="12:00" would be processed as i am trying to filter out my data with respect to time? i tried substituting text within that column and the syntax gave me the needed results. {=COUNT(IF((G2:G8000="test")*('Y2:Y8000<"180000"), Y2:Y8000))} = 3 (i put 3 entries of different values less than 180000) so, bottom line, will using "time" as part of the syntax will work in this particular scenario? thanks in advance... vjmc |
COUNT(IF((,,,)) function
based on your syntax given, this is an array right?
Yes and no. It's a formula that works on arrays but it doesn't have to be array entered (CTRL, SHIFT, ENTER) but it'll work either way. It COUNTS the instances where G2:G8000 = 12:00 PM and Y2:Y8000 <180000 -- Biff Microsoft Excel MVP "vjmc" wrote in message ... hi biff, thanks for the feedback. what i am doing is, i am trying to filter out a system generated statistics file in 30min intervals for 24hours. based on your syntax given, this is an array right? vlad "T. Valko" wrote: What does "12:00" represent? 12:00 AM or 12:00 PM ? If it's PM try this: =SUMPRODUCT(--(G2:G8000=TIME(12,0,0)),--(Y2:Y8000<180000)) Note that if a cell in Y2:Y8000 is empty it will evaluate as being <180000 and could lead to incorrect results. You can account for that by testing to make sure the cells do contain numbers: =SUMPRODUCT(--(G2:G8000=TIME(12,0,0)),--(ISNUMBER(Y2:Y8000)),--(Y2:Y8000<180000)) You can also shorten it a little and replace TIME(12,0,0) with 0.5. 12:00 PM is equivalent to decimal 0.5. =SUMPRODUCT(--(G2:G8000=0.5),--(ISNUMBER(Y2:Y8000)),--(Y2:Y8000<180000)) -- Biff Microsoft Excel MVP "vjmc" wrote in message ... hi all, i am trying to duplicate the example in microsoft help about the count(if((,,,)) function in my spreadsheet but it gives me false results. below is the syntax i am using: {=COUNT(IF((G2:G8000="12:00")*('Y2:Y8000<"180000") ,Y2:Y8000))} i am wondering if in my first argument G2:G800="12:00" would be processed as i am trying to filter out my data with respect to time? i tried substituting text within that column and the syntax gave me the needed results. {=COUNT(IF((G2:G8000="test")*('Y2:Y8000<"180000"), Y2:Y8000))} = 3 (i put 3 entries of different values less than 180000) so, bottom line, will using "time" as part of the syntax will work in this particular scenario? thanks in advance... vjmc |
COUNT(IF((,,,)) function
hi biff,
the syntax you gave is working. thanks a lot and i highly appreciate it! best regards, vlad "T. Valko" wrote: based on your syntax given, this is an array right? Yes and no. It's a formula that works on arrays but it doesn't have to be array entered (CTRL, SHIFT, ENTER) but it'll work either way. It COUNTS the instances where G2:G8000 = 12:00 PM and Y2:Y8000 <180000 -- Biff Microsoft Excel MVP "vjmc" wrote in message ... hi biff, thanks for the feedback. what i am doing is, i am trying to filter out a system generated statistics file in 30min intervals for 24hours. based on your syntax given, this is an array right? vlad "T. Valko" wrote: What does "12:00" represent? 12:00 AM or 12:00 PM ? If it's PM try this: =SUMPRODUCT(--(G2:G8000=TIME(12,0,0)),--(Y2:Y8000<180000)) Note that if a cell in Y2:Y8000 is empty it will evaluate as being <180000 and could lead to incorrect results. You can account for that by testing to make sure the cells do contain numbers: =SUMPRODUCT(--(G2:G8000=TIME(12,0,0)),--(ISNUMBER(Y2:Y8000)),--(Y2:Y8000<180000)) You can also shorten it a little and replace TIME(12,0,0) with 0.5. 12:00 PM is equivalent to decimal 0.5. =SUMPRODUCT(--(G2:G8000=0.5),--(ISNUMBER(Y2:Y8000)),--(Y2:Y8000<180000)) -- Biff Microsoft Excel MVP "vjmc" wrote in message ... hi all, i am trying to duplicate the example in microsoft help about the count(if((,,,)) function in my spreadsheet but it gives me false results. below is the syntax i am using: {=COUNT(IF((G2:G8000="12:00")*('Y2:Y8000<"180000") ,Y2:Y8000))} i am wondering if in my first argument G2:G800="12:00" would be processed as i am trying to filter out my data with respect to time? i tried substituting text within that column and the syntax gave me the needed results. {=COUNT(IF((G2:G8000="test")*('Y2:Y8000<"180000"), Y2:Y8000))} = 3 (i put 3 entries of different values less than 180000) so, bottom line, will using "time" as part of the syntax will work in this particular scenario? thanks in advance... vjmc |
COUNT(IF((,,,)) function
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "vjmc" wrote in message ... hi biff, the syntax you gave is working. thanks a lot and i highly appreciate it! best regards, vlad "T. Valko" wrote: based on your syntax given, this is an array right? Yes and no. It's a formula that works on arrays but it doesn't have to be array entered (CTRL, SHIFT, ENTER) but it'll work either way. It COUNTS the instances where G2:G8000 = 12:00 PM and Y2:Y8000 <180000 -- Biff Microsoft Excel MVP "vjmc" wrote in message ... hi biff, thanks for the feedback. what i am doing is, i am trying to filter out a system generated statistics file in 30min intervals for 24hours. based on your syntax given, this is an array right? vlad "T. Valko" wrote: What does "12:00" represent? 12:00 AM or 12:00 PM ? If it's PM try this: =SUMPRODUCT(--(G2:G8000=TIME(12,0,0)),--(Y2:Y8000<180000)) Note that if a cell in Y2:Y8000 is empty it will evaluate as being <180000 and could lead to incorrect results. You can account for that by testing to make sure the cells do contain numbers: =SUMPRODUCT(--(G2:G8000=TIME(12,0,0)),--(ISNUMBER(Y2:Y8000)),--(Y2:Y8000<180000)) You can also shorten it a little and replace TIME(12,0,0) with 0.5. 12:00 PM is equivalent to decimal 0.5. =SUMPRODUCT(--(G2:G8000=0.5),--(ISNUMBER(Y2:Y8000)),--(Y2:Y8000<180000)) -- Biff Microsoft Excel MVP "vjmc" wrote in message ... hi all, i am trying to duplicate the example in microsoft help about the count(if((,,,)) function in my spreadsheet but it gives me false results. below is the syntax i am using: {=COUNT(IF((G2:G8000="12:00")*('Y2:Y8000<"180000") ,Y2:Y8000))} i am wondering if in my first argument G2:G800="12:00" would be processed as i am trying to filter out my data with respect to time? i tried substituting text within that column and the syntax gave me the needed results. {=COUNT(IF((G2:G8000="test")*('Y2:Y8000<"180000"), Y2:Y8000))} = 3 (i put 3 entries of different values less than 180000) so, bottom line, will using "time" as part of the syntax will work in this particular scenario? thanks in advance... vjmc |
| All times are GMT +1. The time now is 02:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com