Remember Me?

 Andy Posts: n/a 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
--

Best Wishes

Andy

 Domenic Posts: n/a 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 Posts: n/a 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

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post HiTekDiver Excel Discussion (Misc queries) 3 August 29th 05 11:32 PM maxtrixx Excel Discussion (Misc queries) 5 March 31st 05 06:21 PM Heather Murch Excel Worksheet Functions 1 February 17th 05 03:47 PM Pete Petersen Excel Worksheet Functions 2 January 4th 05 03:47 PM maswinney Excel Worksheet Functions 3 November 15th 04 11:06 PM

All times are GMT +1. The time now is 05:25 AM. Copyright ©2004-2021 ExcelBanter.