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

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

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