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

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

Count Function
 

=SUMPRODUCT((B1:B100="F17000")*(A1:A100="Out of
Warranty"))+SUMPRODUCT((C1:C100="F17000")*(A1:A100 ="Out of Warranty"))

--
Gary''s Student - gsnu200813

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

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

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