• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Simplifying COUNTIFS

K

kenshinobi

New Member
Hi everyone,


Being a bit of an excel rookie I thought my best option would be to ask the pros!

(I did try to work this one out myself first but the examples I have read about don't cover this issue very well).


In the first row of each column from A-K I have a title for a number of tests.

In the second row I have the pass mark for each of these individual tests

In the following rows (3-250), I have the scores for each student


In column J, next to each row of results, I am comparing all of these results against their individual pass marks, to count how many tests people failed to pass. As such I have used the following formula:

(this one is at the end of the first row of results)


=SUM(COUNTIFS(A3,"<"&$A$2),(COUNTIFS(B3,"<"&$B$2)),(COUNTIFS(C3,"<"&$C$2)),(COUNTIFS(D3,"<"&$D$2)),(COUNTIFS(E3,"<"&$E$2)),(COUNTIFS(F3,"<"&$F$2)),(COUNTIFS(G3,"<"&$G$2)),(COUNTIFS(H3,"<"&$H$2)),(COUNTIFS(I3,"<"&$I$2)),(COUNTIFS(J3,"<"&$J$2)),(COUNTIFS(K3,"<"&$K$2)))


Is there a way of simplifying this formula so that my co-workers can edit it easily if we need to increase the number of tests in the future?


Many thanks for your advice!
 
In fact I managed to improve the formula to:

=COUNTIFS(A3,"<"&$A$2, B3, "<"&$B$2, C3,"<"&$C$2, D3,"<"&$D$2, E3,"<"&$E$2, F3,"<"&$F$2, G3, "<"&$G$2, H3,"<"&$H$2, I3,"<"&$I$2, J3,"<"&$J$2, K3,"<"&$K$2)


Noticed the SUM was stupidly redundant.

Still, simplifying it further would be great!


Many thanks!
 
Hi Ken ,


I have not understood what you want to do ; can you explain in more detail ?


To simplify matters , can we list things in specifics :


1. There are tests labelled A , B , C , D and so on.


2. There are pass marks PA , PB , PC , PD and so on , for each test.


3. There are students S1 , S2 , S3 , S4 and so on.


What do you want to count ?


Do you want to know how many tests S1 failed in , how many S2 failed in and so on ?


If so , in the last column after the last test , put in the following formula :


=SUMPRODUCT(--(A3:K3<$A$2:$K$2))


Put the above formula in L3 , and copy it down.


The result will be the number of tests each student has failed in.


Narayan
 
Hi Narayan,


Yes sorry, what you have said above is exactly correct, I want to count how many tests S1 failed, then I can copy that formula down through all 250.


--edit: your suggestion works like a charm! Thank you very much! :)
 
Hi Ken ,


I just edited my last post ; please see the formula given there and try it out to see whether that is what you wanted.


Narayan
 
Just off the top of my head, I would remove the need for a pass mark and the actual score by a percentage, and then simply count if equal to or higher than 50%.

***edit: lower than, i prefer counting the number of passes :) ***


unless your really need the actual score as well.
 
Thank you so much everyone for your quick replies, I'm spoilt for choice!

The formula =SUMPRODUCT(--(A3:K3<$A$2:$K$2)) which Narayan provided got it working perfectly.
 
I've been doing a bit of research on that myself, it's a way of forcing positive numerical values from the array.


This article explains more:

http://www.mcgimpsey.com/excel/formulae/doubleneg.html


That's not to say I wouldn't appreciate any further explanation - learning is always good!

:)
 
Hi Raja ,


The link Ken has posted should explain it well though I haven't gone through it myself.


However , in case you want an explanation here , the first point is that SUMPRODUCT operates on arrays ; in this case , the operations within the SUMPRODUCT will do the following :


Look at each value in the range A3 through K3 , comparing it with the values in the range A2 through K2 i.e. A3 will be compared with A2 , B3 will be compared with B2 , C3 will be compared with C2 , and so on.


The result in each case will be either TRUE ( if the check is satisfied , which means the value in row 3 is less than the corresponding value in row 2 ) or FALSE.


We will therefore end up with the following sample result array :


{TRUE,FALSE,TRUE,TRUE,FALSE,FALSE,FALSE,TRUE,TRUE,FALSE,FALSE}


What we now want to do is to count each TRUE as 1 and each FALSE as 0 , so that we should get a result of 5.


The purpose of the -- is to convert the logical values of TRUE and FALSE to numeric values of 1 and 0 , so that the SUMPRODUCT can return a numeric value.


If you do not use the -- , the SUMPRODUCT will return a value of 0 , which may not be the correct answer for that particular row.


Narayan
 
You must log in or register to reply here.
Back
Top