Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif function help
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif function help
One way:
=IF(ISNA(MATCH("V009",Sheet2!E:E,FALSE)), "", VLOOKUP("V009", Sheet2!E:G,2,FALSE) + VLOOKUP("V009",Sheet2!E:G,3,FALSE)) In article , Haz wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif function help
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" wrote: One way: =IF(ISNA(MATCH("V009",Sheet2!E:E,FALSE)), "", VLOOKUP("V009", Sheet2!E:G,2,FALSE) + VLOOKUP("V009",Sheet2!E:G,3,FALSE)) In article , Haz wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif function help
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. In article , Haz wrote: 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" wrote: One way: =IF(ISNA(MATCH("V009",Sheet2!E:E,FALSE)), "", VLOOKUP("V009", Sheet2!E:G,2,FALSE) + VLOOKUP("V009",Sheet2!E:G,3,FALSE)) In article , Haz wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif function help
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. In article , Haz wrote: 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif function help
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 "JE McGimpsey" wrote in message ... 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. In article , Haz wrote: 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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif function help
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 "Haz" wrote: 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif function help
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. In article , Haz wrote: =IF(ISNA(MATCH("e2",Sheet1!E:E,FALSE)), "", VLOOKUP(e2, Sheet1!E:G,2,FALSE) + VLOOKUP("e2",Sheet1!E:f,3,FALSE)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
please help how to combine IF function with Countif function | Excel Worksheet Functions | |||
Function Help - COUNTIF | New Users to Excel | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
Embed a countif function in subtotal function? | Excel Worksheet Functions | |||
COUNTIF FUNCTION | Excel Worksheet Functions |