Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 7th 05, 11:35 AM
Andy
 
Posts: n/a
Default countif argument for 3 occurences of which 1 refers to a range

If - anyone can help please

I need to count rows based on 3 criteria

The first two arguments refer to specific cells but the third refers to a
range and it is here where "the solution" is falling down

The formula =SUM(IF($I$3:$I$50=N$4,IF($F$3:$F$50=$M24,1,0))) is fine to
produce the first 2 true arguments

For the third, in isolation =OR(COUNTIF(J3:J50, M5:M12)) produces true

Now, simply creating
=SUM(AND(IF($I$3:$I$50=N$4,IF($F$3:$F$50=$M24,1,0) ,IF(OR(J3:J50=(M5:M12)),1,0)))) produces #n/a

For arguments 1 and 2 the question is - does it equal cell x or not

For argument 3 the question is - does the cell equal one in a range

When all 3 are true, the formula should count 1
--
Thank you very much for your advice

Best Wishes

Andy

  #2   Report Post  
Old September 7th 05, 01:21 PM
Domenic
 
Posts: n/a
Default

Not sure if this is what you're looking for, but try the following...

=SUMPRODUCT(--($I$3:$I$50=N$4),--($F$3:$F$50=$M24),--(COUNTIF($M$5:$M$12,
$J$3:$J$50)0))

....confirmed with just ENTER, or alternatively...

=SUM(IF(($I$3:$I$50=N$4)*($F$3:$F$50=$M24)*COUNTIF ($M$5:$M$12,$J$3:$J$50)
,1))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
"Andy" wrote:

If - anyone can help please

I need to count rows based on 3 criteria

The first two arguments refer to specific cells but the third refers to a
range and it is here where "the solution" is falling down

The formula =SUM(IF($I$3:$I$50=N$4,IF($F$3:$F$50=$M24,1,0))) is fine to
produce the first 2 true arguments

For the third, in isolation =OR(COUNTIF(J3:J50, M5:M12)) produces true

Now, simply creating
=SUM(AND(IF($I$3:$I$50=N$4,IF($F$3:$F$50=$M24,1,0) ,IF(OR(J3:J50=(M5:M12)),1,0)
))) produces #n/a

For arguments 1 and 2 the question is - does it equal cell x or not

For argument 3 the question is - does the cell equal one in a range

When all 3 are true, the formula should count 1

  #3   Report Post  
Old September 7th 05, 03:19 PM
Andy
 
Posts: n/a
Default

Excellent work Domenic

Using both solutions which are - functioning - perfectly

I must swot up on Sumproduct, I don't yet understand the (-- but will get
there
--
Very Best Wishes and Good Luck!

Andy


"Domenic" wrote:

Not sure if this is what you're looking for, but try the following...

=SUMPRODUCT(--($I$3:$I$50=N$4),--($F$3:$F$50=$M24),--(COUNTIF($M$5:$M$12,
$J$3:$J$50)0))

....confirmed with just ENTER, or alternatively...

=SUM(IF(($I$3:$I$50=N$4)*($F$3:$F$50=$M24)*COUNTIF ($M$5:$M$12,$J$3:$J$50)
,1))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
"Andy" wrote:

If - anyone can help please

I need to count rows based on 3 criteria

The first two arguments refer to specific cells but the third refers to a
range and it is here where "the solution" is falling down

The formula =SUM(IF($I$3:$I$50=N$4,IF($F$3:$F$50=$M24,1,0))) is fine to
produce the first 2 true arguments

For the third, in isolation =OR(COUNTIF(J3:J50, M5:M12)) produces true

Now, simply creating
=SUM(AND(IF($I$3:$I$50=N$4,IF($F$3:$F$50=$M24,1,0) ,IF(OR(J3:J50=(M5:M12)),1,0)
))) produces #n/a

For arguments 1 and 2 the question is - does it equal cell x or not

For argument 3 the question is - does the cell equal one in a range

When all 3 are true, the formula should count 1




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
How do I set up an argument which asks for multiple criteria HiTekDiver Excel Discussion (Misc queries) 3 August 29th 05 11:32 PM
Combining IF and COUNTIF based on two columns maxtrixx Excel Discussion (Misc queries) 5 March 31st 05 06:21 PM
want to count in two columns; countif (colA=x AND colB=y)? Heather Murch Excel Worksheet Functions 1 February 17th 05 03:47 PM
countif number of occurences per month per year. Pete Petersen Excel Worksheet Functions 2 January 4th 05 03:47 PM
Countif - Countif maswinney Excel Worksheet Functions 3 November 15th 04 11:06 PM


All times are GMT +1. The time now is 05:25 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017