Can you combine Count + Lookup Functions?

**thumper300zx** · 01-24-2011, 01:30 PM

I have a spreadsheet (attached) that I'd like to do the following for...

These are NFL football game results.

In Cell AG2, I would like to enter a formula that searches data is columns A:AN and tallies the # of times Column N has a CW (close win, which is 7 or less in "DIF"[M] column) for each winning team (column D).

So, in this case, the formula would see "ARI" five times in column D, then read the values in Column N and count CW three times, and give the result '3' in AG2.

If I have just this one formula, it will help me endlessly with such spreadsheets in the future, so thanks in advance for your help.

**StephenR** · 01-24-2011, 01:46 PM

This in AG2. But you have to remove the trailing spaces in column D:

=SUMPRODUCT(($N$2:$N$257="CW")*($D$2:$D$257=$AF2))

**thumper300zx** · 01-24-2011, 01:51 PM

Originally Posted by StephenR

This in AG2. But you have to remove the trailing spaces in column D:

=SUMPRODUCT(($N$2:$N$257="CW")*($D$2:$D$257=$AF2))

I noticed something was funny about the data -- thanks for pointing out the trailing spaces.

Much appreciated. 14 thousand some odd trailing spaces removed

Must've been a remnant of the table I copied values from.

**StephenR** · 01-24-2011, 01:58 PM

I assume you knew about TRIM then. Or you must be a very quick typist.

**thumper300zx** · 01-24-2011, 02:00 PM

Originally Posted by StephenR

I assume you knew about TRIM then. Or you must be a very quick typist.

Actually just did REPLACE ALL "<space>" w/ "".

Could only do that because I know there are no other spaces needed in this spreadsheet, so I'll check out TRIM