COUNT and SUM

[Mrs T.]

Hi, can you count and sum at the same time? I want to count how many boys
('m' in column B) have a test score (1+ in column O) then total how much
progress they have made since the last test (column P). I would also like to
count how many more able children (any text in column E) have a test score
(in column O) and then total their progress since the last test (column P).
My column O returns a #N/A value if no score is entered.
Thanks
Mrs T

 

[JoeU2004]

 

[Ashish Mathur]

Hi,

Please paste the data here and explain exaclt what you want

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Mrs T.]

Hi, my data looks like this

B D O P

m/f ma score progress
m y 6 0
f 4 1
m p 6 -1
f mat 7 2
m 6 0
f 7 1

folrmula which brings over score is
=IF($N22>0,LOOKUP($N22,AR$20:$AR$62,AS$20:AS$62),"")

Hope you can help - I almost have the sheet working!
Thanks
Mrs T

 

[Mrs T.]

Sorry - I don't think I explained that very well. Data looks like this:

B D O P

m/f ma score progress
m y 6 0
f 4 1
m p 6 -1
f mat 7 2
m 6 0
f 7 1

folrmula which brings over score is
=IF($N22>0,LOOKUP($N22,AR$20:$AR$62,AS$20:AS$62),"")

I am trying to count how many boys have a test score and then sum the
progress for that group. Also to find out how many more able children have a
score and sum the progress for that group. I have got the formula to count
the boys and more able who have test score
=SUMPRODUCT(--(D21153<>""),--(ISNUMBER(O21:O153))) but can't work out how
to sum the progress for them.
Mrs T

 

[Ashish Mathur]

Hi,

This will sum up the "Progress" column

=SUMPRODUCT((D21153<>"")*(ISNUMBER(O21:O153)),P21153)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com