SumIF / SumProduct question

users
#1

I have a spread sheet I use to manage a soccer league.

This shows me of the registered players who is on Dk Blue team.
=SUMPRODUCT($AU$2:$AU$131="Dk Blue", $O$2:$O$131="x")

This shows me the team strength based on assigned values of the players.
=SUMPRODUCT($AU$2:$AU$131="Dk Blue", $AW$2:$AW$131)

I would like to do something like this (this will not work):
=SUMIF($O$2:$O$131="x",$AU$2:$AU$131="Dk Blue", $AW$2:$AW$131)

If 02-0131 is the Spring registration column. If there is a "x" there
then I know
they are playing for the year. I would like to say, "If they are registered for
spring then add up the points in AW2-131 if they are on TeamName.

Any ideas appreciated.

Wade

#2

Just an off-the-top-of-my-head stab at it, but can't you do something
like:

=SUMIF(($O$2:$O$131="x" AND $AU$2:$AU$131="Dk Blue"), $AW$2:$AW$131)

?

Paul

#3

Gives a Err:508.
I dont think it can be text that its comparing.

#4

Yeah, you're right, forgot how SUMIF works for a moment...

It's easy with an intermediate column, but one-shot...

In fact, I'm not sure that it isn't *supposed* to be done with
intermediate columns. At least, the only way I can see that it could be
done without them would be either some sort of temporary column, or by
defining the return value of a range, neither of which are really the
purpose of spreadsheets, as I see it.

Maybe Brian has some smart ideas.

Paul

#5

I guess I should have just tried this:
=SUMPRODUCT($O$2:$O$131="x",$AU$2:$AU$131="Dk Blue", $AX$2:$AX$131)
I thought I had and it did not work but, it does.

:smiley: Thanks

#6

Why not:
=SUMPRODUCT($O$2:$O$131="x",$AU$2:$AU$131="Dk Blue", $AW$2:$AW$131)
the function multiply the values for every argument, if a condition is true
their result is 1, what implies if both are true=
1 * 1 * AWvalue
and this result is added.

and depends on your version you can do it:
=SUMIFS( $AW$2:$AW$131,$O$2:$O$131,"x",$AU$2:$AU$131,"Dk Blue")

Miguel Ángel.