COUNT and SUM (please help!)
 

Hi, the data in my columns is:

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 (column O) is
=IF($N220,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 who have test score and the more able who have a test score
=SUMPRODUCT(--(B21:B153<""),--(ISNUMBER(O21:O153))) but can't work out how
to sum the progress for them. I have spent ages trying to adapt formulae to
work!
Thanks
Mrs T

COUNT and SUM (please help!)
 

Hi,

From your sample data what result would you expect to get for the sum of the
progress?

Mike

"Mrs T." wrote:

Hi, the data in my columns is:

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 (column O) is
=IF($N220,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 who have test score and the more able who have a test score
=SUMPRODUCT(--(B21:B153<""),--(ISNUMBER(O21:O153))) but can't work out how
to sum the progress for them. I have spent ages trying to adapt formulae to
work!
Thanks
Mrs T

COUNT and SUM (please help!)
 

Hello Mrs T.

Try adding another column to the sumproduct with (p21:p153) and it may work.

--
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK
Please do not forget to rate this reply.


"Mrs T." wrote:

Hi, the data in my columns is:

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 (column O) is
=IF($N220,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 who have test score and the more able who have a test score
=SUMPRODUCT(--(B21:B153<""),--(ISNUMBER(O21:O153))) but can't work out how
to sum the progress for them. I have spent ages trying to adapt formulae to
work!
Thanks
Mrs T

COUNT and SUM (please help!)
 

Hi - I have tried adding (p21:p153) but it doesn't work. I would expect to
get a total of -1 for the total progress for boys who have a test score and a
total of 1 for the total progres for more able children who have a test score.
Thanks very much for your help
Mrs T

"Martin Fishlock" wrote:

Hello Mrs T.

Try adding another column to the sumproduct with (p21:p153) and it may work.

--
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK
Please do not forget to rate this reply.


"Mrs T." wrote:

Hi, the data in my columns is:

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 (column O) is
=IF($N220,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 who have test score and the more able who have a test score
=SUMPRODUCT(--(B21:B153<""),--(ISNUMBER(O21:O153))) but can't work out how
to sum the progress for them. I have spent ages trying to adapt formulae to
work!
Thanks
Mrs T

COUNT and SUM (please help!)
 

Hi Mrs T,

I think you want this

=SUMPRODUCT((B21:B153="m")*(O21:O153<"")*(P21:P15 3))


suitably adapted for the ladies of your class.

Mike

"Mrs T." wrote:

Hi - I have tried adding (p21:p153) but it doesn't work. I would expect to
get a total of -1 for the total progress for boys who have a test score and a
total of 1 for the total progres for more able children who have a test score.
Thanks very much for your help
Mrs T

"Martin Fishlock" wrote:

Hello Mrs T.

Try adding another column to the sumproduct with (p21:p153) and it may work.

--
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK
Please do not forget to rate this reply.


"Mrs T." wrote:

Hi, the data in my columns is:

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 (column O) is
=IF($N220,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 who have test score and the more able who have a test score
=SUMPRODUCT(--(B21:B153<""),--(ISNUMBER(O21:O153))) but can't work out how
to sum the progress for them. I have spent ages trying to adapt formulae to
work!
Thanks
Mrs T

COUNT and SUM (please help!)
 

Hi,

Looking at this again I don't understand the significance of Column D

Mike

"Mike H" wrote:

Hi Mrs T,

I think you want this

=SUMPRODUCT((B21:B153="m")*(O21:O153<"")*(P21:P15 3))


suitably adapted for the ladies of your class.

Mike

"Mrs T." wrote:

Hi - I have tried adding (p21:p153) but it doesn't work. I would expect to
get a total of -1 for the total progress for boys who have a test score and a
total of 1 for the total progres for more able children who have a test score.
Thanks very much for your help
Mrs T

"Martin Fishlock" wrote:

Hello Mrs T.

Try adding another column to the sumproduct with (p21:p153) and it may work.

--
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK
Please do not forget to rate this reply.


"Mrs T." wrote:

Hi, the data in my columns is:

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 (column O) is
=IF($N220,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 who have test score and the more able who have a test score
=SUMPRODUCT(--(B21:B153<""),--(ISNUMBER(O21:O153))) but can't work out how
to sum the progress for them. I have spent ages trying to adapt formulae to
work!
Thanks
Mrs T

COUNT and SUM (please help!)
 

Hi - thanks for your time. I get #N/A when I use the formula you suggested.
Is that because my column O returns #N/A value if no score is entered (the
score comes from a lookup table).
Column D identifies children who are more able - I would like to count how
many of them have a test score and sum their progress as well. Input into
column D can be any text.
Thanks again
Mrs T
"Mike H" wrote:

Hi Mrs T,

I think you want this

=SUMPRODUCT((B21:B153="m")*(O21:O153<"")*(P21:P15 3))


suitably adapted for the ladies of your class.

Mike

"Mrs T." wrote:

Hi - I have tried adding (p21:p153) but it doesn't work. I would expect to
get a total of -1 for the total progress for boys who have a test score and a
total of 1 for the total progres for more able children who have a test score.
Thanks very much for your help
Mrs T

"Martin Fishlock" wrote:

Hello Mrs T.

Try adding another column to the sumproduct with (p21:p153) and it may work.

--
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK
Please do not forget to rate this reply.


"Mrs T." wrote:

Hi, the data in my columns is:

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 (column O) is
=IF($N220,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 who have test score and the more able who have a test score
=SUMPRODUCT(--(B21:B153<""),--(ISNUMBER(O21:O153))) but can't work out how
to sum the progress for them. I have spent ages trying to adapt formulae to
work!
Thanks
Mrs T

COUNT and SUM (please help!)
 

Hi again,

Similar to yesterday were into arrays again. Try this which assumes you
don't get any NA's in column P

=SUM(IF(B21:B153="m",IF(ISNUMBER(O21:O153),P21:P15 3)))

and this array formula for 'More able' males

=SUM(IF(B21:B153="m",IF(D21:D153<"",IF(ISNUMBER(O 21:O153),P21:P153))))

Mike


"Mrs T." wrote:


Hi - thanks for your time. I get #N/A when I use the formula you suggested.
Is that because my column O returns #N/A value if no score is entered (the
score comes from a lookup table).
Column D identifies children who are more able - I would like to count how
many of them have a test score and sum their progress as well. Input into
column D can be any text.
Thanks again
Mrs T
"Mike H" wrote:

Hi Mrs T,

I think you want this

=SUMPRODUCT((B21:B153="m")*(O21:O153<"")*(P21:P15 3))


suitably adapted for the ladies of your class.

Mike

"Mrs T." wrote:

Hi - I have tried adding (p21:p153) but it doesn't work. I would expect to
get a total of -1 for the total progress for boys who have a test score and a
total of 1 for the total progres for more able children who have a test score.
Thanks very much for your help
Mrs T

"Martin Fishlock" wrote:

Hello Mrs T.

Try adding another column to the sumproduct with (p21:p153) and it may work.

--
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK
Please do not forget to rate this reply.


"Mrs T." wrote:

Hi, the data in my columns is:

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 (column O) is
=IF($N220,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 who have test score and the more able who have a test score
=SUMPRODUCT(--(B21:B153<""),--(ISNUMBER(O21:O153))) but can't work out how
to sum the progress for them. I have spent ages trying to adapt formulae to
work!
Thanks
Mrs T

COUNT and SUM (please help!)
 

Hi Mike,
Thank you so much. Guess it's time for me to go on an Excel course so I can
do what I want without hassling you lot! I've spent hours trying to get that
to work and you do it in seconds!
Thanks again - you deserve a medal.
Mrs T :)

"Mike H" wrote:

Hi again,

Similar to yesterday were into arrays again. Try this which assumes you
don't get any NA's in column P

=SUM(IF(B21:B153="m",IF(ISNUMBER(O21:O153),P21:P15 3)))

and this array formula for 'More able' males

=SUM(IF(B21:B153="m",IF(D21:D153<"",IF(ISNUMBER(O 21:O153),P21:P153))))

Mike


"Mrs T." wrote:


Hi - thanks for your time. I get #N/A when I use the formula you suggested.
Is that because my column O returns #N/A value if no score is entered (the
score comes from a lookup table).
Column D identifies children who are more able - I would like to count how
many of them have a test score and sum their progress as well. Input into
column D can be any text.
Thanks again
Mrs T
"Mike H" wrote:

Hi Mrs T,

I think you want this

=SUMPRODUCT((B21:B153="m")*(O21:O153<"")*(P21:P15 3))


suitably adapted for the ladies of your class.

Mike

"Mrs T." wrote:

Hi - I have tried adding (p21:p153) but it doesn't work. I would expect to
get a total of -1 for the total progress for boys who have a test score and a
total of 1 for the total progres for more able children who have a test score.
Thanks very much for your help
Mrs T

"Martin Fishlock" wrote:

Hello Mrs T.

Try adding another column to the sumproduct with (p21:p153) and it may work.

--
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK
Please do not forget to rate this reply.


"Mrs T." wrote:

Hi, the data in my columns is:

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 (column O) is
=IF($N220,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 who have test score and the more able who have a test score
=SUMPRODUCT(--(B21:B153<""),--(ISNUMBER(O21:O153))) but can't work out how
to sum the progress for them. I have spent ages trying to adapt formulae to
work!
Thanks
Mrs T

COUNT and SUM (please help!)
 

Glad I could help

"Mrs T." wrote:

Hi Mike,
Thank you so much. Guess it's time for me to go on an Excel course so I can
do what I want without hassling you lot! I've spent hours trying to get that
to work and you do it in seconds!
Thanks again - you deserve a medal.
Mrs T :)

"Mike H" wrote:

Hi again,

Similar to yesterday were into arrays again. Try this which assumes you
don't get any NA's in column P

=SUM(IF(B21:B153="m",IF(ISNUMBER(O21:O153),P21:P15 3)))

and this array formula for 'More able' males

=SUM(IF(B21:B153="m",IF(D21:D153<"",IF(ISNUMBER(O 21:O153),P21:P153))))

Mike


"Mrs T." wrote:


Hi - thanks for your time. I get #N/A when I use the formula you suggested.
Is that because my column O returns #N/A value if no score is entered (the
score comes from a lookup table).
Column D identifies children who are more able - I would like to count how
many of them have a test score and sum their progress as well. Input into
column D can be any text.
Thanks again
Mrs T
"Mike H" wrote:

Hi Mrs T,

I think you want this

=SUMPRODUCT((B21:B153="m")*(O21:O153<"")*(P21:P15 3))


suitably adapted for the ladies of your class.

Mike

"Mrs T." wrote:

Hi - I have tried adding (p21:p153) but it doesn't work. I would expect to
get a total of -1 for the total progress for boys who have a test score and a
total of 1 for the total progres for more able children who have a test score.
Thanks very much for your help
Mrs T

"Martin Fishlock" wrote:

Hello Mrs T.

Try adding another column to the sumproduct with (p21:p153) and it may work.

--
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK
Please do not forget to rate this reply.


"Mrs T." wrote:

Hi, the data in my columns is:

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 (column O) is
=IF($N220,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 who have test score and the more able who have a test score
=SUMPRODUCT(--(B21:B153<""),--(ISNUMBER(O21:O153))) but can't work out how
to sum the progress for them. I have spent ages trying to adapt formulae to
work!
Thanks
Mrs T

COUNT and SUM (please help!)
 

Sometimes you may actually want the #N/A errors to generate if you're using
that specific data as a chart source. However, if you're not charting that
data you could make your life much easier if you change that formula so that
it doesn't return the #N/A. error. You could have it return the TEXT string
N/A and that will solve having to deal with the errors.

--
Biff
Microsoft Excel MVP


"Mrs T." wrote in message
...
Hi Mike,
Thank you so much. Guess it's time for me to go on an Excel course so I
can
do what I want without hassling you lot! I've spent hours trying to get
that
to work and you do it in seconds!
Thanks again - you deserve a medal.
Mrs T :)

"Mike H" wrote:

Hi again,

Similar to yesterday were into arrays again. Try this which assumes you
don't get any NA's in column P

=SUM(IF(B21:B153="m",IF(ISNUMBER(O21:O153),P21:P15 3)))

and this array formula for 'More able' males

=SUM(IF(B21:B153="m",IF(D21:D153<"",IF(ISNUMBER(O 21:O153),P21:P153))))

Mike


"Mrs T." wrote:


Hi - thanks for your time. I get #N/A when I use the formula you
suggested.
Is that because my column O returns #N/A value if no score is entered
(the
score comes from a lookup table).
Column D identifies children who are more able - I would like to count
how
many of them have a test score and sum their progress as well. Input
into
column D can be any text.
Thanks again
Mrs T
"Mike H" wrote:

Hi Mrs T,

I think you want this

=SUMPRODUCT((B21:B153="m")*(O21:O153<"")*(P21:P15 3))


suitably adapted for the ladies of your class.

Mike

"Mrs T." wrote:

Hi - I have tried adding (p21:p153) but it doesn't work. I would
expect to
get a total of -1 for the total progress for boys who have a test
score and a
total of 1 for the total progres for more able children who have a
test score.
Thanks very much for your help
Mrs T

"Martin Fishlock" wrote:

Hello Mrs T.

Try adding another column to the sumproduct with (p21:p153) and
it may work.

--
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK
Please do not forget to rate this reply.


"Mrs T." wrote:

Hi, the data in my columns is:

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 (column O) is
=IF($N220,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 who have test score and the more able who have a test
score
=SUMPRODUCT(--(B21:B153<""),--(ISNUMBER(O21:O153))) but can't
work out how
to sum the progress for them. I have spent ages trying to adapt
formulae to
work!
Thanks
Mrs T

COUNT and SUM (please help!)
 

Thanks - I was just over the moon to get it to work and didn't realise what a
problem the #N/A values would be later on! Quite a learning curve I've had
doing this and just shown me how much I don't know.
Mrs T

"T. Valko" wrote:

Sometimes you may actually want the #N/A errors to generate if you're using
that specific data as a chart source. However, if you're not charting that
data you could make your life much easier if you change that formula so that
it doesn't return the #N/A. error. You could have it return the TEXT string
N/A and that will solve having to deal with the errors.

--
Biff
Microsoft Excel MVP


"Mrs T." wrote in message
...
Hi Mike,
Thank you so much. Guess it's time for me to go on an Excel course so I
can
do what I want without hassling you lot! I've spent hours trying to get
that
to work and you do it in seconds!
Thanks again - you deserve a medal.
Mrs T :)

"Mike H" wrote:

Hi again,

Similar to yesterday were into arrays again. Try this which assumes you
don't get any NA's in column P

=SUM(IF(B21:B153="m",IF(ISNUMBER(O21:O153),P21:P15 3)))

and this array formula for 'More able' males

=SUM(IF(B21:B153="m",IF(D21:D153<"",IF(ISNUMBER(O 21:O153),P21:P153))))

Mike


"Mrs T." wrote:


Hi - thanks for your time. I get #N/A when I use the formula you
suggested.
Is that because my column O returns #N/A value if no score is entered
(the
score comes from a lookup table).
Column D identifies children who are more able - I would like to count
how
many of them have a test score and sum their progress as well. Input
into
column D can be any text.
Thanks again
Mrs T
"Mike H" wrote:

Hi Mrs T,

I think you want this

=SUMPRODUCT((B21:B153="m")*(O21:O153<"")*(P21:P15 3))


suitably adapted for the ladies of your class.

Mike

"Mrs T." wrote:

Hi - I have tried adding (p21:p153) but it doesn't work. I would
expect to
get a total of -1 for the total progress for boys who have a test
score and a
total of 1 for the total progres for more able children who have a
test score.
Thanks very much for your help
Mrs T

"Martin Fishlock" wrote:

Hello Mrs T.

Try adding another column to the sumproduct with (p21:p153) and
it may work.

--
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK
Please do not forget to rate this reply.


"Mrs T." wrote:

Hi, the data in my columns is:

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 (column O) is
=IF($N220,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 who have test score and the more able who have a test
score
=SUMPRODUCT(--(B21:B153<""),--(ISNUMBER(O21:O153))) but can't
work out how
to sum the progress for them. I have spent ages trying to adapt
formulae to
work!
Thanks
Mrs T