Countifs not working

[Gower1983]

Hello,

I am doing some sports analysis for a project and I and trying to count number of appearances for players in any given season

To do this I am trying to use the countifs formula to count the number of player appearances as criteria one and season as criteria 2. But I keep getting a #value! error.

When I do the formula individually I get the results I expect but only when I combine them in countifs I then get the error.

Can anyone shed any light on why it will not work?

 

[Fluff]

Hi & welcome to MrExcel.

Can you post some sample data.
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.

 

[Gower1983]

Hi & welcome to MrExcel.

Can you post some sample data.
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.

 

[Fluff]

Try

=SUMPRODUCT((Database!$C$10:$C$1496=$I$1)*(Database!$S$10:$AH$1496=D2))

 

[Gower1983]

Try

=SUMPRODUCT((Database!$C$10:$C$1496=$I$1)*(Database!$S$10:$AH$1496=D2))

That seems to have worked, thank you. Is there anything I can input so that when column D is black it returns 0?

 

[Fluff]

How about

=if(d2="",0,SUMPRODUCT((Database!$C$10:$C$1496=$I$1)*(Database!$S$10:$AH$1496=D2)))

 

[Gower1983]

Perfect, thank you!!

How about

=if(d2="",0,SUMPRODUCT((Database!$C$10:$C$1496=$I$1)*(Database!$S$10:$AH$1496=D2)))

 

[Fluff]

You're welcome & thanks for the feedback.