Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumif problem
I have a table where I want to sum all the values in "F", if the cells
in "A" begin with "05". "A" actually contains account numbers, which are text, that the first two numbers are the fiscal year. I can't quite get my head around a sumif or other formula. TIA Greg |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumif problem
Hi Greg,
Try this: =SUMIF(A1:A100,"A*",B1:B100) Regards, KL "GregR" wrote in message ups.com... I have a table where I want to sum all the values in "F", if the cells in "A" begin with "05". "A" actually contains account numbers, which are text, that the first two numbers are the fiscal year. I can't quite get my head around a sumif or other formula. TIA Greg |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumif problem
On 23 Jun 2005 16:57:10 -0700, "GregR" wrote:
I have a table where I want to sum all the values in "F", if the cells in "A" begin with "05". "A" actually contains account numbers, which are text, that the first two numbers are the fiscal year. I can't quite get my head around a sumif or other formula. TIA Greg =SUMIF(A:A,"05*",F:F) --ron |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumif problem
=SUMIF(A:A,"05*",F:F)
This won't work if the account number is an actual number (rather than alphnumeric) stored as 511111 for example and formatted to display a leading zero (0511111). Likely this is not the case. -- Regards, Tom Ogilvy "GregR" wrote in message ups.com... I have a table where I want to sum all the values in "F", if the cells in "A" begin with "05". "A" actually contains account numbers, which are text, that the first two numbers are the fiscal year. I can't quite get my head around a sumif or other formula. TIA Greg |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumif problem
On Thu, 23 Jun 2005 20:49:57 -0400, "Tom Ogilvy" wrote:
=SUMIF(A:A,"05*",F:F) This won't work if the account number is an actual number (rather than alphnumeric) stored as 511111 for example and formatted to display a leading zero (0511111). Likely this is not the case. The OP did state that the account numbers were TEXT, but yours is a good warning. --ron |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumif problem
Guys, what if, the entry is formatted as general, as opposed to text.
What adjustment do I need to make to the function to make it work? TIA Greg |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumif problem
On 24 Jun 2005 08:02:00 -0700, "GregR" wrote:
Guys, what if, the entry is formatted as general, as opposed to text. What adjustment do I need to make to the function to make it work? TIA Greg The formatting is irrelevant. What matters is whether the data is stored as Numbers or stored as Text. In your initial post, you stated the data was stored as Text and that you were looking for a leading "05". If that is the case, the SUMIF formula I posted will work. If you are seeing a leading 05 (or 0n), and the cells are formatted as General, then the entry is a TEXT entry and the SUMIF formula I posted should work. If that is not the case, then what do you have there? All numbers? Some stored as numbers and some stored as text? Some with leading zeros and some without? What happens when you do the function =ISTEXT(cell_ref) on some of these? --ron |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumif problem
The entry is stored as 05-45-000-000-000 and your formula works. I was
just musing, if it was a number, what adjustment would I have to make. TIA Greg |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumif problem
A number isn't stored with a leading 0, so you would have to search for
,"5*" -- Regards, Tom Ogilvy "GregR" wrote in message oups.com... The entry is stored as 05-45-000-000-000 and your formula works. I was just musing, if it was a number, what adjustment would I have to make. TIA Greg |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumif problem
On 24 Jun 2005 11:10:30 -0700, "GregR" wrote:
The entry is stored as 05-45-000-000-000 and your formula works. I was just musing, if it was a number, what adjustment would I have to make. TIA Greg Depending on the length of your serial number, something like: =SUMPRODUCT((INT(A1:A100/10^11)=5)*1) --ron |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumif problem
Ron, thank you
Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF problem | Excel Worksheet Functions | |||
Sumif problem | Excel Worksheet Functions | |||
SumIf Problem | Excel Discussion (Misc queries) | |||
SUMIF problem | Excel Worksheet Functions | |||
SUMIF problem | Excel Worksheet Functions |