Home 
Search 
Today's Posts 
#1




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




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




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 
Display Modes  


Similar Threads  
Thread  Forum  
How do I set up an argument which asks for multiple criteria  Excel Discussion (Misc queries)  
Combining IF and COUNTIF based on two columns  Excel Discussion (Misc queries)  
want to count in two columns; countif (colA=x AND colB=y)?  Excel Worksheet Functions  
countif number of occurences per month per year.  Excel Worksheet Functions  
Countif  Countif  Excel Worksheet Functions 