Count across rows of data

Originally Posted by ConneXionLost

How about this:

=IF(SUMPRODUCT(--($A$2:$A$10=A2),--($C$2:$C$10="---")),0,SUMIF($A$2:$A$10,A2,$C$2:$C$10))

Cheers,

That worked great. I didn't need to create a sum when the COUNT = "---", so I changed the end of the function to simply ... ,0,1).

Also, I'm curious: What does the "--" prefix do?

It seems I can use a similar approach for a similar type of calculation. Now that I've flagged every state with a 1 or a 0, I'd like to sum the values for YEAR, only for those states who have a 1 for the Result.

So go from this:

STATE YEAR COUNT RESULT
AL 2000 5 0
AL 2001 --- 0
AL 2002 4 0
AK 2000 8 1
AK 2001 2 1
AK 2002 3 1
etc.

To this:

STATE YEAR COUNT RESULT
AL 2000 5 0
AL 2001 --- 0
AL 2002 4 0
AK 2000 8 1
AK 2001 2 1
AK 2002 3 1
TOT 2000 8
TOT 2001 2
TOT 2002 3

(Not the best example since there's only one State that applies, but I think you get the idea.)

I tried using this function:

=IF(SUMPRODUCT(--($B$2:$B$10=B2),--($D$2:$D$10=1)),SUMIF($B$2:$B$10,B2,$C$2:$C$10))

But it's summing all of the States; not just those who have a flag of 1. Any suggestions?

Thank you again. This is great.

Figured it out:

=SUMPRODUCT(--($B$2:$B$10=B2),--($D$2:$D$10=1),$C$2:$C$10)

Looks like I don't need that last SUMIF clause (the extra B2:B10,B2), nor do I need the first IF.