Not sure if this should be SUMIFS or something else?

[Janine McLean]

Hi

I am trying to summarise some information into date sections. I have a school detentions list and I need to show how many days detention each pupil has had each term on a separate worksheet. I have started using a SUMIFS function but it appears not to be working. I'm not sure if this is even possible with the way that the information has been set out. Any help with this would be much appreciated.

 

[PaulF]

Janine,

I might take a different approach going forward and get all of the detentions in a 2d table where for each line you enter student (or student ID), date, dept, days period. Support tables would be terms & departments. Then finish with a summary table to pull it all together. That being said I did a quick edit, added a col. for combined Last, First and term summary at the end. I then used a index/match to pull it all together. I don't like the formula to sum the 10 detention periods, but it works. I hope this helps you.

P.S. the term lookup is an array formula, so if you edit it make sure you
Ctrl-Shift-Enter

 

[Janine McLean]

Thank you so much Paul. I have never used Tables before so I am learning something new, which is great. I will go and research 2d tables and see what I can come up with. However, what you have done here is amazing and I could never even dream of compiling some of those formulas!!

Once again much appreciated and will go off and do some studying - every day is a school day!!

 

[bosco_yip]

Without helper, try this one formula way :

In "Pupil Summary By Term" sheet F3, enter formula copy across and down :

=SUMPRODUCT((Pupils!$D4:$AE4>=INDEX(Terms!$C$3:$C$6,MATCH(F$2,Terms!$B$3:$B$6,0)))*(Pupils!$D4:$AE4<=INDEX(Terms!$D$3:$D$6,MATCH(F$2,Terms!$B$3:$B$6,0))),Pupils!$F4:$AG4)

Regards
Bosco

 

[Janine McLean]

Thank you so much Bosco

I have tried that and it has worked. I will now have a look at the SUMPRODUCT function as I have never used that before. It's not until you need excel to do something that you learn about all the different things it can do. I really appreciate this.