Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 173
Default COUNTIF With Conditions

Hello,

Here is my current formula: =COUNTIF(F3:F200, H5)

What I would like it to do is to count the instances of H5 within that range
of F3:F100 as long as the corresponding Row in Column E is equal to or less
than 30. The range of Column E is obviously the same as F so E3:E200.

So something like COUNTIF(F3:F200, H5, IF(E3:E200<=30))

Thank you
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default COUNTIF With Conditions


=Sumproduct((E3:E200<=30)*(F3:F200=H5))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"RoadKill" wrote in message
...
Hello,

Here is my current formula: =COUNTIF(F3:F200, H5)

What I would like it to do is to count the instances of H5 within that
range
of F3:F100 as long as the corresponding Row in Column E is equal to or
less
than 30. The range of Column E is obviously the same as F so E3:E200.

So something like COUNTIF(F3:F200, H5, IF(E3:E200<=30))

Thank you



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default COUNTIF With Conditions

Maybe this

=SUMPRODUCT((F3:F200=H5)*(E3:E200<"")*(E3:E200<=3 0))

Mike

"RoadKill" wrote:

Hello,

Here is my current formula: =COUNTIF(F3:F200, H5)

What I would like it to do is to count the instances of H5 within that range
of F3:F100 as long as the corresponding Row in Column E is equal to or less
than 30. The range of Column E is obviously the same as F so E3:E200.

So something like COUNTIF(F3:F200, H5, IF(E3:E200<=30))

Thank you

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,722
Default COUNTIF With Conditions

=SUMPRODUCT((F3:F200=H5)*(E3:E200<=30))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"RoadKill" wrote:

Hello,

Here is my current formula: =COUNTIF(F3:F200, H5)

What I would like it to do is to count the instances of H5 within that range
of F3:F100 as long as the corresponding Row in Column E is equal to or less
than 30. The range of Column E is obviously the same as F so E3:E200.

So something like COUNTIF(F3:F200, H5, IF(E3:E200<=30))

Thank you

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 173
Default COUNTIF With Conditions

All work great.

If you can help me one more time though. How about if in E if the number
falls between 31 and 60. From there I should be able to do the next
incarnation of 61 and 90.

Thanks much

"RoadKill" wrote:

Hello,

Here is my current formula: =COUNTIF(F3:F200, H5)

What I would like it to do is to count the instances of H5 within that range
of F3:F100 as long as the corresponding Row in Column E is equal to or less
than 30. The range of Column E is obviously the same as F so E3:E200.

So something like COUNTIF(F3:F200, H5, IF(E3:E200<=30))

Thank you



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default COUNTIF With Conditions

One mo

=SUMPRODUCT(--(E3:E200<""),--(E3:E200<=30),--(F3:F200=H5))

--
Biff
Microsoft Excel MVP


"RoadKill" wrote in message
...
Hello,

Here is my current formula: =COUNTIF(F3:F200, H5)

What I would like it to do is to count the instances of H5 within that
range
of F3:F100 as long as the corresponding Row in Column E is equal to or
less
than 30. The range of Column E is obviously the same as F so E3:E200.

So something like COUNTIF(F3:F200, H5, IF(E3:E200<=30))

Thank you



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default COUNTIF With Conditions

Try this:

=SUMPRODUCT(--(E3:E200<""),--(E3:E200=31),--(E3:200<=60),--(F3:F200=H5))

Better if you use clls to hold the criteria:

A1 = 31
B1 = 60

=SUMPRODUCT(--(E3:E200<""),--(E3:E200=A1),--(E3:200<=B1),--(F3:F200=H5))


--
Biff
Microsoft Excel MVP


"RoadKill" wrote in message
...
All work great.

If you can help me one more time though. How about if in E if the number
falls between 31 and 60. From there I should be able to do the next
incarnation of 61 and 90.

Thanks much

"RoadKill" wrote:

Hello,

Here is my current formula: =COUNTIF(F3:F200, H5)

What I would like it to do is to count the instances of H5 within that
range
of F3:F100 as long as the corresponding Row in Column E is equal to or
less
than 30. The range of Column E is obviously the same as F so E3:E200.

So something like COUNTIF(F3:F200, H5, IF(E3:E200<=30))

Thank you



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
Countif Conditions - Use of conditions that vary by cell value JonTarg Excel Discussion (Misc queries) 1 May 30th 08 01:21 PM
Countif with 2 conditions DTTODGG Excel Worksheet Functions 7 June 5th 07 08:54 PM
COUNTIF with two conditions Jaydubs Excel Discussion (Misc queries) 3 July 19th 06 10:28 AM
Using countif with 2 conditions Cheryl W Excel Worksheet Functions 2 September 14th 05 03:38 PM
countif two conditions Penny Excel Discussion (Misc queries) 4 May 27th 05 01:59 AM


All times are GMT +1. The time now is 10:26 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"