Spreadsheet Help

[MJ4H]

I am trying to track how well I do at certain types of problems. I will be doing a massive amount of these problems over time (years, thousands of problems). So, I have this spreadsheet. Each problem has a couple of tags, discussing what the themes in these problems are. I'd lilke to enter a line for each problem, whether I got the problem right or not, and then either one or two of the tags. It will look like this:


Above, the R column is Results (1 = correct and the line is green, 0 = wrong and the line is red)

Is there a way I can keep stats for each tag? Ideally, broken down by primary tag (Tag 1) and secondary tag (Tag 2). Automatically, I mean.

Like at the end I'd love to have something like:

Discovery: 78% correct
Fork: 97% correct
Capture Defender: 88% correct

etc.

Maybe separated by primary and secondary. If that's too difficult, lumped together is fine. Anyone understand and know how to do that?

[Barkeep49]

This should do it:

=(COUNTIFS(E1:EXX,"term",C1:CXXX,1))/COUNTIF(E1:EXX,"term")

Replacing term with the word you want to search in each statement and XX with the range of cells you want it to search through.

[MJ4H]

Brilliant, I will try it.

[MJ4H]

=(countifs(E1:E76,"Fork",C1:C76,1))/COUNTIF(E1:E76,"Fork")

This is what I'm using and I'm getting Error: 508

Are the countifs and COUNTIF functions right? Did I make some other mistake?

[MJ4H]

Maybe openoffice doesn't support countifs?

also the documentation says countif(range; condition) instead of countif(range, condition) for open office. however i can't find anything with more than one condition. Any idea?

[Barkeep49]

Yeah I did that equation in Excel. It seems that OpenOffice and GoogleDocs don't support countifs. A little googling seems to have produced another alternative if you use GoogleDocs. Assuming you will have regular internet access when doing the updates I would recommend GoogleDocs for this project because you can create a form to automatically add entries to the database.

If you use googledocs here's the formula:
=COUNTA( IFERROR( FILTER(E1:e76 ;E1:76 = "fork" ;C1:C76 = 1 ) ) )/countif(E1:E76; "fork")

There appears to be a way to do this in OpenOffice as well, but I couldn't get it to work. Here is a page which describes the solution: COUNTIFS - how many values between 65 and 85? (View topic) • OpenOffice.org Community Forum

[Barkeep49]

DOLA - I admittedly didn't try too hard to get the OpenOffice formula to work and since it pops-up on several different similar querries I'm confident that you could indeed get it to do what you want.

[MJ4H]

Looks like I got it working with SUMPRODUCT. Thanks a bunch.