=IF(A3:A200=(TRIM("Total Revenue:")),B3:B200,0)

If the text Total Revenue appears in A19, I would like Excel to return the
corresponding data in B19. If not, then return 0.

What am I doing wrong? I have looked throughout this site and am not
finding my answer so if it has been answered before, please point me to the
correct place. My apologies/thanks in advance

=IF(A19="Total Revenue:",B19,0)
I don't know what you were trying to do with the TRIM function, but it
didn't seem to be doing anything for you. Perhaps you need to look the
function up in Excel help to remind yourself what it does?

Copy the formula up and down the column if you want it in the other rows.
--
David Biddulph

nt wrote:
=IF(A3:A200=(TRIM("Total Revenue:")),B3:B200,0)

If the text Total Revenue appears in A19, I would like Excel to
return the corresponding data in B19. If not, then return 0.

What am I doing wrong? I have looked throughout this site and am not
finding my answer so if it has been answered before, please point me
to the correct place. My apologies/thanks in advance

=IF(A3:A200=(TRIM("Total Revenue:")),B3:B200,"")

Array formula so must entered with CTRL + SHIFT + ENTER then copied down.


Gord Dibben MS Excel MVP


On Sat, 10 Jan 2009 09:00:01 -0800, nt wrote:

=IF(A3:A200=(TRIM("Total Revenue:")),B3:B200,0)

If the text Total Revenue appears in A19, I would like Excel to return the
corresponding data in B19. If not, then return 0.

What am I doing wrong? I have looked throughout this site and am not
finding my answer so if it has been answered before, please point me to the
correct place. My apologies/thanks in advance

OOPS!

Chamge the "" back to 0 as you originally had.


Gord

On Sat, 10 Jan 2009 09:29:19 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

=IF(A3:A200=(TRIM("Total Revenue:")),B3:B200,"")

Array formula so must entered with CTRL + SHIFT + ENTER then copied down.


Gord Dibben MS Excel MVP


On Sat, 10 Jan 2009 09:00:01 -0800, nt wrote:

=IF(A3:A200=(TRIM("Total Revenue:")),B3:B200,0)

If the text Total Revenue appears in A19, I would like Excel to return the
corresponding data in B19. If not, then return 0.

What am I doing wrong? I have looked throughout this site and am not
finding my answer so if it has been answered before, please point me to the
correct place. My apologies/thanks in advance

Hi,

If Total Revenue appears more than once all the other occurances will be
ignored, is that what you want?

The TRIM function serves no purpose here. If you are trying to find the
entry in column B for a match in column A of Total Revenue: then

=VLOOKUP("Total Revenue:",A3:B200,2,FALSE)

If on the other hand Total Revenue: is only part of the text in column A then

=VLOOKUP("*Total Revenue:*",A3:B200,2,FALSE)

Finally, if you are trying to sum all the lines containing Total Revenue: then

=SUMIF(A3:A200,"Total Revenue:",B3:B200)
or
=SUMIF(A3:A200,"*Total Revenue:*",B3:B200)


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"nt" wrote:

=IF(A3:A200=(TRIM("Total Revenue:")),B3:B200,0)

If the text Total Revenue appears in A19, I would like Excel to return the
corresponding data in B19. If not, then return 0.

What am I doing wrong? I have looked throughout this site and am not
finding my answer so if it has been answered before, please point me to the
correct place. My apologies/thanks in advance

I was using TRIM as my data is imported .txt file and I wondered if there
were spaces giving me the error.
I had tried the VLOOKUP but could only get it to work if the look up value
was a cell reference; when I tried to insert the text "Total Revenue:" (the
actual text in the cell), I get #n/a. I thought I was using the wrong
function so I went to IF. I would really like to use the text look up value
as I can't trust that "Total Revenue" will always stay in that particular
cell. Any thoughts as to what I am doing wrong?


"Shane Devenshire" wrote:

Hi,

If Total Revenue appears more than once all the other occurances will be
ignored, is that what you want?

The TRIM function serves no purpose here. If you are trying to find the
entry in column B for a match in column A of Total Revenue: then

=VLOOKUP("Total Revenue:",A3:B200,2,FALSE)

If on the other hand Total Revenue: is only part of the text in column A then

=VLOOKUP("*Total Revenue:*",A3:B200,2,FALSE)

Finally, if you are trying to sum all the lines containing Total Revenue: then

=SUMIF(A3:A200,"Total Revenue:",B3:B200)
or
=SUMIF(A3:A200,"*Total Revenue:*",B3:B200)


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"nt" wrote:

=IF(A3:A200=(TRIM("Total Revenue:")),B3:B200,0)

If the text Total Revenue appears in A19, I would like Excel to return the
corresponding data in B19. If not, then return 0.

What am I doing wrong? I have looked throughout this site and am not
finding my answer so if it has been answered before, please point me to the
correct place. My apologies/thanks in advance

It's working now! Thanks so much for your help-I truly appreciate it!

"Shane Devenshire" wrote:

Hi,

If Total Revenue appears more than once all the other occurances will be
ignored, is that what you want?

The TRIM function serves no purpose here. If you are trying to find the
entry in column B for a match in column A of Total Revenue: then

=VLOOKUP("Total Revenue:",A3:B200,2,FALSE)

If on the other hand Total Revenue: is only part of the text in column A then

=VLOOKUP("*Total Revenue:*",A3:B200,2,FALSE)

Finally, if you are trying to sum all the lines containing Total Revenue: then

=SUMIF(A3:A200,"Total Revenue:",B3:B200)
or
=SUMIF(A3:A200,"*Total Revenue:*",B3:B200)


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"nt" wrote:

=IF(A3:A200=(TRIM("Total Revenue:")),B3:B200,0)

If the text Total Revenue appears in A19, I would like Excel to return the
corresponding data in B19. If not, then return 0.

What am I doing wrong? I have looked throughout this site and am not
finding my answer so if it has been answered before, please point me to the
correct place. My apologies/thanks in advance