A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Counting cells only if cell X equals "yes"



 
 
Thread Tools Display Modes
  #1  
Old November 5th 09, 10:37 PM posted to microsoft.public.excel.worksheet.functions
Frustrated
external usenet poster
 
Posts: 55
Default Counting cells only if cell X equals "yes"

I am tracking my sales reps sales. I currently have a formula to track that,
but now i need to track their sales only if they add a certian feature to the
sale. So now i need to find a formula that tracks their sales if they answer
yes to cell x. If they answer no to cell X i do not want to count that
sale. Can someone please help. The formula that tracks their individual
sales is COUNTIF($A$19:$A$3703, A10)
Ads
  #2  
Old November 5th 09, 10:46 PM posted to microsoft.public.excel.worksheet.functions
brownti via OfficeKB.com
external usenet poster
 
Posts: 103
Default Counting cells only if cell X equals "yes"

=countif($X$19:$X$3703,"Yes")

Frustrated wrote:
>I am tracking my sales reps sales. I currently have a formula to track that,
>but now i need to track their sales only if they add a certian feature to the
>sale. So now i need to find a formula that tracks their sales if they answer
>yes to cell x. If they answer no to cell X i do not want to count that
>sale. Can someone please help. The formula that tracks their individual
>sales is COUNTIF($A$19:$A$3703, A10)


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200911/1

  #3  
Old November 5th 09, 10:47 PM posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom[_3_]
external usenet poster
 
Posts: 136
Default Counting cells only if cell X equals "yes"

Please refrain from multiposting


=SUMPRODUCT(--($A$19:$A$3703=A10),--($B$19:$B$3703="Yes"))

--


Regards,


Peo Sjoblom


"Frustrated" > wrote in message
...
>I am tracking my sales reps sales. I currently have a formula to track
>that,
> but now i need to track their sales only if they add a certian feature to
> the
> sale. So now i need to find a formula that tracks their sales if they
> answer
> yes to cell x. If they answer no to cell X i do not want to count that
> sale. Can someone please help. The formula that tracks their individual
> sales is COUNTIF($A$19:$A$3703, A10)



  #4  
Old November 5th 09, 10:51 PM posted to microsoft.public.excel.worksheet.functions
Sean Timmons
external usenet poster
 
Posts: 1,696
Default Counting cells only if cell X equals "yes"

so, you're saying you want to count if A19:A3703 equals A10 AND X3:X3703 =
"yes"?

=SUMPRODUCT(($A$19:$A$3703=A10)*($X$19:$X$3703="ye s"))

"Frustrated" wrote:

> I am tracking my sales reps sales. I currently have a formula to track that,
> but now i need to track their sales only if they add a certian feature to the
> sale. So now i need to find a formula that tracks their sales if they answer
> yes to cell x. If they answer no to cell X i do not want to count that
> sale. Can someone please help. The formula that tracks their individual
> sales is COUNTIF($A$19:$A$3703, A10)

  #5  
Old November 6th 09, 06:59 AM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 8,521
Default Counting cells only if cell X equals "yes"

When you have multiple critiera to be applied to return the count use one of
the below

---When you have multiple criteria use SUMPRODUCT()
'2 criterias
=SUMPRODUCT((A1:A10=criteria1)*(B1:B10=criteria2))
=SUMPRODUCT((A1:A10=F1)*(B1:B10=F2))

'3 criterias
=SUMPRODUCT((A1:A10=criteria1)*(B1:B10=criteria2)* (C1:C10=criteria3))
'Wtih cells F1,F2,F3 holding the criteria
=SUMPRODUCT((A1:A10=F1)*(B1:B10=F2)*(C1:C10=F3))

---In case you are using XL 2007 check out help on COUNTIFS()
=COUNTIFS( Criteriarange1,Criteria1,Criteriarange2,Criteria2, ...)


If this post helps click Yes
---------------
Jacob Skaria


"Frustrated" wrote:

> I am tracking my sales reps sales. I currently have a formula to track that,
> but now i need to track their sales only if they add a certian feature to the
> sale. So now i need to find a formula that tracks their sales if they answer
> yes to cell x. If they answer no to cell X i do not want to count that
> sale. Can someone please help. The formula that tracks their individual
> sales is COUNTIF($A$19:$A$3703, A10)

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Combine text from multiple cells into one cell - =(A1&","&A2","&A3 mh Excel Worksheet Functions 5 July 27th 09 02:40 AM
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell Steve Kay Excel Discussion (Misc queries) 2 August 8th 08 01:54 AM
if cell equals "a" then another cell would calculate percentage Brandy Excel Worksheet Functions 1 June 20th 08 05:18 PM
Sumif formula that uses "contains" rather than "equals" jerrymcm Excel Discussion (Misc queries) 4 October 2nd 07 05:15 PM
In excel counting cells in a range which meet condition ">Xand<X" Uncivil Servant Excel Worksheet Functions 1 May 19th 06 02:37 PM


All times are GMT +1. The time now is 11:09 AM.


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