• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

COUNTIFS

rbobcat1

rbobcat1

Member
Chandoo I am a begineer and love your site and ExcelUser.

I have 2 questions regarding COUNTIFS and what would be the best formula to use?


First, I cannot seem to get COUNTIFS to work. I get no error but it returns a 0.

I used the Help file and Google but can't seem to get it to work for Multiple ranges of cells.

M5 and N5 =COUNTIFS(C:C,"=Team G",G:G,"Team G") returns 0


Second, what would be the best formula to use to track Wins and Loses?

for example, E:E, I:I to return in cell in M17


Thanks and keep up the great work..


Where can I send an eample file?


Happy Holidays.

Bobcat
 
here is a link to the example file


https://skydrive.live.com/redir.aspx?cid=2b770c659a94550d&resid=2B770C659A94550D!195&parid=2B770C659A94550D!102&authkey=!AJr0x3rcZ9O1O9k
 
Opps, this is a public link


https://skydrive.live.com/redir.aspx?cid=2b770c659a94550d&resid=2B770C659A94550D!195&parid=2B770C659A94550D!102
 
Hi rbobcat1,

issue may be related to the formula syntax.

try this: =COUNTIFS(C:C,"Team G",G:G,"Team G")

i just removed the = sign and it works on my Excel.

Cheers,

Kevin
 
The countifs function is trying to only count rows where both criteria are met. As I'm assuming Team G will never play itself, you need to do either this:

=COUNTIF(C:C,"Team G")+COUNTIF(G:G,"Team G")

or this:

=SUMPRODUCT((C:C="Team G")+(G:G="Team G"))


For the latter question, I'm not exactly sure what is in columns E and I. Assuming they are the wins/losses respectively, formulas would be this.

Wins:

=SUMPRODUCT((C:C="Team G")*(G:G="Team G")*(E:E))

Losses:

=SUMPRODUCT((C:C="Team G")*(G:G="Team G")*(I:I))
 
Thx, I tried that and it returns a 0


i have even trid to use SUMPRODUCT found on Chandoo site but it didn't work either.
 
The first formula works ' =COUNTIF(C:C,"Team G")+COUNTIF(G:G,"Team G")

but

'=SUMPRODUCT((C:C="Team G")*(G:G="Team G")*(E:E)) returns #value
 
try: =SUMPRODUCT((C:C="Team G")*(G:G="Team G"))

does that work?

your formula: =SUMPRODUCT((C:C="Team G")*(G:G="Team G")*(E:E)) should work but maybe there is an issue with the data possibly...
 
Kevin's right, there's probably something with the data in E. Specifically, there's either text in a header row, or the data itself is text. A better formula would be:

=SUMPRODUCT((C:C="Team G")*(G:G="Team G")*(E:E=1))

or

=SUMPRODUCT((C:C="Team G")*(G:G="Team G")*(E:E="win"))


depending on what Col E has in it.
 
OK, I reduced all margin of error and removed the word Team and space so all I have are the letters A-K ('minus the letter I).

The formula '=COUNTIF(C:C,"A")+COUNTIF(G:G,"A") works to count the Teams.


Col E will have numbers representing X-Wins and X-Loses


So if Team A has 8 Wins against Team B 6 Wins the score is 8-6. Or 8 Wins and 6 Loses for Team A, and 6 Wins and 8 Loses for Team B


i posted a new version with the above's

https://skydrive.live.com/redir.aspx?cid=2b770c659a94550d&resid=2B770C659A94550D!196&parid=2B770C659A94550D!102
 
Hi ,


Do you think the following formula will give you the results ?


Team A's wins ( this formula gives 11 ) :


=SUMIF($C$2:$C$144,"A",$E$2:$E$144)+SUMIF($D$2:$D$144,"A",$F$2:$F$144)+

SUMIF($G$2:$G$144,"A",$I$2:$I$144)+SUMIF($H$2:$H$144,"A",$J$2:$J$144)


Team A's losses ( this formula gives 17 ) :


=SUMIF($C$2:$C$144,"A",$F$2:$F$144)+SUMIF($D$2:$D$144,"A",$E$2:$E$144)+

SUMIF($G$2:$G$144,"A",$J$2:$J$144)+SUMIF($H$2:$H$144,"A",$I$2:$I$144)


I have broken up the formula into 2 lines for ease of reading.


Narayan


P.S. Instead of the absolute addresses , you can use C:C , D:D ,...
 
last nite i played with it some more and came up with this;

=SUMIF(C2:O144,"A",D2:N144) for wins

=SUMIF(C2:O144,"A",E2:O144) for Loses

but i see some margin for error using these.


i will try your formula


i reloade a new file to skydrive titled league_example1.2.xlsx
 
not sure what the heck happened, copied all values and formulas only into a new file...


https://skydrive.live.com/redir.aspx?cid=2b770c659a94550d&resid=2B770C659A94550D!198&parid=2B770C659A94550D!102
 
yours work, i did remove the absolute for testing


Wins / =SUMIF(C:C,"J",d:d)+SUMIF(f:f,"J",g:g)+SUMIF(j:j,"J",k:k)+SUMIF(m:m,"J",n:n)


Loses / =SUMIF(C:C,"J",e:e)+SUMIF(f:f,"J",h:h)+SUMIF(j:j,"J",l:l)+SUMIF(m:m,"J",o:eek:)


https://skydrive.live.com/redir.aspx?cid=2b770c659a94550d&resid=2B770C659A94550D!199&parid=2B770C659A94550D!102
 
You must log in or register to reply here.
Back
Top