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

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

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

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

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

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

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

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

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

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

Sumif problem
 

Ron, thank you

Greg