Using a function to count the number of rows in which multiple values are true

**jimbowl** · 04-17-2013, 11:36 AM

Hello everyone...

My first post here so please be gentle....

I've read a few dozen threads and I've approached the vlookup, countif, if, sumproduct, and a handful of other strategies to no avail - I figure this is simple enough and I'm probably not doing it correctly but I'd love any help I can get. I've attached a file and I've detailed below what I'm trying to do....

So I've got a rather small table of data that serves as a schedule for a soccer league. I'm hoping to create an automated standings page that pulls wins and losses from the table. Easy enough - but the kicker is that there are multiple divisions, and some of the divisions have identically named teams...for example, the tigers might be a team in the boys division as well as a team in the girls division, but those wins and losses must be counted separately.

A simplified example of what I'd like to do is have a function that counts and displays the number of times that for every row where "girls" is in the division column AND "tigers" is in the winner column.

I don't know if this is a good explanation but I'm here ready to try and rearticulate! Thanks for your time!

**MarvinP** · 04-17-2013, 11:51 AM

Hi Jim and welcome to the forum,

You could do this problem using Pivot Tables. You make the rows the regions, division and winner and then count the winner names. I hope that helps.

**jimbowl** · 04-17-2013, 12:21 PM

Originally Posted by MarvinP

Hi Jim and welcome to the forum,

You could do this problem using Pivot Tables. You make the rows the regions, division and winner and then count the winner names. I hope that helps.

Thanks for your help Marvin....I'm reading over a tutorial on pivot tables on itechtalk and there's a warning that makes me a bit reluctant to pursue this route....

"NOTE: - ensure that the source data has column headings and there are no blank spaces in the range of cells that contain the source data."

As I insert data into the spreadsheet as the games occur, how would it treat the blank cells?

**MarvinP** · 04-17-2013, 12:26 PM

Hi Jim,

See the attached that shows what I was suggesting. See sheet 2 for the pivot table.

**jimbowl** · 04-17-2013, 12:47 PM

Originally Posted by MarvinP

Hi Jim,

See the attached that shows what I was suggesting. See sheet 2 for the pivot table.

This all looks promising Marvin, but I'm worried I'm just not proficient enough to utilize the pivot table. Take a look at my attachment. I tried to do something simple and add a Loss column with a Count of Loss and it's returning incorrect data. Maybe I just need to learn more about pivot tables?

**jimbowl** · 04-17-2013, 01:27 PM

Oh wow I'm so close....i believe the issue is that my loss column uses an IF function that automatically plugs the non-winner in so it's somehow confused....otherwise the pivot table will do what I need it. If anyone can help here I'd greatly appreciate it.

**jimbowl** · 04-17-2013, 01:36 PM

Originally Posted by jimbowl

Oh wow I'm so close....i believe the issue is that my loss column uses an IF function that automatically plugs the non-winner in so it's somehow confused....otherwise the pivot table will do what I need it. If anyone can help here I'd greatly appreciate it.

Okay I was incorrect...Loss needs to be a row label to correctly count losses...is there any way I can combine win and loss columns as one row value provided that they share the same values? And then would the count functions work??

**arlu1201** · 04-17-2013, 02:32 PM

jimbowl,

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

**jimbowl** · 04-17-2013, 06:19 PM

I figured it out and if I must say so myself...I am amaaaaaazing!!!

If anyone happens upon this thread...I solved the problem w the sumproduct function....since we're counting x dependent on y and z, you just need to turn y and z into a number - I originally wanted to approach this with the IF function but the key is that it needs to be able to use an array! Soooooo you plug in the double negative to your standings table to look something like this...

A, B, C
Blank Cell,Girls Division, Wins
Lions, =sumproduct(--(divisionarray=B1),--(winsarray=A2))

Using a function to count the number of rows in which multiple values are true

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Using a function to count the number of rows in which multiple values are true

Re: Not sure which function to use...please advise

Re: Not sure which function to use...please advise

Re: Not sure which function to use...please advise

Re: Not sure which function to use...please advise

Re: Not sure which function to use...please advise

Re: Not sure which function to use...please advise

Re: Not sure which function to use...please advise

Re: Using a function to count the number of rows in which multiple values are true

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions