Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which formula to use?
Hello
I have a grid of data, down the left is 2 columns for product item and description, along the top are months. I need a formula function to search along the top to find the correct month, then search down the 1st column to find a product match, then search down the 2nd column to find a description match. The intersection of the correct row(s) and column being the value i require. There may be several returns matching the same criteria, so the results need adding together. Previously (xl2003) we used a sum(if array but it is slowing the calculations right down. Now (xl2007) I have looked into the new sumifs and getpivotdata but can't get any sense out of either. I can do a custom function, but at the moment would rather keep it standard. Any ideas Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which formula to use?
Hi,
If I understand correctly you have a table similar to this Jan Feb Mar Apr a b 1 2 3 4 c d 2 3 4 5 e f 8 7 6 5 a b 4 5 6 7 I've limited this to 4 months to prevent it wrapping =SUMPRODUCT((A2:A5=N1)*(B2:B5=N2)*(C1:F1=N3)*(C2:F 5)) The above formula will return 7 whe N1= a N2= b N3= Feb Note That all the dates are correctly formatted dates and not simply text Mike "Cresta" wrote: Hello I have a grid of data, down the left is 2 columns for product item and description, along the top are months. I need a formula function to search along the top to find the correct month, then search down the 1st column to find a product match, then search down the 2nd column to find a description match. The intersection of the correct row(s) and column being the value i require. There may be several returns matching the same criteria, so the results need adding together. Previously (xl2003) we used a sum(if array but it is slowing the calculations right down. Now (xl2007) I have looked into the new sumifs and getpivotdata but can't get any sense out of either. I can do a custom function, but at the moment would rather keep it standard. Any ideas Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which formula to use?
Thanks Mike, I have successfully used your example below for the right
results.(Your datagrid is exactly how it is, well deciphered). I have also managed the sumifs by way of additional formula, =SUMIFS(INDIRECT(ADDRESS(2,(MATCH($I$1,Headers)),, ,)&":"&ADDRESS(9,(MATCH($I$1,Headers)))),A1:A9,"=c ",B2:B9,"=c") We have a massive workbook (xl03) with many sum(if arrays, which takes about 8 minutes to caluculate on a modern pc. We have moved it to xl07 and are trying to speed it up by replacing the old arrays with more efficient formula. Which of the two examples above would run the quickest? Thanks again "Mike H" wrote: Hi, If I understand correctly you have a table similar to this Jan Feb Mar Apr a b 1 2 3 4 c d 2 3 4 5 e f 8 7 6 5 a b 4 5 6 7 I've limited this to 4 months to prevent it wrapping =SUMPRODUCT((A2:A5=N1)*(B2:B5=N2)*(C1:F1=N3)*(C2:F 5)) The above formula will return 7 whe N1= a N2= b N3= Feb Note That all the dates are correctly formatted dates and not simply text Mike "Cresta" wrote: Hello I have a grid of data, down the left is 2 columns for product item and description, along the top are months. I need a formula function to search along the top to find the correct month, then search down the 1st column to find a product match, then search down the 2nd column to find a description match. The intersection of the correct row(s) and column being the value i require. There may be several returns matching the same criteria, so the results need adding together. Previously (xl2003) we used a sum(if array but it is slowing the calculations right down. Now (xl2007) I have looked into the new sumifs and getpivotdata but can't get any sense out of either. I can do a custom function, but at the moment would rather keep it standard. Any ideas Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which formula to use?
Cresta,
Sumifs is an Excel 2007 function and as a poor pensioner of modest means I haven't upgraded to so I can't be certain but if I look at the amount of calls in the sumifs option it's hard to believe it won't be slower. I'm glad my first solution worked. Mike "Cresta" wrote: Thanks Mike, I have successfully used your example below for the right results.(Your datagrid is exactly how it is, well deciphered). I have also managed the sumifs by way of additional formula, =SUMIFS(INDIRECT(ADDRESS(2,(MATCH($I$1,Headers)),, ,)&":"&ADDRESS(9,(MATCH($I$1,Headers)))),A1:A9,"=c ",B2:B9,"=c") We have a massive workbook (xl03) with many sum(if arrays, which takes about 8 minutes to caluculate on a modern pc. We have moved it to xl07 and are trying to speed it up by replacing the old arrays with more efficient formula. Which of the two examples above would run the quickest? Thanks again "Mike H" wrote: Hi, If I understand correctly you have a table similar to this Jan Feb Mar Apr a b 1 2 3 4 c d 2 3 4 5 e f 8 7 6 5 a b 4 5 6 7 I've limited this to 4 months to prevent it wrapping =SUMPRODUCT((A2:A5=N1)*(B2:B5=N2)*(C1:F1=N3)*(C2:F 5)) The above formula will return 7 whe N1= a N2= b N3= Feb Note That all the dates are correctly formatted dates and not simply text Mike "Cresta" wrote: Hello I have a grid of data, down the left is 2 columns for product item and description, along the top are months. I need a formula function to search along the top to find the correct month, then search down the 1st column to find a product match, then search down the 2nd column to find a description match. The intersection of the correct row(s) and column being the value i require. There may be several returns matching the same criteria, so the results need adding together. Previously (xl2003) we used a sum(if array but it is slowing the calculations right down. Now (xl2007) I have looked into the new sumifs and getpivotdata but can't get any sense out of either. I can do a custom function, but at the moment would rather keep it standard. Any ideas Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Regression Leverage Formula (Jerry W. Lewis or Mike Middleton)already have DFITS formula | Excel Worksheet Functions | |||
copy formula down a column and have cell references change within formula | New Users to Excel | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |