# I thought the solution was to use sumproduct()

#### Shmerty

##### New Member
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
143.8 KB · Views: 9

### 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
Maybe you can use this example:

Map3
BCDEFGHIJKLMN
3N5N3N4N5N3N4N5L3345
4a37453432
5b3111432
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
Maybe you can use this example:

Map3
BCDEFGHIJKLMN
3N5N3N4N5N3N4N5L3345
4a37453432
5b3111432
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
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?

#### Shmerty

##### New Member
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.

Liam

Replies
5
Views
405
Replies
0
Views
138
Replies
13
Views
300
Replies
10
Views
675
Replies
5
Views
86

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.

### Which adblocker are you using?

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

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