Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing Tabs
I have 2 sheets, the first has a list of model numbers I want to search for
in sheet2. I then want to total the number of items in sheet 2 that match the list in sheet 1. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing Tabs
Hi,
In a spare column (say, stating from C5) on sheet1, use the formula =countif(B5:B100,A5) and copy down (till say, cell C50). This will return you the number of times the items in Sheet1 are present in Sheet2. Now to know total of items, use the foll formula =countif(C5:C50,"0") -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "cra88" wrote in message ... I have 2 sheets, the first has a list of model numbers I want to search for in sheet2. I then want to total the number of items in sheet 2 that match the list in sheet 1. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing Tabs
cra88 wrote:
I have 2 sheets, the first has a list of model numbers I want to search for in sheet2. I then want to total the number of items in sheet 2 that match the list in sheet 1. Thanks Assume setup: Sheet1!A1:A4 a b c d Sheet2!A1:A10 a a a b b c d x y z One way is to calculate each count in Sheet1!B1 =COUNTIF(Sheet2!A:A,Sheet1!A1) fill down, then total column B. Another way accomplishes this in one step using an array* formula: =SUM(--ISNUMBER(MATCH(Sheet2!A1:A10,Sheet1!A1:A4,0))) Result = 7 *Commmit the array formula by pressing Ctrl+Shift+Enter. Do not just press Enter or Tab. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
creating tabs with sub tabs | Excel Worksheet Functions | |||
tabs are missing even though 'tools-options-view-sheet tabs' ok? | Excel Worksheet Functions | |||
hide tabs from view then lock tabs? | Excel Discussion (Misc queries) | |||
Comparing similar data on different sheet tabs | Excel Discussion (Misc queries) | |||
Can i set up tabs within tabs on Excel? | Excel Worksheet Functions |