PDA

View Full Version : Solved: more SUMPRODUCT help



allison
02-21-2008, 07:08 AM
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
02-21-2008, 09:16 AM
Why are you using T, it didn't say that on the page

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

allison
02-21-2008, 09:30 AM
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
02-21-2008, 09:38 AM
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
02-21-2008, 11:50 AM
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
02-21-2008, 12:20 PM
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
02-21-2008, 12:35 PM
once again, you are correct. my vlookup column had some #N/A's in it....I corrected that and all works great. thanks!