Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Require formula to search across multiple worksheets
Can anyone possibly help with the following?
I have the formula below which counts the total number of items which contain two specific criteria in the worksheet named OffshorePipelay. SUMPRODUCT(('OffshorePipelay'!$C$3:$C$999=$E$10)*( 'Offshore Pipelay'!$R $3:$R$999=$C15)) Where the value in E10 in the formula is selected from a dropdown list of 10 possible entries. I have 10 worksheets each one named for each of these possible entries and would like the formula to search each of these multiple worksheets and find which one of these matches this specific criteria and then count how many there are when both this and the second criteria (in C15) are met. I have tried typing this in: SUMPRODUCT(('OffshorePipelay:SEV'!$C$3:$C$999=$E$1 0)*('Offshore Pipelay:SEV!$R$3:$R$999=$C15)) But when I press enter the formula comes up as this: SUMPRODUCT(('OffshorePipelay:[SEV]SEV'!$C$3:$C$999=$E$10)*('Offshore Pipelay:[SEV]SEV'!$R$3:$R$999=$C15)) And the value comes up as #REF! Can anyone shed some light as to where I’m going wrong as I have quite a few formulas where searching across all the worksheets would save ALOT of time. Thanks in advance for any input! LCTECH |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Require formula to search across multiple worksheets
Your formula will work if you add double unary operators. You can
google this to find out more, but essentially it causes the sumproduct formula to compare each item in the array (in your case, 'OffshorePipelay:SEV!$c$3:$c999) to your search term ($e$10) and evaluate it as a 1 when it matches and a 0 when it does not. The sumproduct formula allows multiple conditions, and then does the usual sumproduct thing: it multiplies the 1s and 0s and adds them together at the end. If a row has a match in one array and a non-match on the same row in the other array, the formula multiplies 1 by 0, resulting in 0 and adding nothing to the final count. If a row contains a match in both arrays, the formula multiplies 1 by 1, resulting in 1 and adding 1 to the final count. The double unary operator is two dashes inside your formula. Your formula should work if you enter this: SUMPRODUCT(--('OffshorePipelay:SEV'!$C$3:$C$999=$E$10),-- ('OffshorePipelay:SEV!$R$3:$R$999=$C15)) Note that I replaced the * multiplication operator with a comma, and added two double unary operators before the open parens. Good luck, let us know how it works. DaveO Eschew obfuscation. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Require formula to search across multiple worksheets
On looking at it further: Excel should not allow you to include a
colon ":" in your worksheet name. Try removing the colon, and making sure the typed formula (everything between the apostrophe and before the exclamation point) exactly matches the worksheet name. Dave O |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search Multiple Worksheets | Excel Discussion (Misc queries) | |||
#N/A search across multiple worksheets. | Excel Programming | |||
Search in Multiple Worksheets | Excel Discussion (Misc queries) | |||
Search multiple worksheets | Excel Worksheet Functions | |||
Search Multiple Worksheets | Excel Discussion (Misc queries) |