ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   countif argument for 3 occurences of which 1 refers to a range (https://www.excelbanter.com/excel-discussion-misc-queries/44185-countif-argument-3-occurences-1-refers-range.html)

Andy

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

Domenic

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


Andy

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




All times are GMT +1. The time now is 12:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com