Countif function help

[Guest]

i'm looking for a countif function I think for the following :
Col E sheet 2 Col F sheet 2
doc no AMT
V009 £500

I want to search for this data in sheet 1 and return the result if there is
an exact match...Where
Col H sheet2
Doc No: ( if there is V009 in this column.........then return the AMT in
either Column F or G. One of these amounts is always '0' so which ever is >0
in F or G sheet 2.
is this possible with count if ?
Hope this is clear
any help would be appreciated Thanks in advance.

 

[JE McGimpsey]

One way:

=IF(ISNA(MATCH("V009",Sheet2!E:E,FALSE)), "", VLOOKUP("V009",
Sheet2!E:G,2,FALSE) + VLOOKUP("V009",Sheet2!E:G,3,FALSE))

 

[Guest]

hi,
this isn't working, should there not be a reference to sheet 1 where the
data to be matched is? also the doc no: will change as you move down the
column.
Thanks

 

[JE McGimpsey]

Hard to tell, I was confused by your references...

Replace Sheet2 with Sheet1 if that's where your look values are, and
replace "V009" with a reference to your cell that contains V009.

 

[Guest]

Thanks for getting back to me, when i do this it says your missing a
parethess--) not sure what this is. However, i've been able to match the
document numbers up by using a count if function:
=IF(COUNTIF(Sheet3!$A:$A,A2),A2,"") I now want to check if the totals agree
in two different worksheets.
Sheet 2 Match with: sheet 1
col E col f or col G where 1 of this is
always Zero, so return
Amt value >0 from either

Is there a simpler solution to this?
Thanks again

 

[JE McGimpsey]

Since you didn't reply to the message with the "this" you refer to, I
can't tell what the problem is.

You can use COUNTIF() if you want, rather than ISNA(MATCH()), but
there's no real advantage to doing so. You'll still need to do a couple
of VLOOKUP()s to return the values you want.

 

[Peo Sjoblom]

In fact there is a drawback since COUNTIF will happily swallow text numbers
and then vlookup will still return the NA error whereas MATCH will not


--


Regards,


Peo Sjoblom

 

[Guest]

hi, Can't get countif to work for me here,your right it'll have to be a
vlookup which i don't know much about. i'm typing in yours as this but it
says your missing a parenthesis--) ? this is what i'm putting in

=IF(ISNA(MATCH("e2",Sheet1!E:E,FALSE)), "", VLOOKUP(e2,
Sheet1!E:G,2,FALSE) + VLOOKUP("e2",Sheet1!E:f,3,FALSE))

where col E in sheet 2 contains the doc no which i'm trying to match in col
H in sheet 1 and then return the value in either column F or G also in sheet
1 depending on which is greater than 0.

Thanks for helping

 

[JE McGimpsey]

There is no missing parenthesis in that formula.

Also, the second VLOOKUP's lookup range must be at least E:G, not E:F.

OTOH, if you're trying to match the value in cell E2, don't put "e2" in
quotes. If you're trying to find the text value "e2", don't use the cell
reference e2 in the VLOOKUP() statement.

XL is persnickety. It attempts to execute what you type, not what you
intend.