IF Statement

  • Thread starter Thread starter nt
  • Start date Start date
N

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
 
=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
 
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)
 
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?
 
Back
Top