Counting something

[Slim Slender]

Game Count Team Errors Type
1 1 Home None n/a
1 Visitor None n/a
2 1 Home Error A
2 Visitor None n/a
3 1 Home None n/a
3 Visitor Error B
4 1 Home Error C
4 Home Error D
4 Visitor None n/a
5 1 Home Error A
5 Home Error B
5 Home Error C
5 Visitor Error A
6 1 Home None n/a
6 Visitor Error A
6 Visitor Error B

Here is some dummy sample data. The "Count" field contains a formula:
=if(countif(a$2:a2,a2)>1,"",1) which gives a count of unique games in
a PivotTable. I can also very easily get a count of Total Errors with
a PT. What I can't figure out is how to get a count of Games with
Errors which I know is 5 (Games 2 thru 6). Any solution is welcomed:
one or more new fields in the data table, a calculated field in a PT,
a sumproduct or any other formula, whatever works.

 

[Don Guillett Excel MVP]

Game    Count   Team    Errors  Type
1       1       Home    None    n/a
1               Visitor None    n/a
2       1       Home    Error   A
2               Visitor None    n/a
3       1       Home    None    n/a
3               Visitor Error   B
4       1       Home    Error   C
4               Home    Error   D
4               Visitor None    n/a
5       1       Home    Error   A
5               Home    Error   B
5               Home    Error   C
5               Visitor Error   A
6       1       Home    None    n/a
6               Visitor Error   A
6               Visitor Error   B

Here is some dummy sample data. The "Count" field contains a formula:
=if(countif(a$2:a2,a2)>1,"",1) which gives a count of unique games in
a PivotTable. I can also very easily get a count of Total Errors with
a PT. What I can't figure out is how to get a count of Games with
Errors which I know is 5 (Games 2 thru 6). Any solution is welcomed:
one or more new fields in the data table, a calculated field in a PT,
a sumproduct or any other formula, whatever works.

I don't like pivot tables but will try if you send (withOUT pivot
table) your desires
"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[Don Guillett Excel MVP]

I don't like pivot tables but will try if you send (withOUT pivot
table) your desires
"If desired, send your file to dguillett  @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."- Hide quoted text -

- Show quoted text -

=IF(COUNTIF(A$2:A2,A2)>1,"",SUMPRODUCT(($A$2:$A$22=A2)*($C$2:$C
$22="error")))

 

[Slim Slender]

=IF(COUNTIF(A$2:A2,A2)>1,"",SUMPRODUCT(($A$2:$A$22=A2)*($C$2:$C
$22="error")))- Hide quoted text -

- Show quoted text -

Thanks for the effort but this doesn't get it. This formula has to be
in every record of the database and gives an array of results
answering the question "How many errors were there in each game
(including games with no errors)?" What I need is, as I said, "a
formula that gives the number of games in which there was an error" or
"a count of games with errors." This would be a single formula in a
single cell returning a single result. For example, the result for the
sample data is the number 5.

 

[Bob Phillips]

Is this array formula what you want

=SUM(--(FREQUENCY(IF(D220="Error",MATCH(A2:A20,A2:A20,0)),ROW(INDIRECT("1:"&ROWS(A2:A20))))>0))

This is an array formula.
This means that you use Ctrl-Shift-Enter to commit the formula, not just
Enter (array Enter it). Excel will put curly brackets around the formula in
the formula bar, you don't do this.
If you need to change the formula at any time, you must array Enter it
again.

--

HTH

Bob

=IF(COUNTIF(A$2:A2,A2)>1,"",SUMPRODUCT(($A$2:$A$22=A2)*($C$2:$C
$22="error")))- Hide quoted text -

- Show quoted text -

Thanks for the effort but this doesn't get it. This formula has to be
in every record of the database and gives an array of results
answering the question "How many errors were there in each game
(including games with no errors)?" What I need is, as I said, "a
formula that gives the number of games in which there was an error" or
"a count of games with errors." This would be a single formula in a
single cell returning a single result. For example, the result for the
sample data is the number 5.

 

[zvkmpw]

... What I need is, as I said, "a

formula that gives the number of games in which there was an error" or
"a count of games with errors." This would be a single formula in a
single cell returning a single result. For example, the result for the
sample data is the number 5.

Any solution is welcomed:
one or more new fields in the data table, ...

This works for me, if I understand the problem correctly.

First put this in F2 and copy downward past the end of the data:
=AND(D2="ERROR",1=SUMPRODUCT((A$1:A2=A2)*(D$12="Error")))

Then use this formula:
=COUNTIF(F:F,TRUE)

Hide column F if desired.

Modify to suit.

Explanation: The AND formula tags those rows satisfying two
conditions: (a) the row has "Error" in column D and (b) no previous
row for the same game has "Error" in column D. Then count 'em up.