SUM of multiple COUNTIFS

[lambi153]

Hi,

Each week I input scores for a group of golfers. The spreadsheet works out the weekly average. I want the spreadsheet to work out automatically the number of times each golfer has beaten this average score based on theirs;

The code I have so far works it out for the first cells compared but will not make any further additions for other cells.

[/CODE]=COUNTIFS(B4,">"&B13)[/CODE]

The row goes from B TO S however when I add more criteria + range the value given does not change.

Any help would be most appreciated.

J.

 

[Joe4]

I do not know that it is quite clear what you are after.
Please post some sample code, and explain the result that you want it to return based on that.

Note that there are COUNTIFS and SUMIFS functions, which allow for multiple ranges and criteria.
See: https://www.techonthenet.com/excel/formulas/countifs.php
and: https://www.techonthenet.com/excel/formulas/sumifs.php

 

[Oscar Cronquist]

QUOTE=lambi153;5072364]Hi,

Each week I input scores for a group of golfers. The spreadsheet works out the weekly average. I want the spreadsheet to work out automatically the number of times each golfer has beaten this average score based on theirs;

The code I have so far works it out for the first cells compared but will not make any further additions for other cells.

[/CODE]=COUNTIFS(B4,">"&B13)[/CODE]

The row goes from B TO S however when I add more criteria + range the value given does not change.

Any help would be most appreciated.

J.[/QUOTE]

Cell reference B13 must be an absolute cell reference, like this: $B$13.

Example:

	A​	B​	C​	D​	E​	F​
1​
2​		Score			Average
3​		25​	0​		50​
4​		54​	1​
5​		75​	1​
6​		14​	0​
7​		82​	1​
8​		29​	0​
9​		22​	0​
10​		86​	1​
11​		18​	0​
12​		64​	1​
13​		95​	1​
14​

Sheet: Sheet53

Formula in cell C3:
=COUNTIF(B3,">"&$E$3)

 

[Oscar Cronquist]

A better example:

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​
2​		Name	Score			Average
3​		John	25​			50​		John	1​
4​		Ben	54​					Ben	4​
5​		Martin	75​					Martin	1​
6​		John	14​
7​		Ben	82​
8​		Martin	29​
9​		John	22​
10​		Ben	86​
11​		Martin	18​
12​		John	64​
13​		Ben	95​
14​

Sheet: Sheet53

Formula in cell I3:
=COUNTIFS($B$3:$B$13,H3,$C$3:$C$13,">"&$F$3)

 

[RoryA]

The row goes from B TO S however when I add more criteria + range the value given does not change.

Do you mean you want to compare B4 to B13 and C4 to C13 and D4 to D13 etc, and count the number that exceed the averages? If so, then try:

=SUMPRODUCT(--(B4:S4>B13:S13))

 

[lambi153]

Do you mean you want to compare B4 to B13 and C4 to C13 and D4 to D13 etc, and count the number that exceed the averages? If so, then try:

=SUMPRODUCT(--(B4:S4>B13:S13))

Hi Rory,

Thank you for your reply. I managed to get it to work using the following code (I'm sure it would work with what you replied with).

=COUNTIF(B4,">"&B13)+COUNTIF(C4,">"&C13)+COUNTIF(D4,">"&D13)+COUNTIF(E4,">"&E13)+COUNTIF(F4,">"&F13)+COUNTIF(G4,">"&G13)+COUNTIF(H4,">"&H13)+COUNTIF(I4,">"&I13)+COUNTIF(J4,">"&J13)+COUNTIF(K4,">"&K13)+COUNTIF(L4,">"&L13)+COUNTIF(M4,">"&M13)+COUNTIF(N4,">"&N13)+COUNTIF(O4,">"&O13)+COUNTIF(P4,">"&P13)+COUNTIF(Q4,">"&Q13)

Thank you for all the replies.

 

[RoryA]

You definitely don't need COUNTIF there - a simple > comparison will work:

=(B4>B13)+(C4>C13)+(D4>D13)+(E4>E13)+(F4>F13)+(G4>G13)+(H4>H13)+(I4>I13)+(J4>J13)+(K4>K13)+(L4>L13)+(M4>M13)+(N4>N13)+(O4>O13)+(P4>P13)+(Q4>Q13)

but the SUMPRODUCT formula is easier to scale up/down.