Criteria values in COUNTIFS function

**jsnoz** · 12-02-2008, 11:39 PM

I have the following formula in a sheet to record basketball stats that I am putting together.

=COUNTIFS('Running Game Log'!A:A,"=Jarod",'Running Game Log'!C:C,"=2 pt miss")

This formula counts from a list that is generated during in-game recording of stats & obviously counts the number of times "Jarod" misses a "2 pt shot".

My question is can the criteria "Jarod" refer to a value in a cell, rather than the specific name "Jarod" as I want to be able to change player names easily, rather than have to edit this formula every time. I have not been able to find this syntax in any of the Excel help files & I am starting to think it cannot be done...

TIA

James

**teylyn** · 12-02-2008, 11:50 PM

Hi

your function won't work like this because countif only takes two arguments.

You can count how often Jarod appears like this

=COUNTIF(A5:A13,A3)

instead of

=COUNTIF(A5:A13,"Jarod")

if you type the name in cell A3 for my example.'

If you want to count with multiple conditions, you need to use Sumproduct. A good description of this is here

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

cheers

**ratcat** · 12-03-2008, 12:05 AM

G'day James,

Your using excel 2007 ??

If you are try this and double check the spelling and the spacing between the formula and what in the cells.

Please Login or Register  to view this content.

Cheers

**jsnoz** · 12-03-2008, 12:52 AM

Teylyn, I am using the COUNTIFS function, not COUNTIF, so I can use multiple arguements, it is just not quite "playing" the way I want it to... The SUMPRODUCT function may work & I will have a go with that too - thanks

Ratcat - yes I am using 2007 & the formula as I cut it in works just fine, I was just hoping the "Jarod" criteria could refer to a cell rather than a fixed criteria as it makes editiing for use with other teams easier.

As it appears that COUNTIFS is new to 2007, then I'm equally stuffed as the machine I would be running this on does not have 2007 installed yet...

**gonzalodb** · 12-03-2008, 01:08 AM

Hi Jsnoz,
Instead of having the name of the payer in your formula, in this case "Jarod", you can have a separate cell where you enter the name of the player and that way every time you want to use other names, you just type the name in. Your formula should refer to that cell.
You will have no problem by doing so when using the countifs function.

**jsnoz** · 12-03-2008, 01:09 AM

Sumproduct works perfectly

Please Login or Register  to view this content.

thanks Terlyn

**jsnoz** · 12-03-2008, 01:13 AM

gonzalodb - what you are suggesting is exactly what I tried, but the formula gave me an error when I put a cell reference in there as a criteria, so I was njot sure if it was me, or if it would not be accepted. The SUMPRODUCT solution produces the same resul anyway...

Cheers

**ratcat** · 12-03-2008, 01:28 AM

G'day jsnoz,

Your welcome to supply an example of the Countifs formula to see what is going wrong with the cell reference in the formula.

Cheers

**gabe17** · 10-21-2011, 11:55 AM

Originally Posted by ratcat

G'day jsnoz,

Your welcome to supply an example of the Countifs formula to see what is going wrong with the cell reference in the formula.

Cheers

Hi I am having a similar issue with Countifs

My criterion looks like this

=COUNTIFS('[april 1, 2011 rent roll.xls]Rent Roll'!$F$20:$F$1222,A4,'[april 1, 2011 rent roll.xls]Rent Roll'!$H$19:$H$1222,">0")

Where A4 is a cell with text that I want to be found in the f range.

Both of these ranges and criteria work perfectly if I use the countif formula for just one of them.

Any help would be greatly appreciated.

Thanks,

Gabe

**gabe17** · 10-21-2011, 11:59 AM

Just solved my own problem. The ranges have to have the exact same row ranges or there will be an error.

In my example I was using 20 - 1222 and then 19 - 1222. When I changed it to 20 and 20 it worked fine.

Thanks anyway

Gabe

**NBVC** · 10-21-2011, 12:46 PM

Gabe, for future reference.....

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.