#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 112
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,344
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,344
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
COUNT FUNCTION? Zakynthos Excel Worksheet Functions 5 May 9th 08 03:14 PM
count function AJ Patel[_2_] Excel Worksheet Functions 7 March 11th 08 08:59 PM
Count function MarekZ Excel Worksheet Functions 5 July 23rd 07 11:59 PM
Count Function Portuga Excel Discussion (Misc queries) 1 May 31st 06 04:53 PM
Count If Function Michele Excel Worksheet Functions 3 April 25th 05 02:31 PM


All times are GMT +1. The time now is 03:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"