SumProduct

[tonyalt3]

I am trying to look up how many times a person is listed on a
worksheet. They are listed as lastname, column A and first name,
column B. I've been doing a "sumproduct" function, however for some
reason it doesn't count some individuals. I KNOW everything is
spelled the same and has the same amount of caps. Any ideas of what
is wrong, or how else to run the function?

 

[T. Valko]

What does your formula look like?

It should look like this (of course, adjusting the ranges to your own):

=SUMPRODUCT(--(A1:A100="smith"),--(B1:B100="john"))

Case is not a factor.

It's better to use cells to hold the criteria:

D1 = smith
E1 = john

=SUMPRODUCT(--(A1:A100=D1),--(B1:B100=E1))

Another possible cause is leading/trailing spaces or other unseen
characters.

smith<space>
<space>john

 

[Duke Carey]

The most likely culprit is leading or trailing spaces. You can use TRIM()
within a sumproduct to eliminate those spaces

=SUMPRODUCT(--(TRIM(A1:A10)="Bob"))

 

[smartin]

I am trying to look up how many times a person is listed on a
worksheet. They are listed as lastname, column A and first name,
column B. I've been doing a "sumproduct" function, however for some
reason it doesn't count some individuals. I KNOW everything is
spelled the same and has the same amount of caps. Any ideas of what
is wrong, or how else to run the function?

The case (caps, not caps) doesn't matter (try it) however if there are
any leading or trailing spaces in the name the match will fail.

 

[tonyalt3]

The case (caps, not caps) doesn't matter (try it) however if there are
any leading or trailing spaces in the name the match will fail.

The "trim" suggestion worked. I figured it was a leading or trailing
space, but other than going through each cell to eliminate it I wasn't
sure how to fix. Trim worked. Thanks to all!

 

[ShaneDevenshire]

Hi Tony,

If the names are all single words you could use a Find and Replace command
to remove all the offending spaces. However, if some of the name are like
Mac Murry, where this is the last name, this technique won't work.

You can also flag all the offending cells by using the following conditional
formatting formula:

=LEN(A1)<>LEN(TRIM(A1))

Assuming your data starts in cell A1, select that cell and choose Format,
Conditional Formatting, Formula is, and enter the above formula. Click
Format and pick a color on the Pattern tab, click OK twice and copy the
format to any cells.

Cheers,
Shane