LinkBack URL
About LinkBacks
Hi,
I have columns that tells me which courses specific people have passed.
I would like this information to result in a matrix for better overview.
Please have a look at the attached file.
Br Bertrand
Hi,
I have columns that tells me which courses specific people have passed.
I would like this information to result in a matrix for better overview.
Please have a look at the attached file.
Br Bertrand
Try:
=IF(COUNTIFS($A$4:$A$9,F$3,$B$4:$B$9,$E4,$C$4:$C$9,"Passed"),"Yes","")
copied down and across.
Note: You have Eric(Erik) spelled 2 different ways....
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Unfortunately i get a formula error message.
Br
Please view attached jpeg.
Use this formula in F4 =IF(SUMPRODUCT(($A$4:$A$9=F$3)*($B$4:$B$9=$E4)*($C$4:$C$9="Passed"))=1,"Yes","No")
As previous poster noted, Eric will not work since it's spelled differently
Hi,
thank you but still have some problems with the formula.
Br
I'm not sure why...both formulas that NBVC and I presented should have worked.
I've attached the file I did the formula in
Strange... your file works, thank for the assistance!
Br
It is probably because your excel version uses semi colons as argument separators instead of commas.
Regarding:
=IF(SUMPRODUCT(($A$4:$A$9=F$3)*($B$4:$B$9=$E4)*($C$4:$C$9="Passed"))=1,"Yes","No")
If I want to find $A$4:$A$9 in another sheet with name "matrix data", How do I add this to the formula?
Br
Hi,
find the attachemnt
and U can add multiple conditions.
Okey, I have made it a little more difficult.
Please view attached file
Try:
=IF(SUMPRODUCT((Data!$A$4:$A$20=C$4)*(Data!$C$4:$H$20=$B6)*(Data!$D$4:$I$20="Passed")),"Yes","No")
adjust ranges to suit and copy down and to next column(s).... note the last condition range is offset to the right by 1 column....
replace commas with semi-colons if needed in your version.
Note: Also Sumproduct is not the most efficient method for ranges so large. If you can add a column that identifies which header in the Data sheet the information is in, then you can use COUNTIFS which is much more efficient. See attached columns A, F and G in the Matis sheet.
In F4, formula is:
=IF(COUNTIFS(Data!$A:$A,F$4,INDEX(Data!$C:$H,0,MATCH($A6,Data!$C$3:$H$3,0)),$B6,INDEX(Data!$C:$H,0,MATCH($A6,Data!$C$3:$H$3,0)+1),"Passed"),"Yes","No")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks