COUNTIF OR COUNTIFS Formula isn't Working

[rtryan]

Let me start by saying I'm an Excel Novice. I'm looking for help with this formula: =COUNTIFS('Test Points'!F2:S2,">0",['Plyr of Yr'!B2:B160,"Smith, John"]). I'm trying to count (in as different worksheet) the number of times a number greater than zero appears in cells F2 thru S2 and match the name of Smith, John in cells B2 thru B160. I would also like to copy this formula so the name changes. Thank you in advance.

 

[DanteAmor]

Could you put an example minisheet?
Manually enter the result you expect.
Use the xl2bb tool to give the example.


Note XL2BB:
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[rtryan]

Let me start by saying I'm an Excel Novice. I'm looking for help with this formula: =COUNTIFS('Test Points'!F2:S2,">0",['Plyr of Yr'!B2:B160,"Smith, John"]). I'm trying to count (in as different worksheet) the number of times a number greater than zero appears in cells F2 thru S2 and match the name of Smith, John in cells B2 thru B160. I would also like to copy this formula so the name changes. Thank you in advance.

 

[Fluff]

Maybe

=SUMPRODUCT(('Test Points'!F2:S2>0)*('Test Points'!B2:B160=B2))

 

[rtryan]

Maybe

=SUMPRODUCT(('Test Points'!F2:S2>0)*('Test Points'!B2:B160=B2))

Is there a way to have B2 listed as the actual name instead? The worksheet I'm placing this formula in has 160 different names. Also, where do I put the $ signs when I drag/copy the formula down?

 

[Fluff]

The worksheet I'm placing this formula in has 160 different names.

Where are those names?

 

[rtryan]

Where are those names?

Where are those names?

The names are in column b. I want to put the formula in column g.

 

[Fluff]

In that case you can put this in G2 & drag down

=SUMPRODUCT(('Test Points'!$F$2:$S$2>0)*('Test Points'!$B$2:$B$160=B2))