Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 15th 10, 10:33 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2010
Posts: 5
Default COUNTIF, SUMPRODUCT, or something else

Sorry, I have another question. What about having 30 columns from where I
should count these yes- values with the same condition? It will be a long
formula. Is there anything else that might be shorter?

Thanks

"Per Jessen" wrote:

Hi

This should do it:

=SUMPRODUCT(--(C1:C11=1),--(D111="yes"))+SUMPRODUCT(--(C1:C11=1),--(E1:E11="yes"))

Regards,
Per

"Count-Adi" skrev i meddelelsen
...
I am trying to use COUNTIF function where the range might vary. For
instance,
I want to know how many yes I have in column D and E together, with the
condition that C will be only equal with 1.

C D E
1
1 Yes
1 Yes
1
2 Yes
2
2 Yes
3 Yes
3 Yes
3 Yes
3

{=COUNTIF(IF($C$1:$C$11=1,$D$1:$E$11,0),"yes”)} should be 2.

Thanks for your help.


  #2   Report Post  
Old January 17th 10, 03:37 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 3,718
Default COUNTIF, SUMPRODUCT, or something else

=SUMPRODUCT((C1:C18=1)*(D1:E18="Yes"))



"Count-Adi" wrote:

Sorry, I have another question. What about having 30 columns from where I
should count these yes- values with the same condition? It will be a long
formula. Is there anything else that might be shorter?

Thanks

"Per Jessen" wrote:

Hi

This should do it:

=SUMPRODUCT(--(C1:C11=1),--(D111="yes"))+SUMPRODUCT(--(C1:C11=1),--(E1:E11="yes"))

Regards,
Per

"Count-Adi" skrev i meddelelsen
...
I am trying to use COUNTIF function where the range might vary. For
instance,
I want to know how many yes I have in column D and E together, with the
condition that C will be only equal with 1.

C D E
1
1 Yes
1 Yes
1
2 Yes
2
2 Yes
3 Yes
3 Yes
3 Yes
3

{=COUNTIF(IF($C$1:$C$11=1,$D$1:$E$11,0),"yes”)} should be 2.

Thanks for your help.



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
SUMPRODUCT OR COUNTIF chrisk Excel Worksheet Functions 6 February 3rd 09 04:29 PM
COUNTIF/SUMPRODUCT?? Peta Excel Worksheet Functions 2 January 31st 09 09:08 PM
SumProduct & Countif? RayportingMonkey Excel Worksheet Functions 8 October 11th 07 07:50 PM
SUMPRODUCT/COUNTIF luvthavodka Excel Discussion (Misc queries) 12 June 17th 06 02:58 AM
countif, sumproduct mg New Users to Excel 7 July 1st 05 10:26 PM


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

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017