Google Spreadsheet Matching values to repeating rows

Question:

( by Dylan Prober )

Hello,

I'm trying something that is seemingly easy, but has become quite complicated.  Here's the test example:

Here's what I would like to do:  I would like to count how many times a single value (in this example Value3) appears in each Subject, and report it to the Reporting Sheet.  Add onto this, the range will only be the "Rounds", so in the example columns B:D for Round 1 and columns F:I for Round 2.  And one last layer is that Value3 need only be counted once per Subject.  So, in the example on the first "Name"/Set, Subject1 has 2 "Value3"s, but it only needs to be counted once.

Here's what I have tried:

Best (but still not good):  I put this in B6 for the count of Subject1 on the Reporting sheet :  =COUNTUNIQUE(MainSheet!B7:D7,MainSheet!B17:D17,MainSheet!B27:D27,MainSheet!B37:D37,"Value3")

This seemed to work, but the formula got really long as in my actual sheet I have 21 repeated sections so it would go all the way to "MainSheet!B217:D217" and if I changed a value in the range, it would not update.  For instance, I changed a Value3 to a Value2 and the count did not go down by 1.  Also, doing this for each subject is quite tedious and would need to be repeated for Round 2 and all the following rounds, i.e. "MainSheet!F7:I7" etc.

Perhaps Correct, but Never Works:  Again, in B6 for the count of Subject1 on the Reporting sheet:

=INDEX(MainSheet!A2:A,MATCH(Countunique(MainSheet!D:B,"Apprentice"),MainSheet!D:B,0))

With this I keep getting an error "error: Range must be 1xN or Nx1"  and I'm not sure what it means to correct.

I have also tried pivot tables, but it never seems to give me the option to count all the Values (Value3 specifically) because inevitably in the first column is all Value1 or Value 2, or other similar issues.

I have also tried naming the ranges per row for the subject, but the Named Ranges wouldn't let me select multiple rows (or I couldn't see how to do it) and I still have the problem of having to do it again each time a new Round starts.

Any suggestions? :)

Solution:

Have a look at the following screenshot of Sheet "Reporting Sheet":

In the above sheet I have the following formula in Cell B6:

=countunique(iferror(query('MainSheet'!A:D;"select B,C,D where A = '"&A6&"' ";0)))

and I have the following formula in Cell D6:

=countunique(iferror(query('MainSheet'!A:I;"select F,G,H,I where A = '"&A6&"' ";0)))

after inserting the above two formulas, you can drag it to the cells below.

And just to show you the calculation part, I have the following formula in Cell G4:
=query('MainSheet'!A:D;"select B,C,D where A = 'Subject1' ";0)

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive