Solved: more SUMPRODUCT help

[allison]

I think that I just can't get the syntax correct and can't figure out what I'm doing wrong.

I have three data ranges:
TEAM -- the values in here are from a VLOOKUP
MONTH -- mm/dd/yyyy format
JUSTIFIED -- Yes/No text

Using what XLD suggested yesterday worked great for finding the number of records in the month that are justified:

SUMPRODUCT(--((month)=$C$1),--(justified="Yes"))

I now want to break things down by team:

# records by team for the month
# records by team for the month that are justified

I thought that I could just incorporate the team part, but I keep getting either #VALUE or #N/A

=SUMPRODUCT(--(T(team="B")),--((month)=$C$1),--(justified="Yes"))

Any suggestions?

[Bob Phillips]

Why are you using T, it didn't say that on the page

=SUMPRODUCT(--(team="B"),--(month=$C$1),--(Justified="Yes"))

[allison]

because it brings back a #N/A when I do what you wrote....I had found a post where it talked about how it returns cell references and not values, so I was trying to make it the text value of the lookup.

[Bob Phillips]

T returns the text value of its argument, so T(19) is empty, T("abc") is abc. I have only ever found one good use for it, and it is not in SP. What you probably read was a formula using OFFSET that returns cell references, and N is used to coerce is to number values.

#N/A is SP is usually caused by unequal ranges, they MUST be the same size.

[allison]

figured out what the #N/A problem was...the range was the same on all, but the justified column has yes/no - but there are some cells that are blank right now. I made the range longer then the current number of records since I know that more records will be added as the year goes on.

If I make the range the exact number of records, it works great. Any suggestions as to how to accomodate the larger range without values? I can always put something in there as a place holder if there isn't a way.

[Bob Phillips]

Believe me, that is not your problem. The formula will handle blanks in the Justified range without any difficulty. You have something else there. Can you post the workbook.

[allison]

once again, you are correct. my vlookup column had some #N/A's in it....I corrected that and all works great. thanks!