Referencing the values returned by a formula
 

I would like to count unique values returned by an array after comparing it
with a list. I have an array set up to return the averages of several
students grades including two other conditions i.e "abs" if the student was
absent, "X"
if they left the class or school. here's the formula for that.. works fine

=IF(AND(G5="Abs",H5="Abs"),"ABS ",IF(AND(G5="X",H5="X"),"EXED
",IF(COUNT(G5:AF5)=0," ",(SUM(G5:AF5))/(COUNT(G5:AF5)))))

....and then another to determine if they are male or female students and sum
them up accordingly

=IF(COUNT(AG5:AG45)=0,"
",SUM(($D5:$D45="F")*(AG5:AG45=0),-($D5:$D45="F")*(AG5:AG45="EXED")))

where F = female & EXED= students who no longer belong to the class. This
works fine only if the 1st formula returns number values i.e percentages, but
does not when it returns text i.e the word EXED.

Help. I need to count only the male/female syudents who were absent or have
percentages.

Referencing the values returned by a formula
 

Hi
if you want to count only female AND EXED, try this

=SUMPRODUCT(--($D5:$D45="F"),--(AG5:AG45="EXED"))

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"StevieSayang" wrote:

I would like to count unique values returned by an array after comparing it
with a list. I have an array set up to return the averages of several
students grades including two other conditions i.e "abs" if the student was
absent, "X"
if they left the class or school. here's the formula for that.. works fine

=IF(AND(G5="Abs",H5="Abs"),"ABS ",IF(AND(G5="X",H5="X"),"EXED
",IF(COUNT(G5:AF5)=0," ",(SUM(G5:AF5))/(COUNT(G5:AF5)))))

...and then another to determine if they are male or female students and sum
them up accordingly

=IF(COUNT(AG5:AG45)=0,"
",SUM(($D5:$D45="F")*(AG5:AG45=0),-($D5:$D45="F")*(AG5:AG45="EXED")))

where F = female & EXED= students who no longer belong to the class. This
works fine only if the 1st formula returns number values i.e percentages, but
does not when it returns text i.e the word EXED.

Help. I need to count only the male/female syudents who were absent or have
percentages.

Referencing the values returned by a formula
 

Thanks Francis but it did not help. What i really need is a formula to count
the females with grades plus those who were absent, but not the females who
left the class, i.e those for whom the formula in AG5:AG45="EXED"

"Francis" wrote:

Hi
if you want to count only female AND EXED, try this

=SUMPRODUCT(--($D5:$D45="F"),--(AG5:AG45="EXED"))

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"StevieSayang" wrote:

I would like to count unique values returned by an array after comparing it
with a list. I have an array set up to return the averages of several
students grades including two other conditions i.e "abs" if the student was
absent, "X"
if they left the class or school. here's the formula for that.. works fine

=IF(AND(G5="Abs",H5="Abs"),"ABS ",IF(AND(G5="X",H5="X"),"EXED
",IF(COUNT(G5:AF5)=0," ",(SUM(G5:AF5))/(COUNT(G5:AF5)))))

...and then another to determine if they are male or female students and sum
them up accordingly

=IF(COUNT(AG5:AG45)=0,"
",SUM(($D5:$D45="F")*(AG5:AG45=0),-($D5:$D45="F")*(AG5:AG45="EXED")))

where F = female & EXED= students who no longer belong to the class. This
works fine only if the 1st formula returns number values i.e percentages, but
does not when it returns text i.e the word EXED.

Help. I need to count only the male/female syudents who were absent or have
percentages.

Referencing the values returned by a formula
 

would you lay out a sample?
This will help to understand where is your data reside
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"StevieSayang" wrote:

Thanks Francis but it did not help. What i really need is a formula to count
the females with grades plus those who were absent, but not the females who
left the class, i.e those for whom the formula in AG5:AG45="EXED"

"Francis" wrote:

Hi
if you want to count only female AND EXED, try this

=SUMPRODUCT(--($D5:$D45="F"),--(AG5:AG45="EXED"))

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"StevieSayang" wrote:

I would like to count unique values returned by an array after comparing it
with a list. I have an array set up to return the averages of several
students grades including two other conditions i.e "abs" if the student was
absent, "X"
if they left the class or school. here's the formula for that.. works fine

=IF(AND(G5="Abs",H5="Abs"),"ABS ",IF(AND(G5="X",H5="X"),"EXED
",IF(COUNT(G5:AF5)=0," ",(SUM(G5:AF5))/(COUNT(G5:AF5)))))

...and then another to determine if they are male or female students and sum
them up accordingly

=IF(COUNT(AG5:AG45)=0,"
",SUM(($D5:$D45="F")*(AG5:AG45=0),-($D5:$D45="F")*(AG5:AG45="EXED")))

where F = female & EXED= students who no longer belong to the class. This
works fine only if the 1st formula returns number values i.e percentages, but
does not when it returns text i.e the word EXED.

Help. I need to count only the male/female syudents who were absent or have
percentages.