Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF FUNCTION works in all cells but those related to one account
My Excel Workbook (2003 version_ includes two tabs (spreadsheets. The first
tab is an income statement. The second tab is a trial balance. The first spreadsheet has one column of account numbers. The SUMIF function is in the amount column to the right of the account number column and it refers to the second spreadsheet account number column. The formula pulls dollar amounts from that spreadsheet (Trial Balance) for each account number found in the first spreadsheet. This SUMIF function works in ever cell of the first spreadsheet except for cells with one specific account number. There is no error message. It simply returns a ZERO amount. Formatting is "general" for the account number columns in both spreadsheets. The account number in question is exactly the same in both spreadsheets. Any ideas why this is happening? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF FUNCTION works in all cells but those related to one account
The numbers for that account may not be numeric numbers. They may be TEXT
numbers. NUMERIC numbers and TEXT numbers are not the same even though they may look the same. Or, the accounts don't *exactly* match from sheet to sheet. The problem is one or the other! If your formula works for every other account then I'd look at the accounts not exactly matching from sheet to sheet as the culprit. -- Biff Microsoft Excel MVP "Skyhawk" wrote in message ... My Excel Workbook (2003 version_ includes two tabs (spreadsheets. The first tab is an income statement. The second tab is a trial balance. The first spreadsheet has one column of account numbers. The SUMIF function is in the amount column to the right of the account number column and it refers to the second spreadsheet account number column. The formula pulls dollar amounts from that spreadsheet (Trial Balance) for each account number found in the first spreadsheet. This SUMIF function works in ever cell of the first spreadsheet except for cells with one specific account number. There is no error message. It simply returns a ZERO amount. Formatting is "general" for the account number columns in both spreadsheets. The account number in question is exactly the same in both spreadsheets. Any ideas why this is happening? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF FUNCTION works in all cells but those related to one account
Wihtout seeing your data, possible ideas:
The sum really is zero? Don't know if negative charges exist. One of the heet's account numbers was originally entered as text. Even if you then switch the formatting later to general, XL will stores the 'number' as text, and you won't get a match. Is there a chance that some zero's have been etnered as O's? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Skyhawk" wrote: My Excel Workbook (2003 version_ includes two tabs (spreadsheets. The first tab is an income statement. The second tab is a trial balance. The first spreadsheet has one column of account numbers. The SUMIF function is in the amount column to the right of the account number column and it refers to the second spreadsheet account number column. The formula pulls dollar amounts from that spreadsheet (Trial Balance) for each account number found in the first spreadsheet. This SUMIF function works in ever cell of the first spreadsheet except for cells with one specific account number. There is no error message. It simply returns a ZERO amount. Formatting is "general" for the account number columns in both spreadsheets. The account number in question is exactly the same in both spreadsheets. Any ideas why this is happening? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF FUNCTION works in all cells but those related to one acc
Thank you for the quick response.
After checking the formatting, I found all the same. However, on a whim I copied the problem account number from another source and pasted it into my spreadsheet. Whalla! It worked. Funny thing, though: formatting was exactly the same as the number replaced. Oh well: I guess this is one of the "mysteries" inherrent in Excel... Skyhawk "T. Valko" wrote: The numbers for that account may not be numeric numbers. They may be TEXT numbers. NUMERIC numbers and TEXT numbers are not the same even though they may look the same. Or, the accounts don't *exactly* match from sheet to sheet. The problem is one or the other! If your formula works for every other account then I'd look at the accounts not exactly matching from sheet to sheet as the culprit. -- Biff Microsoft Excel MVP "Skyhawk" wrote in message ... My Excel Workbook (2003 version_ includes two tabs (spreadsheets. The first tab is an income statement. The second tab is a trial balance. The first spreadsheet has one column of account numbers. The SUMIF function is in the amount column to the right of the account number column and it refers to the second spreadsheet account number column. The formula pulls dollar amounts from that spreadsheet (Trial Balance) for each account number found in the first spreadsheet. This SUMIF function works in ever cell of the first spreadsheet except for cells with one specific account number. There is no error message. It simply returns a ZERO amount. Formatting is "general" for the account number columns in both spreadsheets. The account number in question is exactly the same in both spreadsheets. Any ideas why this is happening? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF FUNCTION works in all cells but those related to one acc
That means your account numbers didn't exactly match from sheet to sheet.
When you copied/pasted you also copied/pasted the format of the source cell. This is the default behavior unless you use Paste Special and explicitly exclude formats. Apparently, this new format then matched the format of the other sheet and is now a match. -- Biff Microsoft Excel MVP "Skyhawk" wrote in message ... Thank you for the quick response. After checking the formatting, I found all the same. However, on a whim I copied the problem account number from another source and pasted it into my spreadsheet. Whalla! It worked. Funny thing, though: formatting was exactly the same as the number replaced. Oh well: I guess this is one of the "mysteries" inherrent in Excel... Skyhawk "T. Valko" wrote: The numbers for that account may not be numeric numbers. They may be TEXT numbers. NUMERIC numbers and TEXT numbers are not the same even though they may look the same. Or, the accounts don't *exactly* match from sheet to sheet. The problem is one or the other! If your formula works for every other account then I'd look at the accounts not exactly matching from sheet to sheet as the culprit. -- Biff Microsoft Excel MVP "Skyhawk" wrote in message ... My Excel Workbook (2003 version_ includes two tabs (spreadsheets. The first tab is an income statement. The second tab is a trial balance. The first spreadsheet has one column of account numbers. The SUMIF function is in the amount column to the right of the account number column and it refers to the second spreadsheet account number column. The formula pulls dollar amounts from that spreadsheet (Trial Balance) for each account number found in the first spreadsheet. This SUMIF function works in ever cell of the first spreadsheet except for cells with one specific account number. There is no error message. It simply returns a ZERO amount. Formatting is "general" for the account number columns in both spreadsheets. The account number in question is exactly the same in both spreadsheets. Any ideas why this is happening? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif based on last digit of account list | Excel Worksheet Functions | |||
How to use SUMIF function with non-adjacent cells | Excel Worksheet Functions | |||
Adding cells with the same color, sub works but function doesnt | Excel Discussion (Misc queries) | |||
Sumif function, nonadjacent cells | Excel Worksheet Functions | |||
SUMIF other related field contains certain information | Excel Discussion (Misc queries) |