Help with if and countif

[htz]

Hi, I was wondering if someone could help me out. I looking for a formula that will count wins and loses for a particular sport. I've attached a sample of my spreadsheet.

Ex. if B = NFL then F4 would count all the 'won' in column C and G4 would count all the 'Loss' in column C
if B = NBA then F5 would count all the 'won' in column C and G5 would count all the 'Loss' in column C
if B = NHL then F6 would count all the 'won' in column C and G6 would count all the 'Loss' in column C
etc

not sure if I explained my self correctly but any help would be appreciated.

[jeffreybrown]

Try in F3 copied to the right and down...

=SUMPRODUCT(--($B$2:$B$628=$E3),--($C$2:$C$628=F$2))

...a pivot table would also work well in this situation.

[htz]

I paste that formula in F3? It gives me 12

[jeffreybrown]

What should you get?

I got 22 for NFL and WON

[htz]

Maybe I'm not using the formula correctly or didn't do a good job of explaining myself. I've attached another copy of the spreadsheet using only the first 10 lines of data as a sample with the results it should give me.

[jeffreybrown]

Then adjust the ranges...

=SUMPRODUCT(--($B$2:$B$11=$E4),--($C$2:$C$11=F$3))

[htz]

Thanks so much, works perfect.

[Ivan Sauri]

COUNTIFS
sample

=COUNTIFS($B$2:$B$628,$E4,$C$2:$C$628,"WON")
sample.jpg

[jeffreybrown]

Hi Ivan,

If you notice, the OPs profile shows a version lower than 2007 so the COUNTIFS would not work.

[Ivan Sauri]

Thanks Jeff, I could not see before that detail. Maybe someone with this situation in (Excel 2007) you could be of benefit. I'm new to the forum, and again thanks for your recommendation.


Respectfully Ivan Sauri