Sumif/s maybe! + percentage

[KClem]

Hi

I need to do two things in the attached spreadsheet.

1. On the 'reading' sheet in cell b5 I need to insert the % of kids from column 'A' (PP-FSM) on sheet 'class information' who have achieved the mark 1a, 2c, 2b or 2a (on reading sheet) as a proportion of the total number of children (cell b4).
2. I need to do the reverse of point 1, i need to show all the children not in column 'A' (PP-FSM) on sheet 'class information'.

Thanks for any help that you may be able to give.

[KClem]

Hi

I need to do two things in the attached spreadsheet.

1. In cell b5 'Reading' I need to insert the % of kids from column 'A' 'class information' who have achieved the mark 1a, 2c, 2b & 2a (on reading sheet) as a % of cell b4.

AND

2. Same as point 1, but i need to show % of kids not in column 'A' 'class information'.

Thanks for any help that you may be able to give.

[ELeGault]

This what you are going for ?

Get the sum of anyone that has one of the 4 you listed (21) divided by the count of total found in B4

=(COUNTIFS(Table1[Mark],"1a",Table1[End of Year 1 Target],"<>"&"")+COUNTIFS(Table1[Mark],"2a",Table1[End of Year 1 Target],"<>"&"")+COUNTIFS(Table1[Mark],"2b",Table1[End of Year 1 Target],"<>"&"")+COUNTIFS(Table1[Mark],"2c",Table1[End of Year 1 Target],"<>"&""))/B4

And then the inverse is just 1 - the above or even subtract the end result as it would be the inverse

1-B5

EDIT - The format of the cells may need to change to Percentage instead of the custom format that you currently have set in B5 and B6 however that may be due to region settings in which case it may work as is for you.

[SDCh]

May be like this:
on cell B5

=SUMPRODUCT(COUNTIFS(Table1[End of Year 1 Target],'Class Information'!A2:A12,Table1[Mark],{"1a","2a","2b","2c"}))/B4

on cell b6

=(B4-SUMPRODUCT(COUNTIFS(Table1[End of Year 1 Target],'Class Information'!A2:A12,Table1[Mark],{"1a","2a","2b","2c"})))/31

Note:
- as EleGault mention, change your cell format into percentage

- on cell b6 you can use EleGault formula too

[Fotis1991]

Welcome to the Forum, unfortunately:

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. As you have replies in both of your threads just for this time, i'll merge your 2 threads.

Pls take some minutes to read Forum Rules.

[pjwhitfield]

In B5 your formula should be:

=(COUNTA('Class Information'!A:A)-1)/B4

however I dont understand what you're expecting from point 2? are you looking for a list of kids names? a count etc?

[Fotis1991]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[KClem]

I can't get either formula to work, neither:-
=SUMPRODUCT(COUNTIFS(Table1[End of Year 1 Target],'Class Information'!A2:A12,Table1[Mark],{"1a","2a","2b","2c"}))/B4

=(COUNTIFS(Table1[Mark],"1a",Table1[End of Year 1 Target],"<>"&"")+COUNTIFS(Table1[Mark],"2a",Table1[End of Year 1 Target],"<>"&"")+COUNTIFS(Table1[Mark],"2b",Table1[End of Year 1 Target],"<>"&"")+COUNTIFS(Table1[Mark],"2c",Table1[End of Year 1 Target],"<>"&""))/B4

This one although useful, only gives me a % of the number of names in column A. Whereas what I need to show in B5 is the % of the names in Column A 'class information' that have achieved either 1a or above.
=(COUNTA('Class Information'!A:A)-1)/B4