Formula Countif/count/and/+?

[airolgmd]

Okay, I've got this worksheet...

B Column has either an H (home) or an A (away) in the cell.

There's a "player" column using Columns E through N. If the player is scheduled to play, I've placed a "1" in their column so that I total across to be sure there are six players each week and then down so that each player has equal playing time.

If a player is not available, there is an "x" in the cell, so I used

=COUNTIF(E3:E20,"x") to total how many they can't play, but I can't figure out how to do this using two different columns.

So, I want Excel to look at each "1" then look for "H" (and then again for an "A") so I can count how many home games/away games each person has without worry as I juggle players.

Can I tell it to look at these cells and give me a total?

I can clarify, if needed.

[teylyn]

Hi airolgmd,

welcome to the forum. Since you are using Excel 2007, you can use COUNTIFS() instead of COUNTIF()

=countifs(range1, value1, range2, value2,...,)

If that does not help, post a small data sample. I can't quite picture how your data is laid out and what you want to count.

cheers

[airolgmd]

Okay, countifs isn't working for me. <grin>

Column A (date) Column B (H or A) Column C (Player name but for each week they are scheduled to play, I've placed a 1 in that cell)

1/5/2010 H 1
1/12/2010 A
1/19/2010 H 1
1/26/2010 A 1


So, in one of the cells below each player, I want it to count how many home games they are playing. So I need it to look for the H and the 1. In this example, it would see that this player is playing two home games and one away game.

I have 10 players and only six can play each week, so I had it sum across to be sure I only have six players that week. I also have it SUM down, so that I can move players around and try to equal out their playing time. I just don't want anyone playing all home games or all away games, so I want it to calculate that info, so I can switch them and not have to worry about that aspect of the schedule.

Did I go about this in the wrong way?

[teylyn]

can you post a workbook that reflects your data layout and mockup your expected results? At this point I'm not sure what you want to see, because I can't picture your data layout.

[airolgmd]

Okay, here's my workbook.

Questions????

[teylyn]

Actually, you can do it with SUMIF, just use the sum_range argument

=SUMIF(B3:B20,"=H",C3:C20)

so, sum all numbers in column C (where the 1's are) if column B = "H"

but here's the syntax for SUMIFS in case you want to use it for something else.

=SUMIFS(C3:C20,C3:C20,1,B3:B20,"=H")

[airolgmd]

That so worked!!! Thank you.

[airolgmd]

One more question, why isn't my "Sit Out" formula working correctly?

If the cell is blank, I wanted it to count it. My "unsure" is working. Thoughts?

Thanks in advance.

[teylyn]

"Sit out" would be .... "Not playing"?

Your formula works, you just need to make sure that the cells are truly empty! F6, for example has a character in it. A space, probably.

to see which cells are truly empty, do this:

- select K3:L20
- hit F5
- click Special
- tick "Blanks"
- hit OK

now the truly blank cells will be selected. Any other cell that appears to be blank has some kind of character in it.

To remove all doubt, maybe you could use another symbol for "not playing", maybe 0 or _ and then countif on that....


hth

[airolgmd]

Perfect! I just used "0".

Thank you so much. I'm a happy camper now!

[teylyn]

Glad it worked for you. I like happy campers.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save