Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 32
Default Help with a formula

I have a formula setup to count all the entries between a certain date to
count the entries for a project within that date. What I need is to count
the entries that are not equal to the project name. Example in column D I
enter the project name could be Greenbrier, Paramount, Harris Ranch or other
names. I have a formula setup to counting Greenbrier, Paramount and Harris
Ranch separately now and what I need is a counts all of the other entries
that are not equal Greenbrier, Paramount or Harris Ranch.

=IF(AND($N4=AC$2,$N4<=AD$2),COUNTIF($D4,"Greenbri er"),"")
=IF(AND($N4=AC$2,$N4<=AD$2),COUNTIF($D4,"Paramoun t"),"")
=IF(AND($N4=AC$2,$N4<=AD$2),COUNTIF($D4,"Harris Ranch"),"")
Now I need one to count anything that is not these 3.
Thanks

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default Help with a formula

Try the below

=IF(AND($N4<"",$N4=AC$2,$N4<=AD$2),ABS(SUM(COUNT IF(C3,{"Greenbrier","Paramount","Harris Ranch"}))-1),"")

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


"Trev" wrote:

I have a formula setup to count all the entries between a certain date to
count the entries for a project within that date. What I need is to count
the entries that are not equal to the project name. Example in column D I
enter the project name could be Greenbrier, Paramount, Harris Ranch or other
names. I have a formula setup to counting Greenbrier, Paramount and Harris
Ranch separately now and what I need is a counts all of the other entries
that are not equal Greenbrier, Paramount or Harris Ranch.

=IF(AND($N4=AC$2,$N4<=AD$2),COUNTIF($D4,"Greenbri er"),"")
=IF(AND($N4=AC$2,$N4<=AD$2),COUNTIF($D4,"Paramoun t"),"")
=IF(AND($N4=AC$2,$N4<=AD$2),COUNTIF($D4,"Harris Ranch"),"")
Now I need one to count anything that is not these 3.
Thanks

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default Help with a formula

cell D4

=IF(AND($N4<"",$N4=AC$2,$N4<=AD$2),ABS(SUM(COUNT IF($D$4,{"Greenbrier","Paramount","Harris Ranch"}))-1),"")

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


"Trev" wrote:

I have a formula setup to count all the entries between a certain date to
count the entries for a project within that date. What I need is to count
the entries that are not equal to the project name. Example in column D I
enter the project name could be Greenbrier, Paramount, Harris Ranch or other
names. I have a formula setup to counting Greenbrier, Paramount and Harris
Ranch separately now and what I need is a counts all of the other entries
that are not equal Greenbrier, Paramount or Harris Ranch.

=IF(AND($N4=AC$2,$N4<=AD$2),COUNTIF($D4,"Greenbri er"),"")
=IF(AND($N4=AC$2,$N4<=AD$2),COUNTIF($D4,"Paramoun t"),"")
=IF(AND($N4=AC$2,$N4<=AD$2),COUNTIF($D4,"Harris Ranch"),"")
Now I need one to count anything that is not these 3.
Thanks

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default Help with a formula

Trev

You formula isn't doing whay you say it is. After evaluating the 2 dates
it's then only looking at a single cell D4. If you post a small sample of
data then I'm sure someong will give you a formula or are you trying to do
this

SUMPRODUCT((N4=AC2)*(N4<=AD2)*(D4:D100="Paramount "))

and to count cells that aren't any of the 3

=SUMPRODUCT((N4=AC2)*(N4<=AD2)*(D4:D100<"Paramou nt")*(D4:D100<"Greenbrier")*(D4:D100<"Harris Ranch")*(D4:D100<""))

Mike


"Trev" wrote:

I have a formula setup to count all the entries between a certain date to
count the entries for a project within that date. What I need is to count
the entries that are not equal to the project name. Example in column D I
enter the project name could be Greenbrier, Paramount, Harris Ranch or other
names. I have a formula setup to counting Greenbrier, Paramount and Harris
Ranch separately now and what I need is a counts all of the other entries
that are not equal Greenbrier, Paramount or Harris Ranch.

=IF(AND($N4=AC$2,$N4<=AD$2),COUNTIF($D4,"Greenbri er"),"")
=IF(AND($N4=AC$2,$N4<=AD$2),COUNTIF($D4,"Paramoun t"),"")
=IF(AND($N4=AC$2,$N4<=AD$2),COUNTIF($D4,"Harris Ranch"),"")
Now I need one to count anything that is not these 3.
Thanks

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



All times are GMT +1. The time now is 01:24 AM.

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"