Troubleshooting: CSE formulas with multiple conditions

[KatK6]

Hi,

Need some help fixing a CSE formula with multiple nested conditions.

Formula is:
{=COUNT(IF(AND($Y$2:$Y$37263<1,$D$2:$D$37263=AC18),$V$2:$V$37263))}
Just returns a value of 1 when it should return something in the order of 3000.

What I want it to do is count the number of rows in the range
where the value of the Y cell is less than 1 AND the number in D is the same as AC.

Thanks,

 

[Jonmo1]

You can't use AND in an array formula like that.
Also, what is the purpose of V2:V37263 ?

Try
=SUMPRODUCT(--($Y$2:$Y$37263<1),--($D$2:$D$37263=AC18))

Or if you have XL2007+ you can use COUNTIFS
=COUNTIFS($Y$2:$Y$37263,"<1",$D$2:$D$37263,AC18)

 

[KatK6]

What is the purpose of V2:V37263 ?

V2:V37263 is the range I want to count the number of cells that meet the other two criteria.

Thanks for the CountIF suggestion.

I also need to calculate average, median, quartile & st deviation figures is there a way to correct the use of AND in my original formula?

 

[Jonmo1]

V2:V37263 is the range I want to count the number of cells that meet the other two criteria.

I don't understand, Column V actually doesn't meet the criteria, as the criteria are in columns Y and D..
Do you mean that there are rows where Y < 1 and D = AC18 but V is empty? And you would NOT want that row counted?
What type of value is in column V that you want counted? Number or Text ?
Is it sufficient to just say if
V is NOT Blank AND
Y < 1 And
D = AC18

 

[Jonmo1]

If that's what you mean by including column V, that you want to count where V is not blank plus the other 2 criteria, your original formula should be changed to

=COUNT(IF(($Y$2:$Y$37263<1)*($D$2:$D$37263=AC18)*($V$2:$V$37263<>""),1,""))

Entered with CTRL + SHIFT + ENTER

 

[KatK6]

V contains the data I want to count, average etc. IF it meets both the criteria.

V contains numbers between 0 and 1000.
Y contains 0s and 1s depending on whether V is an outlier

I want to do something along the lines of:
{=AVERAGE(IF(AND($Y$2:$Y$37263<1,$D$2:$D$37263=AC18),$V$2:$V$37263))}

The count function just counts the sample size for the average function.

 

[Jonmo1]

Try
=AVERAGE(IF(($Y$2:$Y$37263<1)*($D$2:$D$37263=AC18),$V$2:$V$37263))
Enterd with CTRL + SHIFT + ENTER