| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTA with IF in an array
Hi
I need asistance with this problem: Column A Column B P1 08:22:00 P2 02:45:00 P1 03:15:00 P3 00:45:00 P3 11:14:00 P2 21:07:00 Column A is formated to contain Text Column B is formated to contain hh:mm:ss data Considering the above data set, I want to count the number of cells in Column B which have values in them (like a COUNTA) but only where Column A = "P3", for example. I.e. the answer will be "2" for this example. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTA with IF in an array
=SUMPRODUCT(-(A1:A6="P3"),-(ISNUMBER(B1:B6)))
"WildWill" wrote: Hi I need asistance with this problem: Column A Column B P1 08:22:00 P2 02:45:00 P1 03:15:00 P3 00:45:00 P3 11:14:00 P2 21:07:00 Column A is formated to contain Text Column B is formated to contain hh:mm:ss data Considering the above data set, I want to count the number of cells in Column B which have values in them (like a COUNTA) but only where Column A = "P3", for example. I.e. the answer will be "2" for this example. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTA with IF in an array
=SUMPRODUCT((A1:A6="P3")*(B1:B6<""))
-- Gary''s Student - gsnu200806 "WildWill" wrote: Hi I need asistance with this problem: Column A Column B P1 08:22:00 P2 02:45:00 P1 03:15:00 P3 00:45:00 P3 11:14:00 P2 21:07:00 Column A is formated to contain Text Column B is formated to contain hh:mm:ss data Considering the above data set, I want to count the number of cells in Column B which have values in them (like a COUNTA) but only where Column A = "P3", for example. I.e. the answer will be "2" for this example. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTA with IF in an array
Thanks Teethless mama - SORTED!
"Teethless mama" wrote: =SUMPRODUCT(-(A1:A6="P3"),-(ISNUMBER(B1:B6))) "WildWill" wrote: Hi I need asistance with this problem: Column A Column B P1 08:22:00 P2 02:45:00 P1 03:15:00 P3 00:45:00 P3 11:14:00 P2 21:07:00 Column A is formated to contain Text Column B is formated to contain hh:mm:ss data Considering the above data set, I want to count the number of cells in Column B which have values in them (like a COUNTA) but only where Column A = "P3", for example. I.e. the answer will be "2" for this example. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTA with IF in an array
Thanks GS!
"Gary''s Student" wrote: =SUMPRODUCT((A1:A6="P3")*(B1:B6<"")) -- Gary''s Student - gsnu200806 "WildWill" wrote: Hi I need asistance with this problem: Column A Column B P1 08:22:00 P2 02:45:00 P1 03:15:00 P3 00:45:00 P3 11:14:00 P2 21:07:00 Column A is formated to contain Text Column B is formated to contain hh:mm:ss data Considering the above data set, I want to count the number of cells in Column B which have values in them (like a COUNTA) but only where Column A = "P3", for example. I.e. the answer will be "2" for this example. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using COUNTA for a variable array | Excel Worksheet Functions | |||
| counta in array formula not working | Excel Worksheet Functions | |||
| meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
| counta | Excel Discussion (Misc queries) | |||
| COUNTA Function not working =COUNTA(C3:C69,"NH") | Excel Worksheet Functions | |||
Linear Mode
