Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Function
I need to be able to count an item if a key word is in a different columns.
ex: Count the cells that contain "F17000" (Columns B & C) only if it is "Out of Warranty" (Column A). Should return the value 2 Column A Column B Column C Row 1 In Warranty F17000 1085730 Row 2 Out Of Warranty 1085730 F17000 Row 3 Out Of Warranty F17000 1085730 Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Function
Hi,
Try =SUMPRODUCT(--(((B1:B100="F17000")+(C1:C100="F17000"))0),--(A1:A100="Out of Warranty")) -- Thanks, Shane Devenshire "Mickey" wrote: I need to be able to count an item if a key word is in a different columns. ex: Count the cells that contain "F17000" (Columns B & C) only if it is "Out of Warranty" (Column A). Should return the value 2 Column A Column B Column C Row 1 In Warranty F17000 1085730 Row 2 Out Of Warranty 1085730 F17000 Row 3 Out Of Warranty F17000 1085730 Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Function
=SUMPRODUCT((B1:B100="F17000")*(A1:A100="Out of
Warranty"))+SUMPRODUCT((C1:C100="F17000")*(A1:A100 ="Out of Warranty")) -- Gary''s Student - gsnu200813 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Function
Hi Gary,
Your formula will double count if row 2 has F17000 in columns B and C. -- Thanks, Shane Devenshire "Gary''s Student" wrote: =SUMPRODUCT((B1:B100="F17000")*(A1:A100="Out of Warranty"))+SUMPRODUCT((C1:C100="F17000")*(A1:A100 ="Out of Warranty")) -- Gary''s Student - gsnu200813 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Function
Hi,
You can also use the following array formula (Ctrl+Shift+Enter) SUM(IF(((B1:B3="F17000")+(C1:C3="F17000"))*(A1:A3= "Out of Warranty"),1)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mickey" wrote in message ... I need to be able to count an item if a key word is in a different columns. ex: Count the cells that contain "F17000" (Columns B & C) only if it is "Out of Warranty" (Column A). Should return the value 2 Column A Column B Column C Row 1 In Warranty F17000 1085730 Row 2 Out Of Warranty 1085730 F17000 Row 3 Out Of Warranty F17000 1085730 Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Function
Ouch!! & thanks!
Your SUMPRODUCT() handles that case correctly. This is why, whenever I face complicated criteria, I run away and hide behind a helper column. -- Gary''s Student - gsnu200813 "ShaneDevenshire" wrote: Hi Gary, Your formula will double count if row 2 has F17000 in columns B and C. -- Thanks, Shane Devenshire "Gary''s Student" wrote: =SUMPRODUCT((B1:B100="F17000")*(A1:A100="Out of Warranty"))+SUMPRODUCT((C1:C100="F17000")*(A1:A100 ="Out of Warranty")) -- Gary''s Student - gsnu200813 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNT FUNCTION? | Excel Worksheet Functions | |||
count function | Excel Worksheet Functions | |||
Count function | Excel Worksheet Functions | |||
Count Function | Excel Discussion (Misc queries) | |||
Count If Function | Excel Worksheet Functions |