I thought the solution was to use sumproduct()

Shmerty

New Member
Joined
Oct 30, 2013
Messages
30
Hi everyone,

I always feel a bit guilty coming here and asking for help! But I only use it as a last resort when I've tried tried tried. Even writing this explanation has taken an age.

I thought I knew what I had to do. But then I quickly realise, when implementing the solution, that it is far more complex than I was contemplating.

Schools in Scotland need to make sure pupils leave with at least five qualications. I need to group these pupils into categories: 5@3, 5@4 and 5@5. This translates into pupil X has achieved 5 qualifications at Level 3/4/5.

I have a row of subject names along the top. These subjects are three different levels 3-5. Example: Accounting (N5) = Level 5, PC Passport (L4) = Level 4. I can easily count the number of Level 3/4/5 qualifications using =COUNTIF(C10:AG10,"*3*"), but I need that to return and array of 1 and 0 when the condition is hit, I think.

I then have a row of numbers underneath each subject. Any number below 7 means the pupil is passing that subject at that Level. Again, I can easily count those using =COUNTIF(C11:AG11,"<7"). But I also think I need that to return and array of 1 and 0 so I can multiply it against the above array.

I'm trying to find the simplest way of explaining what I require. In my head I'm picturing an array of all the times a particular Level like "4" is found in the subject row {0,0,1,0,0,1...}. Then I think I need to multiply that against an array of the row of grades that are <7 {1,0,0,0,0...}. This would return that the pupil is on track in 0 Level 4 subjects.

So, I've created a wee mock-up with a manual example of what I'm trying to achieve at the top. It goes without saying I could use COUNTIF(C4,"<7")+COUNTIF(E4:F4,"<7")+COUNTIF(H4:I4,"<7")+COUNTIF(K4:L4,"<7") to count the number of level 4 and above a pupil is on-track in, but I'm trying to work on formula that I'll never need to update if subjects are introduced or removed. There are currently 108 subjects so doing this manually has become an absolute headache, but the solution I've been using for 2 years now.

I've tried inserting a mini-sheet but I cant see how this will re-produce my SS:

trying_to_work_out_key_measures.xlsx
AA
22
Sheet1


So I've also attached an image.

Thanks,
Liam
 

Attachments

  • Excel_levels.JPG
    Excel_levels.JPG
    143.8 KB · Views: 9

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,044
Office Version
  1. 2016
Platform
  1. Windows
Maybe you can use this example:

Map3
BCDEFGHIJKLMN
3N5N3N4N5N3N4N5L3345
4a37453432
5b3111432
Blad1
Cell Formulas
RangeFormula
L4:M5L4=SUMPRODUCT(ISNUMBER(SEARCH(L$3,$C$3:$J$3))*($C$4:$J$4<7)*(($C$4:$J$4>0)))+M4
N4:N5N4=SUMPRODUCT(ISNUMBER(SEARCH(N$3,$C$3:$J$3))*($C$4:$J$4<7)*(($C$4:$J$4>0)))
 

Shmerty

New Member
Joined
Oct 30, 2013
Messages
30
Maybe you can use this example:

Map3
BCDEFGHIJKLMN
3N5N3N4N5N3N4N5L3345
4a37453432
5b3111432
Blad1
Cell Formulas
RangeFormula
L4:M5L4=SUMPRODUCT(ISNUMBER(SEARCH(L$3,$C$3:$J$3))*($C$4:$J$4<7)*(($C$4:$J$4>0)))+M4
N4:N5N4=SUMPRODUCT(ISNUMBER(SEARCH(N$3,$C$3:$J$3))*($C$4:$J$4<7)*(($C$4:$J$4>0)))
Thanks mart37.

I'm teaching from home just now but will have a proper look at this after 3pm.

Liam
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,415
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I might have misunderstood, but isn't this just a COUNTIFS question:

Excel Formula:
=COUNTIFS($C$10:$AG$10,"*3*",$C11:$AG11,"<7")

copied down, and across, but replacing the 3 with 4 and then 5 in each column?
 
Solution

Shmerty

New Member
Joined
Oct 30, 2013
Messages
30
I might have misunderstood, but isn't this just a COUNTIFS question:

Excel Formula:
=COUNTIFS($C$10:$AG$10,"*3*",$C11:$AG11,"<7")

copied down, and across, but replacing the 3 with 4 and then 5 in each column?
You have most definitely not misunderstood. This works. Can't believe how much I was overcomplicating the problem. I had just come up with the solution below, which is at least 4 times longer!

Excel Formula:
=SUM(((ISNUMBER(SEARCH("(N3)",C10:AG10)))+(ISNUMBER(SEARCH("(L3)",C10:AG10))))*(C11:AG11<7)*(C11:AG11<>""))

Your simplified code returns the exact same number☺️👍

At least I've learnt a bit about array formula.

Thanks you both for replying.

Liam
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,415
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Glad we could help. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,009
Messages
5,622,145
Members
415,881
Latest member
tasic89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top