Ideas on how to perform a count based on multiple criteria

[Maverick68]

Hi, hopefully an Excel genius can help
Ok, I have an exams spreadsheet, with students names in Column A (lines 1-100), and then Gender (M/F) in column B, followed by Subjects in Columns C/D/E/F/G.
I know how to count the total subject "A" Passes (Countif), and the total number of "A" Passes by Gender (Countifs) but what I'm really looking to do is count the number of students who have achieved either a grade A,B or C across 3 or more subjects.
Any ideas ?
Thanks !

[Pepe Le Mokko]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Maverick68]

Thanks
Ok if you refer to the attached, in Column K I'm looking for a formulae that will return a value of 1 if the student has achieved 3 or more Grade A* - C (the A* over here is a grade above an A).
I've highlighted in green the results of 3 or more A* - C.
Thanks in advance for any suggestions.

[Tony Valko]

Maybe this...

Data Range

	C	D	E	F	G
1	Subject1	Subject2	Subject3	Subject4	Subject5
2	D	D	B	C	F
3	A	A	A	A	A
4	C	D	D	C	B
5	F	F	C	C	D
6	C	D	D	C	C

=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(C2:G6,{"A","B","C"},0)),{1;1;1;1;1})>=3))

Result = 3

Rows 3, 4 and 6 are being counted.

[Maverick68]

That is a genius answer, and it works, thanks so much !!
One more question, is there a function menu option that will step me through the above, or an abbreviated version ? Reason I ask is that this may crop up in my exam, and I would be hard pushed to remember this formulae.
Thanks

[sktneer]

Edit: I think I misunderstood the question. Probably you need the total count not the count for each row.

[Tony Valko]

Try this...

Select the formula cell.

Goto the Formulas tab.

In the Formula Auditing group, click on the Evaluate Formula icon. It's the one that looks just like my avatar!

That will open a userform. Repeatedly click the Evaluate button and it will step through the formula showing you the results of each step.

[FDibbins]

Withe cursor on that cell, click the Fx (insert function) button to the left of the formula bar. a window will open, and you can step through the formula to see what is doing what

[Maverick68]

Thanks Pepe, Tony, Sktneer and FDibbins, between you guys you have solved my issue and I will sleep easy tonight
This forum is excellent and is now saved in my bookmarks !

[Tony Valko]

You're welcome. We appreciate the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

[Maverick68]

Ok done, thanks Tony

[sktneer]

@ Tony

While trying to understand your formula using MMULT function (which I never used), one issue I was thinking about that what if the data set is large say 25 columns or more, what is the best way to define the array2 inside the mmult. Though you have used {1;1;1;1;1}, will it be ok to use another formula to define the this array part?

Please refer to the sheet attached in OP's post#3, can I use the formula like below, though it becomes an Array Formula then...

Please Login or Register  to view this content.

Or is there an better way to define the array2 inside the mmult function?

Your assistance is needed.

[Tony Valko]

In this application the 2nd array needs to be a vertical array of 1s, one for each column in the range.

When there are only a "few" columns in the range we can use an array constant as I have:

{1;1;1;1;1}

When there are "many" columns you wouldn't necessarily want to use an array constant like this:

{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1}

So, we need generate that array via calculation. Any method you can think of will work but I would tend to use:

COLUMN(Range)^0

Since we now have to use the TRANSPOSE function which requires array entry** I would replace SUMPRODUCT with SUM:

=SUM(--(MMULT(--(ISNUMBER(MATCH(E3:I12,{"A*","A","B","C"},0))),TRANSPOSE(COLUMN(E3:I12)^0))>=3))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[sktneer]

@ Tony

COLUMN(Range)^0 is a great idea to generate array of 1's.
Thanks a lot Tony for the guidance you have provided.

[Tony Valko]

You're welcome!