Merging a count IF with a several embedded IFs

[dcusworth]

Hi

I am new to the site and hoping someone may be able to rescue my school data tracking spreadsheet project from certain death!

Basically I have created a spreadsheet which in relevance to this formula matches a class code i.e. 7ABC (B4) which is entered on a Y7 overview sheet, with any students in a sheet 'Year 7' marked as in that class in C5:C300. It then counts the number of students making '3LP' within that class and dumps this into cell E11 on my Y7 Overview sheet. I have this bit working fine, but what I am also wanting to do is get the formula to also match the 'Term' (B5) selected so that it will only bring back levels of progress for the selected term and group. I have pasted my formula below. Any help with this would be greatly appreciated as I have tried placing brckets/ANDs/IFs/ORs in more places than I can remember!

=(IFERROR(IF($B$5=1,COUNTIFS(('Year 7'!$C$5:$C$300),('Y7 Overview'!$B$4),('Year 7'!$Q$5:$Q$300),"4LP"),
AND(IF($B$5=2,COUNTIFS(('Year 7'!$C$5:$C$300),('Y7 Overview'!$B$4),('Year 7'!$Y$5:$Y$300),"4LP")),
AND((IF($B$5=3,COUNTIFS(('Year 7''Year 7'!$C$5:$C$300),('Y7 Overview'!$B$4),('Year 7'!$AG$5:$AG$122),"4LP")))))),"No Data"))

 

[Gerald Higgins]

Hi, welcome to the board.

It's difficult to answer this precisely without seeing your data, and without knowing exactly what you want to do, which isn't clear from your post.

But looking at your formula, we can try and guess what you're trying to do.

The COUNTIFS all appear to be identical, so it seems that you are trying to perform the COUNTIFS if B5 = 1 OR if B5 = 2 OR if B5 = 3.

If this is the case, then perhaps something like this

=IFERROR(IF(OR($B$5=1,$B$5=2,$B$5=3),[insert the COUNTIFS element here],"No Data"),"No Data")

It's not clear when you want "No Data" to appear - is it if B5 is NOT = 1, 2, or 3 ? Or is it if the IFERROR condition is TRUE ?
I've made it apply to both situations.

 

[Mackers]

Just in case you don't have a solution from the above, based on your text it sounds like you just want another criteria within the COUNTIFS() that also requires the Term to match?

I'm imagining there is a column containing which term you're in, a column containing which class and a column containing which year. You just need to select these as all the odd arguments of the COUNTIFS() and then select the cells containing the term, class and year you want in the even arguments. So something like this:

=COUNTIFS(TermsColumn,RequiredTerm,ClassColumn,RequiredClass,YearColumn,RequiredYear)

Not sure if that helps but good luck!