IF Statement

[nt]

=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

 

[David Biddulph]

=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.

 

[Gord Dibben]

=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

 

[Gord Dibben]

OOPS!

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


Gord

 

[Shane Devenshire]

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)

 

[nt]

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?

 

[nt]

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