Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct
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 <spacejohn -- Biff Microsoft Excel MVP "tonyalt3" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct
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")) "tonyalt3" wrote: 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct
tonyalt3 wrote:
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct
On Sep 11, 5:10*pm, smartin wrote:
tonyalt3 wrote: 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. 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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct
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 -- Thanks, Shane Devenshire "tonyalt3" wrote: On Sep 11, 5:10 pm, smartin wrote: tonyalt3 wrote: 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. 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
Can I use sumproduct for this? | Excel Worksheet Functions | |||
Sumproduct | Excel Discussion (Misc queries) | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
SUMPRODUCT Help please | Excel Discussion (Misc queries) |