Counting values in non adjacent cells

[MrsNic]

I have a spreadsheet that shows Effort and Achievement grades for a group of students

Effort goes from A - D Achievement goes from A* - U

the spreadsheet is set out ART - ACH - BIO - ACH

I want to be able to count separately the number of grades each pupil has for their effort or achivement for a range of subjects.

I have tried =CountIf(C2,E2,G2,"A")but it doesn't like the single cells, I have also tried =CountIf(C2:C2,E2:E2,G2:G2,"A")but that wasn't correct either.

Any ideas gratefully accepted.

Thanks

Cath

 

[RivetHed]

Let me just check I understand this first.

Col C holds Art Effort grades, Col D holds Art Achivement grades, Col E holds ACh Effort grades etc.

Now what value do you want to display?

 

[MrsNic]

Yes each subject has two columns one for effort one for achievement. The Effort column has the subject as a heading. e.g. ART ART-ACH BIO BIO-ACH ect.

I want to be able to add up the number of A's a pupil has for effort. The problem is there may also be A's for Achievement in the same row which should not be counted.

I want the sum to be in a numeric value e.g 6 in the A's column 3 in the B's etc.

Thanks for taking the time to help.

 

[SkipVought]

Hi,

"the spreadsheet is set out ART - ACH - BIO - ACH"

Your setup makes it DIFFICULT to analyze. It is a typical mistake that spreadsheet users make: assembling their data the way that they envision it (a report) rather than the way that Excel expects data to be in (a table).
[tt]
Grades
Student - Subject - Category - Grade
[/tt]
where the Subject would be any of ART, ACT, BIO, ACH and the Category would be either of Effort, Achievement.

Then SUMPRODUCT would work just fine.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[GlennUK]

I have to agree with Skip about the layout ... it's not been assembled for analysis, but for looks.

I know I had the same problems when I had to work with reports downloaded from mainframes, but that's another story.

Anyway, to get what you want, try:

=SUMPRODUCT((MOD(COLUMN(C:V),2))*(C2:V2="A"))

( it only counts for columns that are odd numbers ... C is column 3, etc etc )
[/code]


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[MrsNic]

Thanks very much for your help, the layout was just a quick fix as out database that usually works it out has decided not to play ball.

As with everything in a school it had to be done yesterday!

Many thanks
Cath